Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: SQL pattern matching performance

    Hello,
    I'm running queries against a 3rd party db through their odbc driver from access 2007. I'm noticing quite a difference in performance when using different pattern/wildcard matching techniques. Below are the details.

    Using LIKE "W[DB]*" finishes in about 2 minutes.

    Using LIKE "WD*" OR LIKE "WB*" finishes in about 5 seconds.

    I like the first one because of simplicity and maintenance, but can't put up with the performance hit. Does anyone have any input on this? Is it Access or would it be the 3rd party ODBC driver? Is it possible to somehow improve the first scenario?

    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Would it make any difference if you used "W[BD]*"

    Seems a stupid suggestion but [DB] is looking for the value in the range D to B otherwise... B to D just seems a little easier to comprehend.

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sorry, Ignore me. I spoke before thinking. [DB] is looking for D, or B etc.

    I was thinking of using a range wildcard [B-D]

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    It doesn't appear to make a difference. Thanks for the idea though.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

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