Home Forums Article Discussions Article Discussions by Author Discuss content posted by Ben Seaman Optimising “Ends With” searches with REVERSE RE: Optimising “Ends With” searches with REVERSE

  • January 13, 2010 at 9:10 am

    #1103132

    peter-757102 (1/13/2010)

    Note that indexes can only have a limited width (300 bytes or so , I believe).

    Because of this, it makes sense to do a reverse of only the last N characters and limit the length of the search text to match. If your strings have varying lengths between 3 and 250 characters and your search strings are strict enough with 12 characters, add a few extra and use the reverse of the rightmost 16 characters to put the index on and truncate the search string to 16 characters as well.

    This saves you a ton of otherwise unneeded duplicate data and keeps the number of page reads to a minimum.

    It's actually 900 bytes for an index but very good point especially if you need to do it often.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)