Performance techniques when using BDE and Delphi TTables with InterBase

Abstract: Performance techniques when using BDE and Delphi TTables with InterBase

Problem: With the default BDE configuration of the native InterBase driver, when the Delphi component TTable is used in order to fetch a set of rows, an InterBase cursor is setup in order to track the specific row that is being referenced in the dataset. When any modification to the rows is made (insert/update/delete), the Interbase API function isc_commit_transaction() is called resulting in a commit of the data and a loss of cursor. The closing of the InterBase cursor triggers a Delphi "fetchall" which reloads all of the data accross the connection. Other actions against the selected data, such as locatelist, recordcount, and using filters also result in a "fetchall" command in the default environment (driver flags set to NULL or 0). With large data sets, this can be a very slow and network intensive operation. Multiple users doing this against the same datasets simultaneously can easily slow down the server significantly. Solution: There are several possibilities for reducing the impact of using TTable components with large data sets. Some of those suggested are : 1. In the BDE Configuration utilty, you can set the MaxRows parameter to a restrictive value so that only the specified number of rows will be fetched. This is, in general a good idea when it is possible for the user, in advance, to narrow down the search so that it is very likely that the specified number of rows will contain the desired information. To make this user friendly, it may be necessary to code the search such that the next set of rows after the current set can be easily obtained (such as a "Show Next" button). 2. It is possible to modify the InterBase driver flags on a global level to change the driver options for record locking and cursor retention. Since these flags are global to an application, care is required in order to ensure correct behavior. If the BDE -> Configuration -> Drivers -> Native -> Intrbase ->drivers flag is set to 4096 or 4608, the InterBase API call upon commit will typically be changed from isc_commit_transaction to isc_commit_retaining. This will not close the InterBase cursor, hence avoiding the resultant "fetchall". Note: the TDatabase.commit method ignores this flag and issues an isc_commit_transaction which will close the cursor and trigger the "fetchall". Sideeffects: ============ Setting the driver flag to 4096 ------------------------------- This may lead to potential deadlocks of multiple users attempt to modify the same row at the same time. If it is likely that multiple users will attempt to modify the same rows frequently, this solution is not workable. Also, when the user is done with the information, a hard commit should be done to free up the locks established on the table. Setting the driver flag to 4608 ------------------------------- Any changes to data made by other users during the time this transaction is open will not be available since the transaction is scoped as read-committed at the time of the original request to fetch this information. If it is important for one user to see changes made by another user in a timely manner, this solution is not suitable. When a user is done with modifications, a hard commit will allow other changes to the database to be viewed. See article in InterCom Vol 1 Number 2 (about handling deadlocks) for more information on possible driver flags.