Using 'Not Like' for exact string matches

  • Thread starter Thread starter GVR_Mike
  • Start date Start date
G

GVR_Mike

Is there a performance difference between these two query criteria
statements?

Not Like "aa" and Not Like "bb"
vs.
Not "aa" and Not "bb"

I've seen where you should only use Like when a wildcard is being
used. I'm looking to speed up these queries any way I can.

Thanks in advance.
 
It may depend on if that field is indexed. Access may decide to not use an
index if there is a wild card or LIKE statement. I'm guessing though. I'd
have to run Showplan and get out my stopwatch to test it on a large table to
be sure.

Personally I'd use the following just because it's cleaner, shorter, and
easier to add something like "cc" if needed:
NOT IN ("aa", "bb")
 
I believe that you will see no difference in performance for the two
queries.

Have you indexed the field that you are applying the criteria to? Indexing
will usually make query performance quicker.

"Usually .. quicker" meaning that if you are adding records then the add
will take a bit longer since the index also has to be updated and if you are
deleting the index has to be updated. However, the difference may be
overshadowed (especially when deleting) if the number of records selected is
a small portion of all the records.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you Jerry and John for your speedy replies. I'm using Access
2000 to query an Oracle back-end (I only have read access) so I'm not
sure if the fields are indexed or not. Normally any tables I query
from Access are from make-table queries so I guess they don't get
indexed? I just learned the IN keyword not too long ago and I keep
forgetting about it. Too many queries existing to retrofit but I'll
start using that going forward.

Thanks again.
 
The make table queries might, just might, have indexes created. Access
automatically creates an index on fields with ID, key, code, or num anywhere
in the field name. For example if your new table has a ZipCode field, it's
index.
 
Back
Top