Detecting CDX changes

When you add a record or modify an indexed field, you expect to find them on any computer that runs your application. The simplest approach would be for Visual FoxPro to read the CDX file every time you search for a record. We can call this the Java compliant mode because when you run your application you would have enough time to get a new cup of coffee.
Instead FoxPro uses a solution that is similar to many other caches, like the ASP.NET cache. At position 8 the CDX file header has a 32 bit field that is documented as "for internal use only". Whenever Visual FoxPro updates the index file it increments this field by one. To find out if the index file has changed, Visual FoxPro merely reads the first 16 bytes and compares the value against the last value it read. If they differ, the index must have been updated. In this case, VFP invalidates all cached index entries.

Visual FoxPro does not read the header every time you access an index entry. SET REFRESH defines the interval in which VFP performs the update check. The default value is five seconds. So any change to the index file that you make on one machine takes up to five seconds to become visible on a different machine. You can reduce this value to see changes quicker. This comes at price, though. You increase the network traffic which might reduce the performance of your application.

This design is one reason why applications perform faster when everybody is just reading data. Unfortunately, this approach has one drawback when multiple computers update index records. To update the index file, Visual FoxPro has to lock the header. Hence, only one machine at a time can add a record or update an indexed field. Whereas, multiple computers can update non-indexed fields at the same time.

With huge data sets this becomes an even bigger issue. The more records you have in a table the deeper is the index tree. Changing one indexed value in a single record might require updating one index block in a small table, but a dozen blocks in a huge table. That's one factor that contributes to the slow down of applications that change huge volumes of data in Visual FoxPro.