Wednesday, December 22, 2010

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.

1 comment:

  1. Boolean search commands have been used by professionals for searching through traditional databases for years. Despite this, they are overkill for the average web user. The commands described on the Search Engine Math page provide the same basic functionality as Boolean commands and are also supported by all the major search services. If you are new to searching, start off learning how to search better by first reading the Search Engine Math page, rather than trying to learn Boolean commands. I'm certain you'll find it easier. At SourcingLab you can easily create Boolean searches across multiple platforms and store them for future searches.

    ReplyDelete

All suggestions are welcome