I'm importing about 2 million .csv records into Access. The records contains 5 searchable fields (name, address, city, state, zip). These are business name and address records where the names and addresses are formatted differently; i.e., I cannot expect the user to precisely know the name that is being looked up to get the address. Hence, I use a where clause such as:
In this case, I'm allowing the used to input a search of two parts of the name separated by a space and have parsed these parts into fName1 and fName2.
The sTable.sName field is indexed as well as the other four fields, because the search arguments can contain any or all of them. The database has been split and the back end resides on shared server. Unsplitting the database seems to make little difference.
The query response time is anywhere from 30 secs to 2 min depending on the computer being used. Is there anything I can do to get better response times? Any suggestions are appreciated,
Where [sTable].[sName] Like '*" & fName1 & "*' and [sTable].[sName] Like '*" & fName2 & "*'
In this case, I'm allowing the used to input a search of two parts of the name separated by a space and have parsed these parts into fName1 and fName2.
The sTable.sName field is indexed as well as the other four fields, because the search arguments can contain any or all of them. The database has been split and the back end resides on shared server. Unsplitting the database seems to make little difference.
The query response time is anywhere from 30 secs to 2 min depending on the computer being used. Is there anything I can do to get better response times? Any suggestions are appreciated,