REORGDB Performance enhancements

REORGDB Performance enhancements - E4000012 - 8 Aug 2000 (updated 7 September)

We have recently done an extensive investigation into the performance of REORGDB on large databases, and have been able to make some significant performance enhancements as a result. The enhancements are of four types:

We have improved the file buffering, so that less processor time is needed to sort buffers for writing to disk. This is a DP4 enhancement that also affects the database manager. See E400016 for more information.
The most common options execute somewhat faster - on a medium size database (approximately 200MB for both DAT and IND file, the time for a reorganisation is reduced from about 50 minutes, to between 34 and 41 minutes depending on the option chosen. On very large databases the gain is proportionately greater
The options to sort the data file no longer significantly increase the time needed to rebuild the data file. We discovered a problem with this option which caused the sort performance to become worse and worse when reorganising a large database. This is now fixed and the sort option works well on even very large databases (we have tested the new version with a datafile in excess of >1.4GB)
The increase in index size caused by using the -fast option is no longer so significant. On a well sorted data file there may be no penalty at all. However the -fast option still skips the index optimisation phase, which can impact performance of the database afterwards. We recommend you use it only if it is necessary. The majority of the time needed for a reorganisation is in the "indexing other table" phase, and the -fast option only reduces this marginally.

Further information

The -sort (and the new -altsort ) options used to take a long time because they make frequent allocations and deallocations of small amounts of memory. We found the heap fragmentation this caused eventually slowed REORGDB to a crawl. This has been fixed by rewriting the heap management code.

The first time you use the -sort or -altsort option the data file will take somewhat longer to rebuild than with a normal REORGDB. In the worst case it may take two or three times as long to build, but not more than this. Normally this phase is such a small part of the time needed for reorganisation that it is not worth worrying about. Next time you run REORGDB the penalty will not be so great, and the sort will be more complete as the heap will be less fragmented. On a well sorted data file the performance may even eventually be faster than if you had never sorted at all. We tried REORGDB on a small database (data file 56 MB index about 20 MB) and got the following results (all using -data -local):

Reorg version/option	Time (mm.ss)	Index size	DYNACHECK time
Old version	2.50	22 Meg	5.25
Old version with -fast	2.25	25 Meg	5.25
Old version with -altsort	10.10	20.5 Meg	4.40
New version	2.40	22 Meg	5.20
New version with -fast	2.25	23 Meg	-
New version with -altsort	3.20	20.5 Meg	4.40
New version with -altsort (2nd run)	2.40	20 Meg	4.20
New version with -altsort (3rd run)	2.32	19.5 Meg	4.10

The performance improvement for options with -sort or -altsort comes from changing the way the index file is built. Previously REORGDB created the primary index for each record as it came across it. It did this because before creating the secondary indexes (which are all cached) it needs to be sure the record is not a duplicate of another. In the new version REORGDB makes an initial pass through the database creating only primary indexes. This allows the primary index creation to be cached in the same way as for secondary indexes. On all but the smallest databases this improves performance as the improved caching of index building more than offsets the extra time spent reading the data file.

You can disable this new behaviour by specifying the command tail -onepass.

-ALTSORT

The -altsort option is similar to the -sort option. The -sort option sorts records by key but grouping parent and child records with matching keys together. The -altsort option goes not group related tables. The -altsort option will give fastest possible performance when you do a batch search through a whole table (for example with a complex SQL query).

DP4 Version Compatibility

4.519+,4.614+ (Requires updates to system database for progress display to be correct)

Beta software is supplied without warranty and should not be used in live sites without the agreement of Itim Technology Solutions. Compatibility with an eventual final release cannot be guaranteed.

Downloads

4.522/4.618 Win32 REORGDB

The reorgdb.m file should be posted to the 4.5xx system database with 4.5xx MAPPOST, and the reorgdb.m6 file should be posted to the 4.6xx system database with the 4.6xx MAPPOST.