Performance Tip 1: Avoid non-sargable WHERE-clauses.
(06 April 2008)
Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones.
In case a WHERE-clause is sargable SQL Server can take advantage of an index to speed up the execution of the query. In case it is non-sargable SQL Server can' t.
Sargable operators:
= (best sargable operator)
>
<
>=
<=
EXIST
IS
IN
BETWEEN
LIKE 'abc%' (least best sargable operator)
Non-sargable operators
IS NULL
<>
!=
!>
!<
NOT
NOT EXIST
NOT LIKE
LIKE '%abc'
LIKE '%ABC%'
LIKE '%abc%'
a function on a column
column1 = column1
column1 = column2
For example these queries are non-sargable:
1) SELECT * FROM Customers WHERE Salary IS NULL
2) SELECT * FROM Customers WHERE Left(Name,4) = 'Jane'
3) SELECT * FROM Customers WHERE Name LIKE '%Jane%'
4) SELECT * FROM Customers WHERE PersonID NOT IN (SELECT PersonID FROM Persons WHERE Gender = 'Male')
You can better rewrite them to sargable ones:
1) SELECT * FROM Customers WHERE Salary = 0
2) SELECT * FROM Customers WHERE Name LIKE 'Jane%'
3) Cannot be rewritten
4) SELECT * FROM Customers WHERE PersonID IN (SELECT PersonID FROM Persons WHERE Gender = 'Female')
I have created a factsheet for SQL Server (6.5, 7.0, 2000 and 2005) developers that fits on one A4 (printed on both sides) so that you can laminate it and keep it on your desk. You have to print it with a color-printer to be able to use all information!
This factsheet contains loads of information about SQL Server and can be found here:
SQL Server factsheet for developers
Posted by Xander Zelders
0 Comments:
|
|
|
|
