CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
jrara
jrara

Reputation: 16981

SQL Server: is there a performance overhead between '=' and 'like' operators?

Is there a difference in performance between these two queries?

--= operator
SELECT COL1, COL2 
FROM DBO.MYTABLE 
WHERE COL1 = '1'

--like operator
SELECT COL1, COL2 
FROM DBO.MYTABLE 
WHERE COL1 LIKE '1'

Basically using LIKE in this case is wrong but database engine accepts this.

Upvotes: 2

Views: 798

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

It's easy enough to test. I used a table with 6000000 rows doing a comparison with = and like against a nvarchar field that is not indexed.

Using like 'xx'

SQL Server Execution Times:
   CPU time = 6416 ms,  elapsed time = 493 ms.

Using = 'xx'

 SQL Server Execution Times:
   CPU time = 3444 ms,  elapsed time = 212 ms.

If you use like with wild card I at least assumed it would be slower still but it was not

Using like 'xx%'

 SQL Server Execution Times:
   CPU time = 3168 ms,  elapsed time = 296 ms.

Using a wild card in the front is a different matter.

Using like '%xx'

 SQL Server Execution Times:
   CPU time = 18017 ms,  elapsed time = 1530 ms.

All these tests are done without index on the column so what I actually compare here is the internal operation that do equal compare with a like compare. In the execution plan they are <Intrinsic FunctionName="like"> for like and <Compare CompareOp="EQ"> for =.

The query plans "look" the same for using like without % and and = but they are not. like still uses <Intrinsic FunctionName="like"> even without %.

Upvotes: 3

Darin Dimitrov
Darin Dimitrov

Reputation: 1038710

Checkout the following post.

Quote (in case it goes off-line):

My knee-jerk response was that the = would be faster, but I thought about it and realized that the query optimizer would actually see them as the same thing. A check of the query plans against a quickly-created tbFoo confirmed it. So that's what I told him.

Except that I realized a moment later that there was a major caveat - the query optimization depends on how the statement is parameterized. If it's purely ad hoc SQL and being compiled at run-time, then the statements are equivalent, but if there's going to be any plan re-use, either by including the statement in a stored proc or preparing it and executing via sp_executesql, the LIKE will impose a significant penalty.

This is because the optimizer doesn't know at compile time whether the parameter to the LIKE operator will contain a wild card, so it can't use the more specific optimization (including index selection) that it could with an = operator. So if you mostly pass parameters without wildcards, you will be executing a suboptimal query plan. Keep this in mind when designing your query!

Upvotes: 6

Related Questions