The need for speed

[Jonathan]

Hi, I'm am running queries to return the postcode. The New Zealand Post have
supplied a data file that contains 1,829,432 distinct delivery points. Do you
have any suggestions/recommendations to help speed up the performance of a
search; particularly over a network. Both the data file and front end are in
Access 2003.

To add to the mix, some addresses have alternative street names and/or
alternative suburb names and/or alternative town names. These alternatives
are contained in separate tables. That is if a matching address is not found
in the main table, a search is then made for each of the possible alternative
combinations.

The typical search is on the fields: streetName, streetAlpha, streetType,
suburb, townCity.

Many thanks, Jonathan

 

[John Spencer]

The only suggestion I have is to make sure the fields you search against are
indexed. That will make a huge difference in the speed of searches.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

What John said.

Also is this data file in an Access database format or some other format
which you are linking too? If linking and it's not Access, importing into the
database might be faster.

How are you searching the other tables if the data isn't found in the main
table? Union queries are slow. Searching one table then moving to another is
slower. It could be much faster for some searches if everything is in one
table, especially if as John said, it's properly indexed.

Also if the need for speed is important, Access might not be the best
solution in this case. Upsizing to something like SQL Server on a proper
database server would be much faster. It would also be much, much more
expensive.

 

[Jonathan]

Thanks for your observations and suggestions. I've put indexes on these fields.

I have converted the data from a text file format to store in Access. I
agree, SQL would be better, especially in a network environment.

The data in stored in separate tables. I will combine as recommended. At
this stage I do separate searches.... if not found in the main table I then
search the others. I have avoided union queries as I did find they are slow.