Application Development Tools
Go Up to Database and Server Performance
This section describes ways you can develop applications that are efficient, using various popular development environments and tools.
Contents
InterBase Express™ (IBX)
InterBase engineers have created a full-featured set of data-aware VCL components for use with the TDataSet
architecture in Delphi. IBX can also be used with Embarcadero’s C++ Builder. See the Developer's Guide for full documentation of InterBase Express.
IB Objects
Another set of VCL components is available for projects with Delphi. It is designed to provide very sophisticated data component technology that is optimized for use with InterBase. The demo product can be downloaded from http://www.ibobjects.com
.
Visual Components
This section describes visual components that developers commonly use in Delphi and C++Builder to access data from InterBase. Follow the recommendations below for better client/server performance.
Understanding Fetch-all Operations
In a client/server configuration, a “fetch-all” is the nadir of performance, because it forces BDE to request that the database generate a dataset again and send it over the network.
InterBase and most relational databases do not keep datasets in cache on the server in case the client requests a refresh. InterBase must execute the SQL query again when the BDE requests a refresh. If the query involves a large quantity of data, or complex joining or sorting operations, it is likely to take a long time to generate the dataset.
It is also costly for the server to transfer a large dataset across a network interface. It is more costly by far than it is for a desktop database like Paradox to return a dataset, because a desktop database typically runs locally to the application
It is often the case that software developers choose to use a relational database like InterBase because they are managing a larger amount of data than a desktop database like Paradox can handle efficiently. Naturally, larger datasets take more time to generate and to send over a network.
The person using the client application perceives that it has better performance if the user does not have to wait for refreshes. The less often the client application requests a refresh of the dataset, the better it is for the user.
- Important: A principle of client/server application design is therefore to reduce the number of costly refresh operations as much as possible.
TQuery
CachedUpdates
= False
Allows the server to handle updates, deletes, and conflicts.
RequestLive
= False
Setting RequestLive
to False can prevent the VCL from keeping a client-side copy of rows; this has a benefit to performance because it reduces the network bandwidth requirement
- Below are some operations in which a TQuery perform a fetch-all. Avoid these as much as possible, or be aware of the cost of such operations.
Using the Locate
method:
You should use Locate
only on local datasets.
Using the RecordCount
property:
It’s convenient to get the information on how many records are in a dataset, but when using InterBase, calculation of the
RecordCount
itself forces a fetch-all. For this reason, referencing the RecordCount
property takes as much time as fetching the entire result dataset of the query.
A common use of RecordCount
is to determine if the result set of an opened TQuery contains any records, or if it contains zero records. If this is the case, you can determine this without performing a fetch-all by testing for both EOF
and BOF
states. If both end of file and beginning of file are true for the dataset, then no records are in the result set. These operations do not involve a fetch-all.
For example, for a given TQuery
instance called qryTest
:
qryTest.Open; if qryTest.BOF and qryTest.EOF then begin // There are no result set records. end else begin // There are some result set records. end;
Using the Constraints
property:
Let the server enforce the constraint.
Using the Filter
property:
For the TQuery to filter records, it must request a much larger dataset than that which it subsequently displays. The InterBase server can perform the filtering in a much more efficient manner before returning the filtered dataset. You should use a WHERE
clause in your SQL query. Even if you use a WHERE
clause, any use of the TQuery.Filter
property still forces a fetch-all.
TTable
The TTable
component is designed for use on relatively small tables in a local database, accessed in core memory. TTable
gathers information about the metadata of the table and tries to maintain a cache of the dataset in memory. TTable
refreshes its client-side copy of data when you issue the TTable.post
method and when you use the TDatabase.rollback
method. This incurs a huge network overhead for client/server databases, which tend to have larger datasets and are accessed over a network. You can observe the activity of TTable
with the SQL Monitor tool. This reports all calls to the BDE and InterBase API.
Though TTable
is very convenient for its RAD Studio methods and its abstract data-aware model, you should use it sparingly with InterBase or any other client/server database. TTable
was not designed to be used for client/server applications.