Optimising “Ends With” searches with REVERSE

Question

Optimising “Ends With” searches with REVERSE

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

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

john.arnott SSChampion Points: 11882 More actions · Answer 1

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

johnzabroski SSCrazy Points: 2355 More actions · Answer 2

You can circumvent the max index key size

http://blogs.msdn.com/anthonybloesch/archive/2009/11/23/implementing-uniqueness-constraints-on-large-columns.aspx

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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

ben-564110 SSC Eights! Points: 942 More actions · Answer 4

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

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

ben-564110 SSC Eights! Points: 942 More actions · Answer 6

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