The following example demonstrates how to determine the best records per block setting:
1. Assume you retrieved the following information from your table analysis:
The database has 1 million records.
The mean record size is 59 bytes.
2. Add the directory entry overhead (2 bytes) to determine the number of the actual size of the stored record, as shown:
Mean record size + overhead = actual storage size
59 + 2 = 61
3. Divide that number into your database block size to determine the optimal records per block, as shown:
Database block size / actual storage size = optimal records per block
8192 / 61 = 134
4. Choose a power of 2 from 1 to 256 for the records per block. You have two choices: 128 and 256. If you choose 128, you will run out of record slots before you run out of space in the block. If you choose 256, you run the risk of record fragmentation. Make your choice according to the nature of the records. If the records grow dynamically, then you should choose the lower number (128) to avoid fragmentation. If the records are added, but not typically updated, and are static in size, you should choose the higher number (256) of records per block. Generally OpenEdge will not fragment a record on insert; most fragmentation happens on update. Records that are updated frequently are likely to increase in size.
If you choose the lower value, you can determine this cost in terms of disk space. To do this, take the number of records in the table and divide by the number of records per block to determine the number of blocks that will be allocated for record storage, as shown:
Number of records / records per block = allocated blocks
1,000,000 / 128 = 7813
Next, calculate the number of unused bytes per block by multiplying the actual storage size of the record by the number of records per block and subtracting this number from the database block size, as shown:
Database block size – (Actual storage size * records per block) = Unused space per block
8192 – (61 * 128) = 384
Take the number of allocated blocks and multiply them by the unused space per block to determine the total unused space, as shown:
Allocated blocks * unused space per block = total unused space
7813 * 384 = 3000192
In this case, the total unused space that would result in choosing the lower records per blocks is less than 3MB. In terms of disk space, the cost is fairly low to virtually eliminate fragmentation. However, you should still choose the higher number for static records, as you will be able to fully populate your blocks and get more records per read into the buffer pool.
The database has 1 million records.