Kbase P133717: Querying records that were added after converting a database from version 9 to version 10 takes long
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
24/03/2009 |
|
Status: Verified
SYMPTOM(s):
Querying records that were added after converting a database from version 9 to version 10 takes longer than querying records that existed before the conversion.
FACT(s) (Environment):
Approximately the same number of records are returned for the query run using version 9, as the query run using version 10.
Rebuilding indexes using the proutil <dbname> -C idxbuild command does not change the observed behavior.
Querying records from the table using an index other than the primary index returns records much faster.
Converting the table where this behavior is observed to a Type 2 area does not change the behavior.
Moving indexes for that table to a separate area does not change the behavior.
A binary dump and load of that table does change the behavior. The querying of records existing both before, and added after conversion, now take about the same amount of time. However, this query is now slower than the original query of records that existed before the conversion.
Records are queried by specifying a particular date range.
OpenEdge 10.1B
Progress 9.1E
All Supported Operating Systems
CHANGE:
converted from 9.1E to 10.1B
CAUSE:
In version 9, the create limit for an 8K block database is around 600 bytes, which means that blocks that have less than 600 bytes free are considered as not having enough space available to accommodate a new record, regardless of that record's size. In version 10, the create limit is lowered to 150 bytes. As a result, records that are added to a database after a conversion can be written to existing blocks, that in version 9, were considered "full". The records added to the database before conversion were most likely somewhat sequentially ordered. Records added after a conversion can be written to existing blocks if the record size is less than 450 bytes (600 bytes - 150 bytes). This can result in the same number of records added after the conversion occupying a larger number of blocks than the same number of records added before the conversion. A query of records added after the conversion can take longer because more blocks are retrieved to return the records.
FIX:
Perform a binary dump and load using the index that is used by the query. This will sequentially order the records in the blocks making the query run much faster.
Backup the database
Dump the data definition file for the table.
Binary dump the table.
Command: proutil <dbname> -C dump <table name> <dump directory> -index <index number>
Delete the table
Load the data definition file for the table.
Binary load the table.
Command: proutil <dbname> -C load <binary dump file> build indexes