Sql Server: Cautions Must Be Taken for WHERE Clause


During query performance tuning sessions, I have observed that most of us like to care about columns that are going to be used in WHERE clause. Indexes are carefully created on these columns to enhance performance. But most of the time we are unaware that these indexes are useless, because of the way we use columns in WHERE clause. Specially be careful for following two situations.
  1. Arithmetic Operators
SELECT *
FROM dbo.TestTable
WHERE columnName * 5 > 500
  1. Functions
SELECT FirstName
FROM HumanResources.Employee
WHERE SUBSTRING(FirstName,1,3)=‘Joh’
Use of arithmetic operations and functions in WHERE clause causes the optimizer to omit indexes (created on these columns). Resultantly query performance decreases dramatically.

Rewrite your queries to avoid arithmetic operators and function in WHERE clause.

Advertisements

Posted on December 16, 2010, in Performance Tuning. 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: