Title: BDE and SQL Links Performance Tips
Question: Some tips taken from Dunstan Thomas Website. There are also other useful InterBase links there...
Answer:
Here are a few tips to improve performance of BDE and SQL Links in general:
On the System page in the BDE Configuration utility, set LOCAL SHARE= False. This option needs to be set toTRUE only if both BDE and nonBDE applications are accessing dBASE or Paradox tables simultaneously.All Inprise products use the BDE to access Paradox and dBASE tables, sothis option should usually be FALSE.
If you don't have it already, get SQL Links 3.5. This is availableby buying Intrabuilder/Client Server edition or as part of maintenancefor Delphi 2.0.
If you wish to improve performance of Opening Tables and UpdateableQueries for a Server BDE Alias, set the ENABLE SCHEMA CACHE = TRUE.The only situation where you cannot do this is if your application is constantlycreating tables or altering the structures of existing tables. We havenoticed improvements of upto a factor of 10 times when we use this option.
For Microsoft SQL Server and SYBASE : Try increasing the PACKETSIZE to at least 4K. You also need to set the Packet Size option onthe Server to match. This option is new for SQL Links 3.5.
For ORACLE, DB/2 and ODBC Socket : Try tuning the ROWSET SIZEoption for the alias or the driver in the BDE configuration. This specifieshow many rows you fetch or insert in a single Server operation.
Try tuning BATCH COUNT parameter for the Database Alias. Thisis the amount of records processed in a single transaction in a BatchMoveoperation. This will also affect the performance of the Data Migrationtool.
Make sure that TRACE MODE = 0 (on driver page of BDE Configurationutility). This option is used only for deugging and can tremendously slowdown your application.
For MSSQL and SYBASE : Make sure that DRIVER FLAGS = 0 (on Driverpage of BDE Configuration utility). If it is 2048, queries will executein Aynchronous mode, which is very slow.
Are your C/S applications in Delphi 2.0 using TTables? If they are,you should consider using TQueries along with Cached Updates toimprove the performance of your overall application. TTables give you aneasy model for programming and provide adequate performance in lots ofcases but are not designed with speed in mind.
Using explicit Begin Transaction and End Transaction in your applicationwill usually be much faster than relying on BDE SQL Links to do AUTOCOMMIT.In other words, using the BDE Configuration utility, set the value of SQLPASSTHRUMODE = SHARED NOAUTOCOMMIT for the Database Alias and control Transactionsexplicitly in your application.