>>I have a form that inserts a couple of million records into a table from a text file. This process seems to get slower and slower the longer it runs.
>>
>>I would like to delete the indexes on this table before reading in the data. Hopefully this will speed thinks up.
>>
>>I found the delete tag command that actually deletes the cdx file. Is there a way to recreate these indexes programmatically when everything is done. I looked at the index command, but I don't think this will work because I need on of the indexes to be primary.
>>
>>Is there another way to do this?
>>
>>Thanks
>>
>>Pat Murphy
>
>Pat,
>If you do delete the indexes, it may speed up the import (somewhat) but the problem will be recreating all the indexes on millions of records. You will have to go through the table once for each index and that will probably take longer than waiting for the process to update all the indexes initially.
>

Larry, I disagree strongly here; it's preferable to recreate the indexes from scratch after massive imports for several reasons:

(1) It reduces bloat caused by internal reorganization of the indexes which occur during incremental additions to an index tag. Worse when lots of tags are involved.

(2) It places all related segments of the CDX file for each TAG local to one another, which will reduce physical disk I/O (especially with a nicely defragmented media to write on!) and speeds Rushmore operations which involve retrieving a tag from shared storage to a local station by reducing disk positioning overhead when the tag gets sucked across.

(3) It keeps the target data file relatively defragmented - if both the data file and the CDX are being grown incrementally, they'll compete for potential free storage (especially a problem with less-than brilliant strategies like FAT partitions use) and end up co-mingled and fragmented.

(4) The process of creating the index may be able to make good use of free memory where available; if the total data size would fit in the available physical RAM available to the system, careful tuning of a system with a half-gig of SDRAM using the SYS(3050) command could reduce the incremental time to build all the indexes by letting VFP grab and hold the data table being indexed in its cache. Even if it spills over to virtual memory, beating on the swap file generally will be faster than repetitively sucking the table across a wire.

One obvious alternative would be to use a non-structural CDX (remember them?) - this means a bit more work opening the file, and extra file handles getting consumed, but they can be 'detached' during the import and then reattached and REINDEXed to bring them up-to-speed. It's ugly, and I would (and do) recreate the indexes from scratch because of all the advantages noted above, but they are available, just like (bletch) IDX files, and are occasionally appropriate.

Of course, the same can be said for the use of do-it-yourself lobotomy kits...