Read textfile, search database, append results

[Slider]

Hi guys

Looking to try and write an application which will read an input file (textfile, excel, access file), search an access database on one particular field (from the input file) and then append upto 10 fields (from access) to the input file, then re-write out.

The access database I am using as a lookup file is very large, approx 1.9 million records.

What is the best process for search on this number of records.

I was thinking of reading the textfile in. Parsing the record and getting user input to select the search field. This search field may not be in a consistent field on the input field. It could be field 6 through to 10. Once the user has selected the field, I was going to use a SELECT * FROM Table1 WHERE field = userfield.

Is this the best method of searching a large quantity of data?

 

[newguy]

Part of your question is what are they looking for, and how many hits would that bring? Is there something else to narrow the search i.e. date/time, category etc...? There are some websites I have seen that will not search until enough criteria are met - cause they too have too large of a database to just show it all at one time. I find XML files work better than text files - not that it won't work, just easier if you only changing 1 element's data.
Just some thoughts..

 

[compguru910]

How many index's are their in the database. Thats the best way to speed up querys in database searches. If you can use a query like "SELECT * FROM Table WHERE index1 = 'value' AND index2 = 'value' . That speeds things up greatly cause that is how the database natively sorts things anyways.

 

[Slider]

Thanks both.

The search is on one field. It's a UK postcode.

Basically it's to find the postcode in the Access database, then append several fields from the access database onto the input file.

 

[compguru910]

If that is the case, I would move from using an access database to something a little more powerful since your database has 1.9m entries. Access can query it, but its slower than MySQL or SQL Server