Optimising “Ends With” searches with REVERSE

  • Bravo! Excellent example of explaining a technique that, once understood, seems so very natural. And, once again the discussion adds tremendously by making it clear that this is a "pay me now or pay me later" situation (absorb the overhead of index maintenance in favor of faster queries) and by suggesting improvements like Peter's (make the computed column only the last N characters of the search column).

    Articles and discussions like this have made SSC my favorite web site (edging out ESPN.com).

    =============

    edit: fix minor typo

  • Jeff, Paul and others have made some excellent comments. I think this was a nice article. It certainly opens up avenues of thinking for any who might need to refine similar situations.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Peter Brase (1/13/2010)


    Thanks for this, an excellent tip! I discovered that even if you skip the extra column and just run the reverse query, i.e., "where reverse(fieldname) like 'cba%'" instead of "where fieldname like '%abc'" the response time improves by 50%.

    Hi Peter,

    Depending on how you measured that response time I'd be a little bit wary of that test. It might be simply that the first time you ran the query, the pages returned were kept in the SQL cache, and then when the second query was run (which would return exactly the same rows/pages) the data came straight from the cache and was therefore quicker.

    Regards,

    Ben

  • Thanks Ben, great idea for when you absolutely have to have those fast lookups with wildcards at the beginning of the search term. Which happens more often then one might think...

    One small point; for the sake of code clarity, I would do something like this:

    SELECT NAME FROM TEST_TABLE WHERE NAME_REVERSED LIKE REVERSE('%ID')

    Signature is NULL

  • Calvin Lawson (1/15/2010)


    Thanks Ben, great idea for when you absolutely have to have those fast lookups with wildcards at the beginning of the search term. Which happens more often then one might think...

    One small point; for the sake of code clarity, I would do something like this:

    SELECT NAME FROM TEST_TABLE WHERE NAME_REVERSED LIKE REVERSE('%ID')

    Hi Calvin,

    True - I suppose it depends on whether you can get the calling code to reverse the search term for you. If you have a high volume web site doing lots of searches for example, you might want to get the web code to do this (albeit minor) string reversion as SQL is probably going to be slower at doing this than a web farm.

    On the other hand it makes it better for code isolation purposes if you can just give the search term you are looking for to the SQL code

    So another 'it depends' answer then!

    Regards,

    Ben

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply