Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

MS Access Query very slow

In Access 2010 I have a simple query (not append not update not delete) that is composed from three tables. Number of records is 30 -40 thousands, not big tables. Query runs fast (instant speed) when I use number field as criteria but when I use a text field (e.g. Last Name or similar as criteria it runs very slow it takes 1 minute or more to give result. All references are ok nothing missing.

What it could be the reason?
0
Taras
Asked:
Taras
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Bill BachPresidentCommented:
When you use a number, the system may be able to leverage an index and find proper matches.  However, when you say "or similar" in the criteria, this generates a LIKE clause in SQL -- and the system can no longer optimize on things.  

Remember that in a join, you have to MULTIPLY the record counts to get the result set size.  So, if you have a table of 30,000 rows joined to two tables of 1000 rows each, the result set may be 30,000,000,000 unique combinations.  It is the query optimizer that then filters this down and makes it more usable based on the other indices available.  Without an exact match for the text field, you'll have to read EVERY record of the table and check the field for a match, and this will definitely take longer.
0
 
TarasAuthor Commented:
I tried with exact value e.g. = ”Jonson” and with like  (Like “Jonson” )the result is the same – slow.
0
 
Nick67Commented:
Query runs fast (instant speed) when I use number field as criteria but when I use a text field (e.g. Last Name or similar as criteria it runs very slow it takes 1 minute or more to give result.

Let me re-state that and see if you find it applicable:
Query runs fast (instant speed) when I use autonumbered ID field (which is indexed) as criteria but when I use a text field (e.g. Last Name or similar as criteria, but aren't indexed) it runs very slow it takes 1 minute or more to give result

Fields you use in criteria should be indexed.
Autonumber primary keys, and fields used in relationships have indexes created by Access automatically.
Index the fields you are JOINing on, if they aren't already and test the result.
(and that means in both the host table and the foreign table)
Tell us about how the JOIN fields are related and indexed.

I tried with exact value e.g. = ”Jonson” and with like  (Like “Jonson” )the result is the same – slow.
Note that since you have no wildcard in your LIKE comparison, it will return the same result -- but likely be slower
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bill BachPresidentCommented:
Do you have an Index created on the LastName field?
0
 
TarasAuthor Commented:
I did not have before, I put index on that field now and I have good speed on ( ="Jonson") it is almost instant , but still the same slow time on( Like "*" & "Jonson")
??
0
 
Bill BachPresidentCommented:
Indeed.  Again, with the LIKE, you won't be able to leverage the index.
0
 
PatHartmanCommented:
As Bill already mentioned LIKE will always be slower due to the fact that indexes cannot normally be used.  If you always know the beginning of the string so you can use Like "somevalue*"  - with the wildcard at the end, Access might be smart enough to use an index if you have created one.  It absolutely cannot use an index with a leading wildcard.
0
 
Nick67Commented:
Leading wildcards in a LIKE comparison are especially poor because the query engine can't optimize that AT ALL.
LIKE 'something*' can be a little better because the engine can truncate the field to the number of characters before the wildcard, order them and then do the scan.  So instead of processing ALL the text for ALL the rows, it can do something simpler.

But a leading * makes it do all the grunt work.

Depending on your data, you can create computed fields in your table that will give you better data to JOIN on.  I have a memo field (which you CANNOT JOIN on) and in the table I have a computed field which is Left (ThatMemoField,255) which I can JOIN on.

Why do you need a leading * ?
THAT you need to avoid!
0
 
TarasAuthor Commented:
Thanks a lot, I removed front "*"  and now it is fast and working much better.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now