>Hi Bob ---
>
>Is it possible that one of the index tags was on a field whose length was shortened since the last index creation? Thats one possible explanation. Also, the complexity of the tags and/or the field length definitely impacts load time and index size. Lets say you have lastname and firstname columns of 50 bytes each. An index on lastname+firstname requires a 100byte+ space in the CDX file.

No structural changes since (or before) the last INDEX ON ...
No changes in index complexity between the drop from 8 seconds to 2 seconds.


>As to the future, are the plans to shorten the table due to better normalization? That will help speed a lot as well.

Normalization is the real reason for the change. I am expecting a big change in speed though due to a more reasonable width.


>Finally, it is in everyone's best interest if you could schedule a weekly "Down hour" to PACK the tables and recreate the indexes. It's a tough sell, but it'll be easier to work with than if the system goes ker-splat due to infrequent maintenance.

"Down hour" is not available. Down few minutes is available. This has nothing to do with selling an idea. This is a system requirement. I am looking to design a system that does not cause built-in maintenance problems.

.......................

>>I have a form that was taking a long time to load. It was opening 1 table in its data environment, which was 230 MB (150,000 records) with a 19 MB CDX index file. In init, there was a LOCATE command, and one of the 11 indexes was on deleted()
>>
>>If the LOCATE command was removed, the load time dropped from 8 seconds to 2 seconds. The index was deleted and then recreated. Its size dropped from 19 MB to 14 MB. The table now loads in 2 seconds with or without the LOCATE command.
>>
>>Did I have "index bloat", to use a phrase that I have heard? What is index bloat? How do I prevent index bloat from happening in the first place.
>>
>>This is a poorly designed table with an extra-wide 161 fields with a total width of 1534 characters. There are plans to drop it to about half its width in the near future. Could this large width be contributing to my problems. The system did have a couple of unrelated crashes recently so the index file’s integrity was suspect, but the seeks always worked.
>>
>>This is a 24 hour application and it is tough to get the users to free up two or three minutes to do system maintenance, so I am willing to change my file handling approach if it can remove the need for a periodic reindex.
>>
>>Bob