Solved

MS Access Query very slow

Posted on 2016-09-13
9
58 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 35

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

809 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