Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Query very slow

Posted on 2016-09-13
9
Medium Priority
?
185 Views
Last Modified: 2016-09-13
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
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 41796237
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
 

Author Comment

by:Taras
ID: 41796251
I tried with exact value e.g. = ”Jonson” and with like  (Like “Jonson” )the result is the same – slow.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41796281
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 28

Expert Comment

by:Bill Bach
ID: 41796283
Do you have an Index created on the LastName field?
0
 

Author Comment

by:Taras
ID: 41796345
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
 
LVL 28

Expert Comment

by:Bill Bach
ID: 41796359
Indeed.  Again, with the LIKE, you won't be able to leverage the index.
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 800 total points
ID: 41796381
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 1200 total points
ID: 41796389
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
 

Author Closing Comment

by:Taras
ID: 41796571
Thanks a lot, I removed front "*"  and now it is fast and working much better.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question