Improve your Delphi application performance – Using BDE cross configuration features

In 2000, Borland introduced a new SQL driver architecture called "dbExpress". dbExpress is designed to deliver ultra high performance data access and simplify deployment and configuration of SQL drivers. dbExpress is a pure SQL driver architecture and does not use BDE technology. dbExpress is also designed to be highly portable and drivers are being delivered both for Windows and Linux. This new driver architecture replaces the SQL data access functionality of the "older" BDE SQL Links combination, but does so without the runtime and deployment overhead of the BDE.

At this time dbExpress (soon to be known as DataSnap Direct) is the recommended driver solution for SQL server database access in Delphi and C++Builder. BDE SQL Links will ship in the next Delphi product release but will be marked 'deprecated'.

dbExpress was specifically designed as a replacement technology for SQL Links, so we have kept migration and transition in mind from the beginning. The Delphi and C++Builder database application development architecture is designed to allow different data access technologies to plug in underneath the Dataset layer. This unique design abstracts the database drivers from the basic development components and source code such that new driver architectures can be developed without breaking the basic database application development APIs. This allows the same dataset-based programming model, data aware visual components, and DataSnap Middleware to function without change. Transitioning from SQL Links to dbExpress can be as straight forward as swapping the SQL Links drivers with dbExpress drivers. Some additional effort may be required to decouple an application from BDE specific features that a developer may have used in the SQL Links components. All of this means that the transition effort from SQL Links to dbExpress drivers is designed to be low impact and for most developers will require minimal source code changes.

BDE configuration Utility for various Database links:

On the System page in the BDE Configuration utility, set LOCAL SHARE= False. This option needs to be set to TRUE only if both BDE and nonBDE applications are accessing dBase or Paradox table’s simultaneously. All Inspires products use the BDE to access Paradox and dBase tables, so this option should usually be FALSE.

If you don't have it already, get SQL Links 3.5. This is available by buying Intrabuilder / Client Server edition or as part of maintenance for Delphi 2.0. If you wish to improve performance of Opening Tables and Updateable Queries for a Server BDE Alias, set the ENABLE SCHEMA CACHE = TRUE. The only situation where you cannot do this is if your application is constantly creating tables or altering the structures of existing tables. We have noticed improvements of up to a factor of 10 times when we use this option.

Microsoft SQL Server and SYBASE:

Try increasing the PACKETSIZE to at least 4K. You also need to set the Packet Size option on the Server to match. This option is new for SQL Links 3.5.

ORACLE, DB/2 and ODBC Socket:

Try tuning the ROWSET SIZE option for the alias or the driver in the BDE configuration. This specifies how many rows you fetch or insert in a single Server operation. Try tuning BATCH COUNT Parameter for the Database Alias. This is the amount of records processed in a single transaction in a BatchMoveoperation. This will also affect the performance of the Data Migration tool. Make sure that TRACE MODE = 0 (on driver page of BDE Configuration utility). This option is used only for debugging and can tremendously slowdown your application.

MSSQL and SYBASE:

Make sure that DRIVER FLAGS = 0 (on Driver page of BDE Configuration utility). If it is 2048, queries will execute in Asynchronous mode, which is very slow.

If your C/S applications in Delphi 2.0 using TTables, you should 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 in lots of cases but are not designed with speed in mind. Using explicit Begin Transaction and End Transaction in your application will 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 Transactions explicitly in your application.

In CATTLtd we will take care the performance of the application to make the application faster. The TMS (Tele marketing system), RW Reports for Gold Mine are configured with different RDBMS (Paradox, dBase, Interbase, MSSQL2005, Mysql), The applications will run smoothly and quickly based on different application database configuration. The RWreport will generate dynamic reports with simple steps for different Gold Mine RDBMS configuration.

Posted by : Anil Chowdary D.