27 Sep 2000The original version of this enhancement introduced a bug into the private database facility, causing it soemtimes to fail to return the updated state of a record.
We have made some improvements to the performance of the DP4 database manager. These will be most apparent during lengthy processing local to the server, such as end of day processing, large reports, complex SQL queries, or when running programs such as RECASTDB. For these programs you can probably expect a performance improvement of around 15-20% for programs that mainly read the database, and 10-15% for programs that mainly update the database. In addition we have made some optimisations to the the Private database facility, which can help certain badly written programs, and SQL statements to execute anything up to ten times faster.
The less spectacular improvements are due to improvements in the file buffering inside the database manager. Previously there was a considerable processing overhead in maintaining the list of buffers for database files sorted on address, in order to reduce head movement when updating the database. For programs that did not update the database this sorting was a complete waste of effort, which could degrade performance. In the new version buffers are kept unsorted and only sorted when they need to be written. In addition "write" buffers are now kept in memory somewhat longer than "read" buffers so that more writes can be batched together.
We have also increased the number of transactions that are allowed between rollback log flushes. Previously the rollback log was flushed every 256 transactions. The idea behind this was to limit the length of time DBRECOV would take to run. As in almost all cases DBRECOV takes only a few seconds we have increased this limit to 1024 transactions (though the rollback file will be flushed sooner if it reaches a size of 256K). This has no impact on the security of data in the database, as these transactions are available from the transaction log, or the temporary transaction log if transaction logging is disabled.
The more dramatic enhancement will only affect a fairly small number of programs. There is a performance problem in DP4 when a transaction becomes very large - with in excess of one thousand updates per commit unit. This problem is much worse for programs that use the private database and do something like:
while (rec_fetch_main(NEXT,&table.l)) /* or even worse fetch on a secondary index */
rec_post(&table.l);
db_commit();
In some circumstances DP4 must search through the work file of already posted records before returning from rec_fetch() or rec_fetch_main(). As more and more records are updated the fetches become slower and slower. The best way to avoid this kind of problem is to call db_commit() and intervals of every 20 -100 records, and to avoid using the PRIVATE_DB flag at db_open time or to specify NEXT|NO_UPDATE instead of NEXT on the rec_fetch_main(). However you can't do this from SQL, so SQL such as DELETE FROM TABLE without a WHERE clause executed very slowly on large tables. We have also added a configuration option to ODBC to allow the private database feature to be turned off. (See E4000017.)
In previous versions the entire work file (which contains all the records updated in this transaction) would be searched whenever the table being fetched was also updated, or whenever more than three tables were being updated in the current transaction. In the new version this search won't be necessary, for searches with rec_fetch_main(), as long as there have been no inserts on the table in this transaction, and there have been inserts into no more than ten other tables. Therefore batch updates or deletions from programs using the private database will be significantly faster if they did not specify NO_UPDATE to disable the private database for that part of the program.
The work file has a small hash index at the start, which for large transactions reduces the length of time it takes to search the work file for an update to a particular record. In the new version of the database manager the size of this hash table is configurable, with command line options to SRVn and a program can elect to use a larger hash table than usual by passing the flag BIG_TRANSACTIONS (4096) to db_open(). The updated SQLLIBT.DLL supplied with E4000017 uses this flag and it can improve performance dramatically - in one preparing a delete of 8000 records took 3 seconds instead of almost five minutes. (The time to do the actual commit is not affected however and as the commit for this transaction took 30 seconds the overall improvement is only about ten-fold). You need to have both E4000016 and E4000017 installed to get this performance improvement.
These programs accept two new command tails.
This specifies the size of the per-user private database hash as a multiple of 512 bytes (1=512,2=1024). The value must be in the range 1 through 8. The default value is 1. Specifying a larger size will only improve performance if you have a lot of programs that do quite large commits (probably in excess of 100 records updated in one go). Performance may be adversely impacted if your system has a lot of users, as the hash area reduces the memory available for other file buffering.
This is similar to -workhash but specifies the hash size for programs that specify the BIG_TRANSACTIONS flag to db_open(). Currently the only such programs will be programs that use the new DP4 SQL Library. The value of n for -bighash can be between 1 and 32. The default value is 8. Roughly speaking the time to execute a statement such as the example code above is reduced by a factor of somewhere between two and four each time the size of the hash table is doubled, but only so long as the number of records updated in the transaction is bigger than the size of the hash table/4.
For example in one test, preparing a delete of 38000 records took around 90 seconds with the default hash size, and 45 seconds with a hash size of 8192 (-bighash 16). However the actual commit time for this transaction was 16 minutes, so 4096 is probably the largest useful value. (Such a transaction should NEVER be performed on a live database as all other users would be locked out for the whole time). If your system has many programs connecting to DP4 simultaneously via SQL/ODBC you might want to consider reducing the bighash value to 4 or even 2, in order to prevent performance degradation due to lack of file buffers.
See E4000017 for details and the download for the updated SQL/ODBC files.
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.