I keep seeing the same suggestion on various “tips and tricks” websites: For situations in which you might want to use LIKE in the WHERE clause, but for which indexes cannot be used, PATINDEX will perform faster.
So, according to these sources, this:
[sql]SELECT *
FROM tbl
WHERE PATINDEX(‘%abc%’, col) > 0
[/sql]
is faster than this:
[sql]SELECT *
FROM tbl
WHERE col LIKE ‘%abc%’
[/sql]
The thing is, I’m not one to just take this kind of stuff at face value, so I’ve tested this assertion several times. Every time I see this tip, I think, “I must be missing something,” and I test again. And every single time I test again, with different data, different patterns, etc, I arrive at the same conclusion: They perform exactly the same.
Which brings us to today. In the SQL Server Central forums, William O’Malley told me that in his tests, on his data that he can’t post due to his industry (?), PATINDEX does outperform LIKE.
So I decided to test yet again. And I’m still coming up with the exact same numbers. I’m hoping that some reader will be able to tell me this mysterious circumstance in which PATINDEX really does outperform LIKE. Or maybe explain why my test is totally incorrect.
Anyway, here’s what I did today… First, I created a big table of test data (83 million rows) with the following (which you may notice that I lifted from a previous post):
[sql]SELECT DISTINCT A.Name + B.Name + C.Name AS SomewhatLargeString
INTO #BigTableOfStrings
FROM master..spt_values A,
master..spt_values B,
master..spt_values C
WHERE a.TYPE NOT IN (‘P’, ‘R’, ‘F’, ‘F_U’)
AND b.TYPE NOT IN (‘P’, ‘R’, ‘F’, ‘F_U’)
CREATE CLUSTERED INDEX CI_LargeString ON #BigTableOfStrings(SomewhatLargeString)
[/sql]
I decided to test against the pattern ‘%ossDbOwnChainRefere%’, for which there are 1752 rows in the test table.
First, I ran the LIKE query:
[sql]SELECT COUNT(*)
FROM #BigTableOfStrings
WHERE SomewhatLargeString LIKE ‘%ossDbOwnChainRefere%’
[/sql]
Runtime: 9:55.
Then I tried PATINDEX:
[sql]SELECT COUNT(*)
FROM #BigTableOfStrings
WHERE PATINDEX(‘%ossDbOwnChainRefere%’, SomewhatLargeString) > 0
[/sql]
Runtime: 9:56 (yes, worse)
Then I ran LIKE again with a runtime of 9:47, then PATINDEX again with a runtime of 9:50, and now I’m not patient enough to run either of them again.
So am I correct? Is this claim bogus? Or have I gone completely off-base?
The important bit you missed is the phrase ‘ but for which indexes cannot be used’. Your test has a clustered index on the searched field and that will be used every time. If you run the same test with a clustered primary key index (on an id field) and your search field set up as a text datatype there is a hugh difference. I have had up to 50% speed gains on a 1.3 million record table of life customer data using patindex, although I would rather shoot myself in the head then do either of these as using a fulltext index on the same data returns the same results in less than a second, compared to 20+ seconds with patindex, or 35+ seconds with like.
The main reason anyone will swirl away from using SQL FT to using a LIKE or a PATINDEX is when they have to do a *term .
SQL FT supports only term* and not *term or *term* .
For example: If I were to search for 123*, I will get 1234, 123456 etc. I can also get 00123 if I were to create a column with the reverse text & create a FT index on it.
But, How can i find the term with has "0012345" ? Looks like this is not supported with 2008 either ! huh ! After all these years Microsoft does not seem to figure out this trivialities!
-AA
Why can’t they just create a "Reverse" Index? the same way they create an index and check the letters left to right just create another right to left and boom!, you have *123*.
They must have it for a couple of languages like Arabic or Japanese I would guess.
Would some one please answer the poor guys question ?? I wanna know !!
About the reverse index? Well they "could" create it (they being the SQL Server team). There are lots of things they could do, but at the end of the day they need to decide what to put in the product based on available time, resources, and the realities of the fact that they’re trying to sell software. Would a reverse index improve sales? Probably not much. Therefore, it’s not going to be prioritized, no matter how useful it would be.
But you can create it yourself. Add a computed column using the expression REVERSE(yourTextCol) and index it. And there you have it, a reverse index. Is it the prettiest thing in the world to have floating around in your database? Nope. But it gets the job done.
For those who are interested in the performance benchmarks between CHARINDEX and other functionality such as LIKE, LEFT/RIGHT, etc, here’s some:
http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex
Interesting in that CHARINDEX wiped out the competition in most cases.