Working with TQuery
Use TQuery rather than TTable; the latter should never be used with InterBase.
Why Not to Use TTable
TTable is very convenient for its RAD methods and its abstract data-aware model, it is not designed to be used with client/server applications; it is designed for use on relatively small tables in a local database, accessed in core memory.
TTable gathers information about the metadata of a table and tries to maintain a cache of the dataset in memory. It refreshes its client-side copy of the data when you issue the
Post method or the
TDatabase.Rollback method. This incurs a huge network overhead for most client/server databases, which have much larger datasets and are accessed over a network. In a client/server architecture, you should use
Setting TQuery Properties and Methods
Set the following
TQuery properties and methods as indicated to optimize InterBase performance:
CachedUpdatesproperty: set this property to <False> to allow the server to handle updates, deletes, and conflicts.
RequestLiveproperty: set this property to <False> to prevent the VCL from keeping a client-side copy of rows; this has a benefit to performance because fewer data must be sent over the network.
In a client/server configuration, a “fetch-all” severely affects database performance, because it forces a refresh of an entire dataset over the network. Here are some instances in which cause a
TQuery to perform a fetch-all:
Locatemethod: you should only use
Locateon local datasets.
RecordCountproperty: although it is nice to get the information on how many records are in a dataset, calculating the
RecordCountitself forces a fetch-all.
Constraintsproperty: let the server enforce the constraint.
Filterproperty: let the server do the filtering before sending the dataset over the network.
Commitmethod: forces a fetch-all when the BDE DRIVER FLAGS option is not set to 4096 (see Setting the Driver Flags), or when you are using explicit transaction control.