Results 1 to 2 of 2

Thread: Access Query is taking forever, looks simple enough

  1. #1

    Thread Starter
    Frenzied Member wengang's Avatar
    Join Date
    Mar 2000
    Location
    Beijing, China
    Posts
    1,602

    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

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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
  •  



Click Here to Expand Forum to Full Width