-
Sep 14th, 2012, 11:03 AM
#1
Thread Starter
Frenzied Member
Access Query is taking forever, looks simple enough
I'm experiencing a major slowdown running this query in a Word macro.
It's basically looking for any term in table1 field1 that 1) has a space in it anywhere, and 2) is not listed in Table2 Field2 (renamed for clarity, of course)
select Field1 fromTable1 where Field1 like '% %' and Field1 not in (select Field2 from Table2)
I've separated the parts and the problem seems to be in the second half.
I put a timer on to see how long it was taking, but ended up interrupting the app. It seems like it will never finish.
For reference, there are 30,000 records in Table1 and 20,000 records in Table2
Suggestions?
Thanks.
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Sep 17th, 2012, 05:25 AM
#2
Re: Access Query is taking forever, looks simple enough
The contruct '% %' makes any index you have on Table1 field1 unusable. It is not SARGABLE (look that up on Google or another search index). An index on Table2 Filed2 might help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|