Главная страница | назад





Article #25617: 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.

Last Modified: 26-OCT-00