Kbase P72973: Extremely poor performance reading records from a table recently dumped and loaded
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/09/2004 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1D
SYMPTOM(s):
Extremely poor performance reading records from an audit table
Connected via shared memory
Database has 8K blocksize
Buffer pool is 25,000
With no one else using the database only gets 300 to 700 records read per second
Takes 5 minutes to read 50,000 records
Each record is approximately 2,000 bytes in size
Customers query is extremely simple:
FOR EACH Audit NO-LOCK:
ASSIGN Count = Count + 1.
IF Count MOD 50000 = 0 THEN
DISPLAY Count.
END.
CAUSE:
Upon reviewing the record position on disk, the records were found to be non-contiguously arranged.
Tables were not dumped by primary index order.
Tables were not loaded in primary index order.
FIX:
Dump and Load the records by a different index order to preserve contiguity and sequential placement on disk to improve read performance by the disks and Progress.
The following code example can be used to validate ordering to see if it is optimal from the Progress block perspective.
Example:
for each <tablename> by recid(<tablename>).
display <primary index field(s)> recid(<tablename>).
end.
If the ordering of the recids doesn't match the ordering of the primary index, then this should be observable with this code.
Example if we had loaded the alphabet in reverse sort order we would see
Letter Recid(Letter)
Z 1
Y 2
.
.
.
B 25
A 26
Whereas if we had loaded the alphabet in ascending sequential order:
Letter Recid(Letter)
A 1
B 2
.
.
.
Y 25
Z 26
If a table were loaded by non-primary index and then a query was made using the primary index (not the order that is on disk) the query should perform slowly due to the extensive need to reposition the disk to get to the various records.