Solved

MS Access Query very slow

Posted on 2016-09-13
9
47 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
  • 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
 
LVL 28

Expert Comment

by:Bill Bach
ID: 41796283
Do you have an Index created on the LastName field?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 34

Assisted Solution

by:PatHartman
PatHartman earned 200 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 300 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now