Notes about network programming with the BDE

Copyright © 2000 Ernesto De Spirito

Paradox databases

All applications accessing tables of the same Paradox database should use the same PDOXUSRS.NET file. Record locking and other features depend on this. Failure to use the same PDOXUSRS.NET file may result in corrupt data and data loss.

The setup program installs the PDOXUSRS.NET file in the root directory of the first hard drive of each computer where you run it (C:\), and sets the NET DIR configuration in your IDAPI.CFG file to that directory (C:\). Therefore, by default, all applications of a single workstation share the same PDOXUSRS.NET file, but not all applications running in the network!

There are two ways of correcting this. One way is changing the IDAPI.CFG file in each workstation:

1. Run the BDE Administrator (you'll find it the Control Panel)
2. In the menu "Object" choose "Open configuration..."
3. Open the IDAPI.CFG file
4. In the "Configuration" tab go to Configuration/Drivers/Native/Paradox
5. Change the NET DIR entry to point to the location of the shared PDOXUSRS.NET file. This can be for example F:\ in one workstation or E:\ in another, depending on how the drive mappings are configured in each workstation. To avoid dependency on drive mappings and use a uniform name in all workstations, you can use the network path \\MACHINE\RESOURCE\[PATH\], where MACHINE is the name of the server where the file is stored, RESOURCE is the name of the shared resource (a directory) and optionally PATH is the subdirectory path where the PDOXUSRS.NET is located.

   For example, if the file is located at C:\ALL\DATABASE\PDOXUSRS.NET in the server named "SERVER" that shares the C:\ALL directory under the name "SHARED", then the NET DIR in all workstation (including the server) should be \\SERVER\SHARED\DATABASE\

6. Save the changes (Object / Save As configuration).

The other way to do it is setting the NetFileDir property of the Session object of your applications, before opening any table or query. For example:

  Session.NetFileDir := '\\SERVER\SHARED\DATABASE\';

Improving performance

The BDE has some serious problems regarding performance in a network, and this is why there are so many replacements for the BDE going around:

  BDE Alternatives Guide

It is not much what can be done to improve the performance of the BDE, but you might want to try the following tips to see if they help.

CACHED UPDATES

Using cached updates apparently not only caches updates to a table, but it also caches records read from the table. The problem is that it makes things more complicated and has some disadvantages... You can find information about cached updates in the Delphi help file:

    Developing Database Applications
        Working with cached updates

CACHING COMPONENTS

If you search the Internet you should be able to find replacements for some data access components (like TTable and TQuery) and some data controls (like TDBGrid, TDBLookupListBox and TDBLookupComboBox) that perform some caching to render a better performance. These components are usually not freeware, but we suggest you give them a try because they might be well worth their registration fees.

KEY RANGES

Key ranges are by far more performant than filters. You should apply key ranges instead of filters whenever possible to reduce the number of records and then if necessary use filters to get the records you want. For example, if your filter condition looks like this:

CustNo = X AND SaleDate > D1 AND SaleDate < D2 AND Code = Y

and you have an index by CustNo and SaleDate, then you should use that index and apply a key range (SetRange([X,D1], [X,D2]);) and then use a filter with the condition Code = Y.

PREPARE QUERIES

If you use a query more or less often you can prepare it (with the Prepare method) to make the BDE compile it and keep it compiled until you unprepare it (with the Unprepare method). When a query is prepared it executes faster.

PACK TABLES

Deleted records occupy space and when deletion of records happens more or less often, sooner or later the deleted records become more than 50% of the total number of records. Unused disk space is not itself the problem, but network bandwidth gets wasted transferring records that won't be used. For this reason you should pack the tables removing the deleted records from time to time. The example of DbiPackTable in the help file shows clearly how to accomplish this.

LOCAL TABLES AND CLIENT DATASETS

Maybe the best and simplest way to reduce network traffic and improve performance is to have a local copy of some of the tables of the database and use these local copies to read records. When you make an update on a table, you should update both the local and the server table. This insures the server database is always updated.

The problem is that the changes made by other users won't be visible to your application until you "refresh" the local tables (copy them again from the server), and therefore this technique should only be used with tables for which it is pretty unlikely two or more users will add or edit the same record record with different data.

You should refresh the local tables from time to time: when Windows starts, when your application starts, once a day, once a week, when the user presses a Refresh button, or whenever... It will depend on the table, and you can judge case by case: some tables may require more frequent refreshes than others, and you should take advantage of this fact to avoid refreshing tables more often than needed.

If you use Paradox tables, you should use a different session for the local tables with its own NetFileDir containing the location of a local PDOXUSRS.NET file.

If you have the Enterprise Edition of Delphi, you can use Client Datasets for some of the local tables. Client Datasets store the records in memory (so you shouldn't use them with big tables) and they are very fast and powerful.