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.

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.