Are SQL PATINDEX() And The SQL LIKE Clause Doing The Same Thing?
Posted September 24, 2007 at 8:26 AM by Ben Nadel
I know that running a loop and testing execution times is NOT a valid way of doing speed tests (but until I get not-lazy enough to learn how to properly load test, this is all I got). I think even doing this is valid in its own way, and if nothing else, at least opens the door for conversation. That being said, I recently found out about the SQL PATINDEX() function from Nathan Mische and Sana. Nathan was talking about finding a string of characters in a database column value and was using SQL's PATINDEX() to find this. I had dropped a comment about using the SQL LIKE construct to do the same thing (although my comment was in response to a total misunderstanding of how the PATINDEX() function worked).
Sana, then commented that PATINDEX() was much more powerful than LIKE. To me, though, they seem to be doing the same thing. And, with my primitive caveman speed test, they seem to operate at the same efficiency. In the following code, both the SQL PATINDEX() and the SQL LIKE clause operate consistently at the same speed:
- <!---
- Query for all the blog entries that have the phrase
- "SEXY" in it. Do so using the SQL LIKE clause.
- --->
- <cftimer
- type="outline"
- label="SELECT Using LIKE Clause">
-
- <!--- Loop over this method 10 times. --->
- <cfloop index="i" from="1" to="10" step="1">
-
- <!--- Query for blog information. --->
- <cfquery name="qBlog" datasource="#REQUEST.DSN.Source#" result="objResult">
- SELECT
- b.id,
- b.name
- FROM
- blog_entry b
- WHERE
- b.content LIKE '%[Ss][Ee][Xx][Yy]%'
- </cfquery>
-
- <!--- Output execution time of query. --->
- #objResult.ExecutionTime# ms /
- #qBlog.RecordCount# records
- <br />
-
- </cfloop>
-
- </cftimer>
-
-
- <!---
- Query for all the blog entries that have the phrase
- "SEXY" in it. Do so using the SQL PATINDEX() method.
- --->
- <cftimer
- type="outline"
- label="SELECT Using PATINDEX() Function">
-
- <!--- Loop over this method 10 times. --->
- <cfloop index="i" from="1" to="10" step="1">
-
- <!--- Query for blog information. --->
- <cfquery name="qBlog" datasource="#REQUEST.DSN.Source#" result="objResult">
- SELECT
- b.id,
- b.name
- FROM
- blog_entry b
- WHERE
- PATINDEX( '%[Ss][Ee][Xx][Yy]%', b.content ) > 0
- </cfquery>
-
- <!--- Output execution time of query. --->
- #objResult.ExecutionTime# ms /
- #qBlog.RecordCount# records
- <br />
-
- </cfloop>
-
- </cftimer>
In my example, I am using the pattern:
%[Ss][Ee][Xx][Yy]%
I was purposefully trying to make the pattern more complex in hopes that a bigger difference would be seen between the two algorithm choices. However, since my Database does not perform LIKE with case sensitivity, using this pattern:
%sexy%
... would have sufficed for both tests (but executed in about half the time as the previously defined pattern).
Now, I know that obviously they are doing different things: LIKE is a comparison operator and PATINDEX() returns the character index of the matched patterns - two different tasks. But, in terms of performance, if you do not care about the way in which it functions, they seem to operate at the same speed.
If all I care about is the comparison operator aspects used to filter a WHERE clause, is there any reason that I should be using PATINDEX() over the SQL LIKE operator?
Reader Comments
The biggest reason for using or not using PATINDEX probably would be whether or not you need the location of the value...maybe you might want to parse the value for some reason, like doing a replace on Sexy with another piece of text, or extract the value to use elsewhere.
PATINDEX is kind of like a combination of CHARINDEX and LIKE.
@Tom,
That sounds about right to me. I was hoping maybe there was an actual speed increase just from switching to PATINDEX() over LIKE, but I perhaps that is just not the case.
Hi Ben,
I have been using PATINDEX on large texts columns; where I found that LIKE is operator response is slow.
Sorry about if my theory is wrong. Anway I am forwarding 2 links from MSND.
http://msdn2.microsoft.com/en-us/library/ms179859.aspx
http://msdn2.microsoft.com/en-us/library/ms188395.aspx
These two info page cleary describe about LIKE and PATINDEX.
In my blatently unscientific tests PATINDEX was slower than LIKE. I searched 176546 Descriptions for matches to eight strings:
SELECT Count(*) FROM GSA_Work WHERE PATINDEX('%Storage%', Description) > 0 OR PATINDEX('%Storag%', Description) > 0
OR PATINDEX('%Storwks%', Description) > 0 OR PATINDEX('%Stor wks%', Description) > 0
OR PATINDEX('%Storwrks%', Description) > 0 OR PATINDEX('%Stor wrks%', Description) > 0
OR PATINDEX('%Stor rmkt%', Description) > 0 OR PATINDEX('%sureStor%', Description) > 0
5357 results that averaged around 2.2 seconds
SELECT Count(*) FROM GSA_Work WHERE [Description] LIKE '%Storage%' OR [Description] LIKE '%Storag%'
OR [Description] LIKE '%Storwks%' OR [Description] LIKE '%Stor wks%'
OR [Description] LIKE '%Storwrks%' OR [Description] LIKE '%Stor wrks%'
OR [Description] LIKE '%Stor rmkt%' OR [Description] LIKE '%sureStor%'
5357 results that averaged around 1.1 seconds
@Kees,
I appreciate you posting these findings. I haven't been on SQL Server in a while (work mostly on MySQL these days), so I pretty much use LIKE for all my non-exact matching. Actually, I wonder if MySQL has something like this... I am shockingly poor at the intricacies of MySQL.
