We are wrapping up an application. During testing we found that when we reindex the tables, the performance drops drastically. Here is the situation:

We have a table with about 32,000 records. The data in this table was generated by importing data from another data source with a VFP import program we wrote. The table has about 40 fields, one being a memo field.

It is accessed in a fairly complicated view with several joins in a run-time query system. To return a single record, including the overhead to draw the form, it took less than 1 second. All is well.

We reindexed the file. After the reindex, the same exact test takes over 50 seconds to return the same single record.

We tried a couple things: We use SDT. We reindex the table using SDT - no difference. We reindex the table using the standard tools in Mere Mortals - no difference. We tried restoring the data and taking single passes through each of the above scenarios. All had the same result: Untouched table = 1 sec, reindexed table = 50 secs.

If we Pack the table, the performance is almost as good as new, about 2 seconds.

Note that either reindexing using SDT or VFP or Packing all created a CDX that was smaller (1.5MB down from 2.4MB)

Looking in SDT or the table modifier in VFP shows no signs of corruption.

Why would reindexing the table make the program run 50 times slower?