<< Click to Display Table of Contents >> Navigation:  Application development > Database driver characteristics > SQL drivers > SQL performance tips

The following tips are suggested to help reduce unnecessary processing, and speed up performance:

•Use passthrough SQL for complex queries or stored procedures.

•You can bypass BDE functions and make direct calls using the native SQL API. Use DbiGetProp to get native handles.

•Use the server to minimize the size of the returned result set.

•Return results into a local table for processing.

•Use DbiAddFilter, DbiSetRange, and DbiSetFieldMap before data access to limit the number of records accessed.

•Create a descending index if backwards navigation is done frequently.

••        Avoid moving toward the beginning of the table except within a small cache range.

•Avoid using DbiSetToEnd and DbiSetToKey in the middle of large tables or when the table is ordered on a composite index.

All options mentioned below are configurable using the BDE Administrator:

•For Microsoft SQL Server and Sybase: increase PACKET SIZE to at least 4096. You must also need to set the Packet Size option on the Microsoft of Sybase Server to match. Make sure that DRIVER FLAGS is 0. If it is 2048, queries will execute in asynchronous mode, which is slow.

•For Oracle, DB2, and the ODBC socket: try adjusting ROWSET SIZE. This specifies how many rows you fetch or insert in a single server operation.

•Set TRACE MODE to 0. This option is used only for debugging and can slow down your application.

•If your client/server applications TTables in Delphi, consider using TQueries along with cached updates to improve the performance of your overall application. TTables give you an easy model for programming and provide adequate performance but are not designed with speed in mind.

•Set SQLPASSTHRU MODE to SHARED NOAUTOCOMMIT and use explicit Begin Transaction and End Transaction statements in your application instead of relying on SQL Links to do AUTOCOMMIT.

 

See also:
Improving BDE performance
A few general suggestions for maximizing BDE's performance in accessing tables