Performance Question - name search (1 Viewer)

sumdumgai · Dec 5, 2018

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:

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,

isladogs · Dec 5, 2018

I'm surprised it takes as little time as that even with indexing.

Try removing the leading wildcards if possible
Consider splitting into two separate searches
Or possibly use a number of cascading combo boxes.
Have a look at this example and see if you can adapt the idea: https://www.access-programmers.co.uk/forums/showthread.php?t=302126

EDIT: Name is a reserved word in Access and shouldn't be used for field names

jdraw · Dec 5, 2018

Independent of the initial search strategy, I would suggest devising a "standard method" for storing such names if you plan on using this database in future. Having an ad hoc name formatting and storage is not a long term solution to an operational database.
But you haven't told us much about what will happen after the importation of the data.

Like Colin I am surprised with your response times with the wild cards fore and aft.

sumdumgai · Dec 5, 2018

Thanks to both of you.
To jdraw, we have no control over standardizing names. The data comes via subscription from web site and it is what it is. The looked up record is used to get current address of the business. The looked up business address will be manually copied by some method to local database. The data will be imported quarterly.

To isladogs, the wildcards are needed because I have to allow a partial name or address search. As I said, the incoming names are pretty 'dirty' and can have any format, including having titles, special characters, leading, trailing numbers, etc. I'll look at your suggested example.

sumdumgai · Dec 5, 2018

Good news. I reprogrammed building the sql string, so that instead of including all of the searchable form fields in the sql string, I included only the form's search fields that had an argument. So, even with using the 'Like' command on the name field, I am getting response times under 10 secs.

jdraw · Dec 5, 2018

???2 million records with leading wildcard???
How do you resolve duplicates/replicates?
What processor etc are you using?

sumdumgai · Dec 5, 2018

The back end is .7GB and has 1.8 M records, 3 of the 5 fields ( name, address, city) can be partially filled with search argument. When I put any value in those 3 fields, response is 14 secs. I'm running W10, Office 365 with an Intel I7 4Ghz proc and 16GB mem, using WD NAS.

sumdumgai · Dec 5, 2018

The query results are placed in a list box bottom of form. User can scroll the results list, find the record that he wants and double click it. The full record is then displayed at bottom of form, including those record fields that are not searchable.

jdraw · Dec 5, 2018

I' sure it's primarily the 4 Ghz processor that's accounting for the speed.
No indexing when using leading wildcard.

For clarity, you are searching names and addresses, but what do you do with the "hits/results"? If you are trying to identify unique companies, there must be more to your process.

sumdumgai · Dec 5, 2018

All of the search fields are indexed (dups allowed) which of course is useless for the wildcard fields. I should probably remove those indices. The state and zip fields are not wildcarded and expect user to input exact value.

As I said earlier, the results are displayed in list box middle of form. The user can scroll through the list, find the record he/she wants, double click it to return full record in boxes at bottom of form. From there, they manually copy and paste to another database. That is subject of another Access VBA programming task.

isladogs · Dec 5, 2018

Intel I7 4Ghz proc and 16GB

But will each of your users have that spec & what about network performance issues?

All of that is infinitely better than I have - Intel i5-2310 CPU @ 2.90GHz & 4GB RAM. Woefully underpowered for my needs but I'm reluctant to upgrade as my end users usually have similar spec (or worse)

However using indexing and cascading combos I can search 2.6 million postcodes in a small fraction of each second for each combo.

sumdumgai · Dec 5, 2018

The state and zip fields on the database are indexed duplicates okay. Also, their query statement says 'equal' and not 'like', meaning user must enter exact values. If I include the state or zip with the partial name search, I get 2-3 second response. That makes sense because Access probably first finds the state and/or zip records, and then finds the partial name records.

sumdumgai · Dec 5, 2018

Never heard of cascading combos. Is that covered in the example you referenced?

User spec problems and networking issues are yet to be discovered, if any, as this is a new application for them.

isladogs · Dec 5, 2018

That was the title of the thread!
You can also find it on my website: http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

jdraw · Dec 5, 2018

I don't mean to wander off topic here, but your intro said

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.

..
which makes me wonder how a user can identify which record they need when the spellings and formats may differ and be duplicated/replicated.

sumdumgai · Dec 5, 2018

They know the approximate name and probably general location of the business they are looking for. For instance, a Mr. John Doe may own a financial advisor business in California. The name of the company may actually be Doe Financial, Inc., or John Doe, LLC, or Doe Enterprises, Inc. So, they'll search on 'John Doe' and 'CA' and get a list of records meeting those criteria. That should narrow it down to few records from which they can select the one they want.

Hope that answers your question.

jdraw · Dec 5, 2018

Thanks. Good luck with your project.

AccessBlaster · Dec 5, 2018

In your search query, make sure you only include the minimum number fields required for your search. If you have 25 fields in your table, you might only need 10 fields in your QBE. Of those fields, you may only need 5 to be indexed. Be stingy on indexing.

This should improve speed by cutting down on network traffic.

Performance Question - name search (1 Viewer)

sumdumgai

Registered User.

isladogs

MVP / VIP

jdraw

Super Moderator

sumdumgai

Registered User.

sumdumgai

Registered User.

jdraw

Super Moderator

sumdumgai

Registered User.

sumdumgai

Registered User.

jdraw

Super Moderator

sumdumgai

Registered User.

isladogs

MVP / VIP

sumdumgai

Registered User.

sumdumgai

Registered User.

isladogs

MVP / VIP

jdraw

Super Moderator

sumdumgai

Registered User.

jdraw

Super Moderator

AccessBlaster

Registered User.

Similar threads

Users who are viewing this thread