Upgrade task V94

Symptom

The upgrade V93 takes forever with a customer, possibly because the DBMS chooses the worst execution path: Although it takes less than 5 minutes on a similar installation to perform the entire deduplication, a single request takes 45hrs for this customer.

We'll just replace the upgrade task V93 with a V94, which would be a copy of V70.

Solution

One upgrade task to rule them all!

• We’ll group the upgrade task V92 and V93 in one, V94,

• The implementation of V94 is the same as V70 (the colliding key manager), so we’ll remove V70,

• We’ll also remove V71, which sets the indexing mode for small instances - superseeded by V91, which sets the indexing mode for all instances,

• We’ll remove V74 and V77, which set the upper key (done in V94),

• We’ll remove V80, which removes the null requirements (done in V94).

In the end, we detect the duplicate requirement keys, but we let the customers decide what to fix:

Speed

The “Colliding key manager” is slower than the V92-V93 upgrade tasks which were based on a global query to detect all duplicates in one go:

• 5:41 to diagnose 750k requirements (in little transactions of 1000 requirements),

• 8:45 to fix 750k requirements into 300k single requirements (in little transactions of 100 requirements).

However:

• It processes every requirement separately, therefore each query is (should be) the smallest possible subquery, ensuring that transactions are very small, which is good on large instances,

• The global “find duplicate” query of V92-V93 was causing a deadlock on some instances,

• It works on UTF-8 duplicates (in theory),

• It takes this opportunity to reapply the “upper key” column, delete requirements with no key, etc.