Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access Query very slow

Posted on 2016-09-13
9
Medium Priority
?
233 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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 40

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

916 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