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:
SELECT *
FROM tbl
WHERE PATINDEX('%abc%', col) > 0
is faster than this:
SELECT *
FROM tbl
WHERE col LIKE '%abc%'
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):
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)
I decided to test against the pattern '%ossDbOwnChainRefere%', for which there are 1752 rows in the test table.
First, I ran the LIKE query:
SELECT COUNT(*)
FROM #BigTableOfStrings
WHERE SomewhatLargeString LIKE '%ossDbOwnChainRefere%'
Runtime: 9:55.
Then I tried PATINDEX:
SELECT COUNT(*)
FROM #BigTableOfStrings
WHERE PATINDEX('%ossDbOwnChainRefere%', SomewhatLargeString) > 0
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?