Performance techniques when using BDE and Delphi TTables with InterBase

By: Borland Staff

Abstract: Various options for improving performance when using a TTable with an InterBase database

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.


Server Response from: ETNASC03