Like filter takes hours Whereas not like works instantly!!!

[mike6271]

Hi All,

Opening a form with filter
([ContactID] IN (SELECT ContactID FROM tblBuisnessAddress WHERE tblBuisnessAddress.postcode LIKE 'w1*'))

takes a crazy amount of time. (60 sec for 450 records and a few minutes for 1000's).

BUT...

([ContactID] IN (SELECT ContactID FROM tblBuisnessAddress WHERE tblBuisnessAddress.postcode NOT LIKE 'w1*'))

is instant! (Whether 1 or 1000's of records).

All that is different is the word NOT!

Am I missing something simple here? Just cant think why :banghead:

Thanking you for listening

Any advice will be much appreciated.

 

[Galaxiom]

IN is slow at the best of times and in your structure it will take time proportional to the number of records in the subquery.

I expect Like w1* returns a lot more records than Not Like w1*

 

[michaellondon]

Thank you for taking the time to reply.

That doesn't seem to be it because I've tried variations which return both few and 1000's e.g. LIKE "N*" and NOT LIKE "N*" both return 1000's of records but the LIKE filter takes infinitely longer!

I've worked around the problem but I just cant understand it all...

Cheers

 

[Pat Hartman]

You didn't include the whole query. It is possible that you don't need the "IN" at all and should be using a join which is far more efficient.

Also, depending on what your BE database is, the database engine might need to do a full table scan to use the Like predicate rather than use an index.

 

[mike6271]

Hi,

That was the entire query. I deleted original qry segment by segment to trap the part that was causing the problem.

You are right I didnt need to use it - just wouldve saved time rewriting a specific query for this form.

Using Access BE.

Thanks for comments - was driving me mad more than being practical problem! Cos what if I did need to use it...