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?
What it could be the reason?
ASKER
I tried with exact value e.g. = ”Jonson” and with like (Like “Jonson” )the result is the same – slow.
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
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
Do you have an Index created on the LastName field?
ASKER
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")
??
??
Indeed. Again, with the LIKE, you won't be able to leverage the index.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot, I removed front "*" and now it is fast and working much better.
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.