I've developed an application using DBISAM, and I'm experiencing performance problems. What can I look for in terms of optimizations ?

Frequently Asked Questions

I've developed an application using DBISAM, and I'm experiencing performance problems. What can I look for in terms of optimizations ?

There is no easy answer to this question, even though we get it very often. However, here are some things to look for:

Problem Area

Potential Optimizations

A query or filter is slow	DBISAM requires indexes with the proper case sensitivity settings in order to optimize SQL queries and expression filters. You should try to at least make sure that WHERE clause and filter expressions contain at least one condition, preferrably the most restrictive condition in the expression, that is fully optimized using an available index. Also, SQL join conditions must be optimized in DBISAM or the performance will be terrible. A description of the expression filter optimizations can be found here. A description of the SQL optimizations can be found here. In addition, you can use the query plan functionality found in the TDBISAMQuery GeneratePlan and Plan properties to analyse everything that DBISAM is doing in the course of executing a query, including all optimizations and changes that are made to the query. This query plan functionality is a very useful tool for evaluating the performance of a given query.
Loading a form with a lot of lookups is slow	This is particularly a problem for client-server applications using remote sessions to connect to a DBISAM database server. The only solution is to reduce the number of lookups occurring or change the application so that it caches lookup tables locally in the client application in DBISAM in-memory tables. Lookups tend to cause a lot of requests to be issued to the database server, and remote client-server access is particularly sensitive to the number of requests and responses due to the turnaround time on a request and the subsequent response.
Looping through many records is slow	This is usually only an issue with client-server applications that use a First..while not EOF..Next type of navigational processing on large tables. The solution is to use SQL or to use a RemoteReadSize setting for the table that allows records to be read in larger chunks as opposed to one at a time. As discussed in the previous item, the key with remote client-server access is to reduce the number of requests, not necessarily the size of the requests. DBISAM can be configured to compress data being sent to and from a DBISAM database server, so the amount of data is rarely an issue.
Multi-user performance on a network drops when a second client accesses the data	This is an issue only for non-client-server, multi-user applications that use Windows clients to access DBISAM tables on an SMB (Server Message Block) file server. For Windows, this means a Windows 2003 or higher server. For Linux, this means a server running the Samba server software that allows Linux machines to behave like Windows file servers. The solution to this problem is to bump up the memory buffer settings in DBISAM so as to prevent DBISAM from trying to read data from the file server as often as it does with the default memory settings. Usually tripling or quadrupling the default memory settings is enough to alleviate the problem. The general idea is to keep bumping up the memory settings until the performance becomes acceptable.

More Support Options

Support Forums

Technical Bulletins

Technical Articles

Incident Reports

Product Manuals

Roadmap