THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Is PATINDEX faster than LIKE?

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?


Published Wednesday, July 12, 2006 10:13 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mark Jackson said:

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.

October 24, 2007 9:24 AM
 

Anon Anon said:

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

October 14, 2008 2:03 PM
 

Alex Cons said:

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.

March 9, 2011 6:13 PM
 

Ankit said:

Would some one please answer the poor guys question ?? I wanna know !!

October 7, 2011 6:47 AM
 

Adam Machanic said:

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.

October 7, 2011 10:06 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement