This section discusses some options that may affect performance of databases on which data is poorly sorted. Most of the content is specific to release 4.622 and Windows NT family operating systems. The last section Improving Performance of Large Databases should be of interest to users of any DP4 release.
Many DP4 applications read through the whole of a large table using the primary index. Typically the physical order of the data in the data file, and the primary key sequence for the table will be closely correlated, which means that reading the data using the primary index is usually very fast - between 30000 and 50000 records per second may be read on a typical Pentium 4 class machine. However, if the primary index and physical order of the data are not closely correlated, reading the database may be much slower - by a factor of 10 or even 20 times. One common situation where this arises, is on "central" databases, to which transaction data from many locations is being posted continuously. Typically transaction tables will use some kind of location id as the first part of the primary key. On the "local" databases all the transactions will share one location id and the primary index will be fast. However on the central database transactions from many locations are being posted. Typically the order of the data in the data file will reflect the order in which the transactions are posted to the central database, and in most cases transactions from multiple locations will be highly interleaved. Therefore reading the whole of a transaction table on the primary index will typically involve as many passes through the data file as there are locations being posted from, resulting in very poor performance. If you are using release 4.622 or later, any time you have issues with performance of the database, the first thing you should consider doing is reorganising the database. It is best to reorganise frequently, because the time taken for a full reorganisation will be much less if the database has previously been reoganised, so that older data is well sorted.
Release 4.622 attempts to improve the performance of DP4 databases by reading both the data and the index file predictively - whenever an application does a fetch(NEXT) type operation, asynchronous read requests may be done to read ahead the next (in index sequence) few locations in both the data file and the index file, in the hope that they will have been completed by the time the application requires the data. Such a read-ahead strategy is likely to be far more successful at preloading soon to be required parts of the database than the normal track buffering performed by the operating system.
Unfortunately an underlying limitation of Windows NT renders read-ahead ineffective: unless data is being read from disk at quite a low rate, Windows quickly runs out of resource to perform the reads asynchronously and does them synchronously instead, completely cancelling out any benefit in doing read ahead. However, there is a partial solution to this - most reads will be genuinely asynchronous provided an application completely bypasses the operating system's own file caching. So, in release 4.622, there is an option to do this, and read ahead is, by default, only turned on if you choose to do so.
Disabling the operating system file cache certainly has some possible disadvantages: for example when a file is closed, DP4 must instantly forget any cached information it held about that file, whereas there is no need for the underlying operating system to do so. Additionally, the operating system knows a great deal more about how data is physically arranged on the disk than DP4. Nevertheless, if your database is large enough to make it unlikely that the operating system file cache can store more than a small proportion of the file in memory, disabling the operating system file cache is very likely to be beneficial. Tests on an unsorted database with a data file in excess of 3GB suggested that performance on reading large tables could be improved by between 35% and 50% or more by disabling the operating system cache and enabling read-ahead. For example a load -delete on a particularly fragmented table with less than 50000 records reduced the time taken to delete all the records from around 340 seconds to 110 seconds.
When updating a "central" database performance is also likely to be worse than with a "local" database, because the way in which updates are made means that widely separated parts of the index need to be updated at more or less the same time, which will cause a certain amount of disk thrashing. If you choose to use disable the operating system cache DP4 uses separate threads to write information to the database asynchronously. This can improve performance significantly compared with earlier DP4 releases. For example the time taken to import 3.5 million records into an intiially empty database is reduced from 29 minutes using 4.621 to 16 minutes using 4.622 in one of the tests used for validating DP4.
In order to bypass Windows file caching completely set bypass_cache=2 in the [dp4srvr],[dbcheck], or [reorgdb] section of the DP4 configuration file.
If a database is very well sorted, for example it has just had a full 4.622 REORGDB run against it, read-ahead is of no benefit at all. However, tests indicate that it degrades performance only very marginally, by 2% or less. As the database is used in normal operation its fragmentation will inevitably increase to some extent, and read-ahead will start to be beneficial again.
If Windows file caching is disabled it may be necessary to tune the amount of memory available to DP4 for caching, and various other related parameters. By default, DP4 uses only 16MB of memory for file caching, (and even less on Linux/Unix or Windows CE) whereas the operating system will usally allocate all memory not being used for anything else.
In general, it will prove better to allocate too little rather than too much cache to DP4. If you allocate too much cache the performance of other applications on the system may be degraded, and DP4 may page fault if the system is stressed. Probably you should never allocate more than half the available memory to DP4 for caching purposes. In fact, most of the time you will notice little difference in performance even if you allocate much less than this: in tests on a machine with a RAID 0 disk controller reducing the allocation for cache from 128MB to 16MB made almost no difference to performance of some tests (but on another test it made a *4 difference in performance). One other aspect of caching that may be significant for performance is the number of disk sectors that DP4 should read and write at once. In 4.622 this is controlled by the tracksize and pagesize settings in the [progname] section. In release 4.620/4.621 DP4 typically reads and writes sectors in 4K pages, since this matches what the file mapping used by the operating system cache does. However, with file caching disabled it may be better to read and write larger numbers of sectors at once. According to Microsoft documentation on the subject reading and writing files in at least 8K chunks is recommended, and that performance is usually optimal when 64K chunks are used, corresponding to a tracksize of 128. Keeping so many sectors in a single contiguous buffer would introduce a considerable amount of "stiffness" into the caching scheme used by DP4, forcing it to retain entire 64K file chunks in memory even when only one sector is being used, which would degrade performance. Instead reads and writes are done logically in "pagesize" chunks, but at a lower level DP4 attempts to coalesce reads and writes into "tracksize" units. For example if DP4 asks to read two 4K pages separated by less than 56K, the lowest level of DP4 file I/O will allocate additional pages and perform one 64K read instead.
In release 4.622 pagesize will be forced to the "system page" size on Windows machines, if the Windows cache is disabled. This is 4K on current Windows releases on Intel platforms.
Large databases, particularly "central" databases suffering from the fragmentation problem described earlier, will benefit enormously from being reorganised regularly. In release 4.622 the -data option of REORGDB will build an optimal database. If you are using release 4.621 you should use REORGDB −DATA −ALTSORT. For a large database you should enable enhanced index compression and use an index node size of 1024 bytes. With a node size of 512 bytes the index is almost always taller than with a larger node size, and a significant amount of space in the node is likely to be wasted, especially in indexes using large character fields, so the index will be larger as well.
With a large node size fewer disk accesses are required for a random fetch(EQUAL) or an insert or update of a record. However, for most applications the only time fetch or update time becomes significant, is during a scan or update through an entire table. In this case most databases accesses will not require any disk access at all, and the dominant factor in performance is CPU time. In our test using the largest node size (3) of 4096 bytes slowed down a scan through a hole table by about 5%. However insert time was increased by as much as 50%. We recommend using a node size of 1.
As a simple example of the benefits of reorganisation LOAD -DELETE mentioned earlier, mentioned as requiring up to 340 seconds, will run in 2 seconds or less on an optimal database.
Unfortunately running a full reorganisation may take a prohibitively long time, though the more often it is done the quicker it is likely to be. There may be things you can do that would provide a significant performance improvement without the need for a full reorganisation.
If you are using release 4.622 and a "Two File Index" you can use the "Defragment Database" option to arrange the data file optimally. This option runs so fast that you can probably run it nightly, or at least weekly on even the largest databases.
One other possibility for central databases is to make data collection a two stage operation: rather than immediately post transaction data to the "real" transaction tables, first post it to "shadow" transaction tables. When there is a worth while amount of data in the "shadow" tables, or an update is desirable for some other reason, transfer the data table by table (rather than transaction by transaction) to the real tables. This will ensure that data that is logically contiguous is at least moderately physically contiguous.
A second possibility, which might work very well, would be to split the central database, either on a per-store basis, or possibly on an area basis. This would reduce fragmentation at the database level a lot, though there would be some increase in the fragmentation of the underlying file system. Reorganisations could be done on these smaller databases on a round-robin basis, and would obviously take much less time than a full reorganisation of the large database. In order to make such a collection of databases appear to be one database to reports and other applications, an ADC would be required. Although the general logic of such an ADC lends itself to a generic implementation, there would probably have to be some application specific details, though these could probably be parameterised, or controlled via an INI file.