THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Yet another example of defensive query optimization

My second take at the same problem I blogged about yesterday:

To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we are telling to the optimizer may be no longer relevant. In the previous post, Optimizing a query, then documenting assumptions in a unit test , we defined the problem and used a unit test to document an assumption. Read the previous post before proceeding.

In this post we shall eliminate the assumption altogether, and our code will be safer and easier to maintain. Instead of hardcoding the maximum duration of an event, we shall read it from the same table. To make sure that we read it efficiently, we shall use an index on a computed column:

ALTER TABLE dbo.Events ADD EventDurationInMinutes AS  DATEDIFF(MINUTE,StartedAtEndedAt) ;          
CREATE INDEX Events_EventDurationInMinutes ON dbo.Events(EventDurationInMinutes) ;

This index allows to retrieve maximum duration efficiently, as shown in the following procedure:

 ALTER PROCEDURE dbo.SelectRunningEventsForTime @AsOfTime DATETIME
DECLARE @MaxEventDurationInMinutes INT ,
@EarliestPossibleStart DATETIME ;
SET @MaxEventDurationInMinutes SELECT   MAX(EventDurationInMinutes) + 1
FROM     dbo.Events
) ;  
SET @EarliestPossibleStart DATEADD(MINUTE,
@AsOfTime) ;
StartedAt ,
FROM    dbo.Events
WHERE   @AsOfTime BETWEEN StartedAt AND EndedAt
AND StartedAt >= @EarliestPossibleStart ;

Let us run this procedure:

EXEC dbo.SelectRunningEventsForTime @AsOfTime '20100223 01:38:08' ;

Selecting the maximum duration uses 3 reads, it is a simple index seek

Table 'Events'. 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.

 Reading the rows takes just a few more reads.

Table 'Events'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


We have added an index on a computed column, and changed the stored procedure, so that it does not use any hardcoded constants. As such, the procedure is safer. Quite likely this improved stored procedure will require less maintenance.


Published Thursday, February 18, 2010 3:11 PM by Alexander Kuznetsov
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement