Sql Server Performance: Avoid LIKE Operator for Exact Searches


Like is a common string comparison operator and should only be used when you have a fragment of the target character string and cannot use the exact search method.
USE AdventureWorks
 SELECT     *
FROM    Sales.SalesOrderDetail
WHERE   CarrierTrackingNumber LIKE ‘4911% — To return records where CarrierTrackingNumber start with ‘4911’
For strings comparison where you are sure about values just use ‘=’ operator.
USE AdventureWorks
SELECT     *
FROM    Person.Contact
WHERE   FirstName = ‘4911-403C-98’ — To return records where CarrierTrackingNumber is exactly ‘4911-403C-98’
Instead of ‘=’, ‘Like’ operator can be used for exact search but performance of ‘=’ operator for such cases will be much better as compare to ‘Like’ operator. Let’s see
Performance Difference:
USE AdventureWorks
go
SELECT     *
FROM    Sales.SalesOrderDetail
WHERE   CarrierTrackingNumber = ‘4911-403C-98’
go
SELECT     *
FROM    Sales.SalesOrderDetail
WHERE   CarrierTrackingNumber LIKE ‘4911-403C-98’
 For my hardware, performance ratio for ‘=’ operator and ‘Like’ operator for exact search is 37%:63%. That’s why, for exact search we must use ‘=’ operator for strings.
Advertisements

Posted on December 22, 2010, in Performance Tuning, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: