Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Binary dump is taking longer for one table than for another table with more than three times the number of records.

Binary dump takes 2.5 hours to dump Table A, and 30 minutes to dump Table B.

Running a simple 4GL program that does a FOR EACH through a table using the default index and recording how many times the next RECID is less than the current one shows that Table A has a higher percentage of next RECID's less than the current RECID.

The 4GL program also measures the difference in RECID when the next RECID is less than the current RECID. This value is averaged and shows that in table A, the difference between RECID is about eight times greater that the averaged differences in table B.

FACT(s) (Environment):

A proutil <dbname> -C dbanalys shows that Table A has a scatter factor of 3.8, and Table B has a scatter factor of 3.0.
Table A has 12 million records and is 2.2 GB in size.
Table B has 44 million records and is 3.2 GB in size.
The tables are located in different database.
Both databases are located in the same directory on the same physical disk.
Linux
OpenEdge 10.2A
OpenEdge Database Category: Performance

CAUSE:

When performing a binary dump of Table A and Table B using the default index, blocks are read into the buffer pool. With a greater difference in recid's when the next RECID is less than the current one, the chances are much greater that the block containing the RECID is no longer in the buffer pool and must be reread from disk. Disk I/O is an expensive operation in terms of performance, and could account for the difference in time it takes to binary dump Tables A and B.

FIX:

You can try increasing -B when running the binary dump. For example:
proutil dbname -C dump tablename directory -B 50000
or if you know how records were added to the table (by date for example), you can use the -index qualifier when performing the binary dump to retrieve records into the buffer pool in roughly the same order that they were added to the table. This could help eliminate the need to reread blocks into the buffer pool if the records are read in in roughly RECID ascending order. For example:
proutil dbname -C dump tablename directory -index num
A program that can count the number of times the next RECID is less than the current one, and averages the difference between current and next RECID when the next is less than the current is shown below. Be sure to substitute your table name for the two references to TABLENAME:
/* --------------------------- Begin --------------------------------------------------*/
def var aDist as int init 0 no-undo. /* Average "distance" */
def var BakCnt as int init 0 no-undo. /* Count of "backward" seeks */
def var Crec as RECID init ? no-undo. /* Current RECID */
def var MsgCnt as int init 1000000 no-undo. /* Status update interval */
def var Prec as RECID init ? no-undo. /* Previous RECID */
def var RecCnt as int init 0 no-undo. /* Total Record count */
def var tDist as /* int64 - OE10 */ deci /* - V9 */
init 0 no-undo. /* Total "distance" */
for each TABLENAME no-lock:
assign
Crec = RECID( TABLENAME )
RecCnt = RecCnt + 1.
if Prec <> ? then
do:
tDist = tDist + abs( int( Crec) - int( Prec)).
if Prec > Crec then
do:
BakCnt = BakCnt + 1.
end.
end.
Prec = Crec.
if (RecCnt mod MsgCnt) = 0 then
do:
aDist = tDist / RecCnt.
display RecCnt aDist format ">>>>>>>>>>>>>>9" BakCnt with down frame f1.
end.
end.
aDist = tDist / RecCnt.
display
RecCnt
BakCnt
aDist label "Avg Dist" with frame f2.
/* --------------------------- End--------------------------------------------------*/