Optimizing the InterBase SQL Links Driver

From InterBase
Jump to: navigation, search

Go Up to Programming Applications with Delphi or C++ Builder


Use the BDE Administrator to configure the InterBase SQL Links driver. To start the BDE Administrator, select it from Delphi or C++ in the Programs menu. To view the InterBase driver definition, click on the Configuration tab, and then expand Drivers and Native from the Configuration tree. Click on INTERBASE to display the InterBase driver settings.

To optimize the InterBase driver, you can change the following options:

  • DRIVER FLAGS
  • SQLPASSTHRU MODE
  • SQLQUERY MODE

These are discussed in the following sections.

Setting the Driver Flags

Depending on your database needs, you should set the DRIVER FLAGS option to either 512 or 4608 to optimize InterBase. The recommended value for DRIVER FLAGS is 4608.

  • If you set DRIVER FLAGS to 512, you specify that the default transaction mode should be repeatable read transactions using hard commits. This reduces the overhead that automatic transaction control incurs.
  • If you set DRIVER FLAGS to 4608, you specify that the default transaction mode should be repeatable read transactions using soft commits. Soft commits are an InterBase feature that lets the driver retain the cursor while committing changes. Soft commits improve performance on updates to large sets of data.

When using hard commits, the BDE must re-fetch all records in a dataset, even for a single record change. This is less expensive when using a desktop database because the data is transferred in core memory. For a client/server database such as InterBase, refreshing a dataset consumes the network bandwidth and degrades performance radically. With soft commit, the cursor is retained, and a re-fetch is not performed.

Note: Soft commits are never used in explicit transactions started by BDE client applications. This means that if you use the StartTransaction and Commit methods to explicitly start and commit a transaction, then the driver flag for soft commit is ignored.

Setting the SQL Pass-through Mode

The SQLPASSTHRU MODE option specifies whether the BDE and passthrough SQL statements can share the same database connections. By default, SQLPASSTHRU MODE is set to SHARED AUTOCOMMIT. To reduce the overhead that automatic transaction control incurs, set this option to SHARED NOAUTOCOMMIT.

However, if you want to pass transaction control to your server, set this option to NOT SHARED. Depending on the quantity of data, this can increase InterBase performance by a factor of ten.

The recommended setting for this option is SHARED NOAUTOCOMMIT.

Setting the SQL Query Mode

Set the SQLQRYMODE to SERVER to allow InterBase, instead of the BDE, to interpret and execute SQL statements.

Retrieved from "http://docwiki.embarcadero.com/InterBase/XE7/e/index.php?title=Optimizing_the_InterBase_SQL_Links_Driver&oldid=22986"