Indexes on a Linked Text file

[kkitt]

Is there anyway to create indexes on the fields in a Linked Text File. I have a file that contains approx 3 million records that i would like to link to rather then import on a daily basis. The import takes forever where the link is very quick. Only drawback is that I not been able to find a way to create any Indexes on the linked table and performance takes a major hit hit.

Any suggestions.

Thanks in advance.

 

[BNPMike]

One of the reason the import takes a long time is Jet is setting up the data in a format conducive to rapid data access. If you were to find a way to index the text file that process would also take up time.

What you might try is different ways of importing. If an index is involved you may want to try sorting the file externally ie before you bring it in. Use a standard sort package. Then try creating the index after loading compared to during the import. Also obviously try and get rid of unneccessary indexes and data. After that, the usual things - plenty of memory, fast discs, fast processor, no other processes running etc

 

[DrSimon]

BNPMike is right. Sure it's quick to link to a table like that, but you're paying for the lack of index in performance later. I've never compared the efficiency, but as an alternative you could write some bespoke code that reads the text file line by line and then saves into a table. I suspect you have no control over the content of the big file; but if you do, you could consider whether it could be normalised and reduce the amount of data to be imported.
Simon Rouse