 |
|
Q: |
We are experiencing
wide fluctuations in the time required to store certain record types
in our database. The problem does not seem to be related to locking
problems or lack of CPU resources (no other processes were attached
to the database, and the system has significant idle CPU available.)
|
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! |
|
Copyright © 2011 Software Concepts International |