>How often do you recommend re-indexing from scratch ?

It deends on the app and environment; typically, most of my apps will rebuild the indexes of the main tables during a routine maintenance process such as an end-of-month processing procedure, and more often if necessary. ANy time the data needs to be PACKed, it probably should be reindexed; if bulk data transfers take place, it's my usual practice to remove all tags before adding the bulk data or archiving out data, and rebuild all the indexes after the bulk process completes.

>What exactly do you mean by sequential rebuilding ?

FOR EACH MAJOR TABLE IN DBC
   USE table EXCLUSIVE
   DELETE TAG ALL
   *  if feasible, defragment the disk volume 
   PACK && or equivalent COPY
   *  if feasible, defragment the disk volume again
   INDEX ON <i>blah</i> TAG <b>blah</b>
   *  rebuild each tag one after another
   *  REATTACH TO .DBC
   *  Final step is to recreate pKey using ALTER TABLE and use
   *  SDT to fix up the database
   USE
ENDFOR
CLOSE DATABASE
USE MyDBC.DBC EXCLUSIVE
PACK
USE

>Is the order in which the fields are indexed important ?

Not particularly from what I see; the last index built is the PKey, because that's done by SDT when restoring the .DBC via ALTER TABLE.

>How much does the need to rebuild depend on the no of records added, deleted ,changed ?

It generally appears that tables which change a lot, especially where values used by tags in existing records will change during the life of the data, will have more problems than tables thatdon't change much, or often. If you drive a car 1,000 miles/month, it'll probably have more problems than a car driven 200 miles/month on similar roads, and fewer problems than one driven 5,000 miles/month.

>Do files that have records added frequently need to be indexed more often ?

Seems to be the case. Frequent additions and changes result in more fragmentation of the CDX, and more index bloating than relatively static files. The files are also more likely to be physically fragmented. Defragmentation of physical storage, logical content, and removal of bloat all improve performance.

>How are you on ODBC speed inconsistencies with Foxpro ?

Not nearly as well informed as people who deal primarily with ODBC and ADO issues.