Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Unanswered: Like *slower than =

    If i do a qry using Like *find*
    is that slower than if i did using
    ="find This"?

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    don't quote me on this, but I believe like has some other facilities available that make it just a shade slower. Since "like" has contextual comparisons available, I would guess using the literal "=" would seem to be faster. Again, I'm no expert on the guts of "like"...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I don't think JET has the capability of full text search as available on some server data stores.

    Access is doing a full table scan if you use the like "*blah" predicate, and probably doing it for a ="blah" predicate.

    if you are using like "*blah" then it will do a complete scan through the text column looking for any match
    looking for "*blah*" will take even longer. So if your column is 100 characters long and you searched for "*blah*" you are potentially doing anywhere between 25 and 96 comparisons to find if the string exists,

    using ="blah" means a single comparision on the column

    So specifying like "*blah*" is going to force the JET engine to do a lot more work and if its pulling it accrosss a network the implications on a big tabel are horriffic.

  4. #4
    Join Date
    Dec 2004
    Madison, WI

    Using Like

    I'm guessing that doing a Like "*" & VariableName & "*" might also be slower than doing a Like VariableName & "*". Also, depending on how you open the recordset (if you're using VBA) can play a role in speed (i.e. opening it using adOpenDynamic verses adOpenForwardOnly.)

    The slowest way I've found though is opening an entire recordset in VBA and then doing a docmd.Find VariableName or using the seek command.

    If you're using queries though, I haven't noticed (or payed much attention to) the difference in the criteria row between using the Like and using =. Others might have more insight on this.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts