Here are a few general programming practices to help improve overall BDE performance in accessing tables:
| 1. | Keep the number of maintained secondary indexes to a minimum; sometimes it is better to delete the index and recreate it than to perform a number of table operations with the indexes in place. |
| 2. | If possible, increase the size of the swap buffer and the number of file handles that BDE has available to it. This will decrease BDE's need to swap resources.
Note: Be sure to increase the file handles available to your application by using SetHandleCount. Also, in IDAPI.CFG, increase the number of file handles available to BDE. |
| 3. | Open the table exclusively. |
| 4. | Batch as many operations as possible--do not read or write records one at a time. Use DbiBatchMove, DbiCopyTable, DbiReadBlock, and/or DbiWriteBlock. |
| 5. | When using DbiWriteBlock, try to work in multiples of the physical block size, usually 2K or 4K. |
| 6. | If you are opening and closing one or more tables repeatedly, consider calling DbiAcqPersistTableLock on a non-existent file after you initialize the BDE. This will create the .LCK file so that it will not have to be created each time a table is opened, created, and so on. (Note: You'll also want to call DbiRelPersistTableLock before calling DbiExit). This applies to Paradox tables only. |
| 7. | Work with in-memory tables when possible. |
| 8. | When working with remote data sources that support transactions, use explicit transactions. For example, each insert to a table on an SQL server will force a transaction to be started and committed. This adds a lot of overhead when inserting a large group of records. Instead, start a transaction, insert a group of records, and then commit the changes as a group. |
All options mentioned below are configurable using the BDE Administrator:
| 1. | Set LOCAL SHARE to False. This option should only be TRUE if both BDE and non-BDE applications are accessing dBASE or Paradox tables simultaneously. Borland products all use the BDE to access Paradox and dBASE tables, so this option can be FALSE if you're using only Borland applications. |
| 2. | To improve performance of opening tables and updateable queries for a Server BDE Alias, set ENABLE SCHEMA CACHE to TRUE. Do not use the schema cache if your application is constantly creating tables or altering the structures of existing tables. |
| 3. | Try adjusting BATCH COUNT. This is the amount of records processed in a single transaction in a BatchMove operation. This also affects the performance of the Data Migration Expert. |
See also: SQL performance tips
