Q:

A:

The first thing that we looked for was excessively full storage areas. In your environment, this was not the cause of your problems. By analyzing the transactions that were experiencing this problem, and running some test cases on your database, we were able to isolate the source of your problem. The problem you are experiencing has to do with the way that DBMS inserts records into sorted sets that have many duplicate key values. The current algorithm works as follows:

When DBMS attempts to insert a record into an indexed set, it searches the b-tree index for the largest key value that is less than or equal to the key value of the record being inserted. The index points to the first record that meets these criteria. If you are inserting a key value that is larger than the key value found, or the set order specifies duplicates are last, DBMS performs a sequential search through a chain of pointers linking records with duplicate key values until it finds a record with a key value larger than the one being inserted. 

Thus, the time to insert records into indexed sets with duplicate key values can fluctuate significantly depending on the number of duplicate records that must be traversed during the insert operation. In your case, the number of duplicates range from none to over 75,000!