Avoiding Calculations and Functions in WHERE Clauses

Here is an example of how a simple query change can make a pretty dramatic change in cost and performance.  Generally speaking, you want to avoid doing calculations or calling functions in the WHERE clause of your queries.

There is a unique, non-clustered index on the MeasurementTime and AvgRunnableTaskCount columns. Despite this, the original query did an Index scan on the non-clustered index, even though the resultset was only one row. This is bad. Changing the query to do the calculation ahead of time (instead of in the WHERE clause) gives us an index seek, and dramatically reduced logical I/O (from 786 to 4 logical reads).

 

— Orginal bad way (Do the calculation in the WHERE clause) ****************************************************************
SELECT AVG(AvgRunnableTaskCount)
FROM dbo.SQLServerSchedulerTaskHistory WITH (NOLOCK)
WHERE DATEDIFF(Minute, MeasurementTime, GetDate()) < 4 — Causes an index scan

— Original I/O statistics
— Table ‘SQLServerSchedulerTaskHistory’. Scan count 1, logical reads 786, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

— Improved way (Do your calculation ahead of time) *************************************************************************
DECLARE @ThreeMinutesAgo DateTime
SET @ThreeMinutesAgo = DATEADD(minute, -3, GetDate())

SELECT AVG(AvgRunnableTaskCount)
FROM dbo.SQLServerSchedulerTaskHistory WITH (NOLOCK)
WHERE MeasurementTime > @ThreeMinutesAgo — We get an index seek

— Improved I/O statistics
— Table ‘SQLServerSchedulerTaskHistory’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

— Even better and simpler way (courtesy of Brian Reischl)
SELECT AVG(AvgRunnableTaskCount)
FROM dbo.SQLServerSchedulerTaskHistory WITH (NOLOCK)
WHERE MeasurementTime > DATEADD(minute, -3, GetDate()) — We get an index seek

–Table ‘SQLServerSchedulerTaskHistory’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

One Response to Avoiding Calculations and Functions in WHERE Clauses

  1. Brian says:

    Thanks for the credit Glenn, but that\’s spelled "Reischl"🙂

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