THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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) ;          
GO
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
AS 
    BEGIN 
;
        
DECLARE @MaxEventDurationInMinutes INT ,
            
@EarliestPossibleStart DATETIME ;
        
SET @MaxEventDurationInMinutes SELECT   MAX(EventDurationInMinutes) + 1
                                           
FROM     dbo.Events
                                         
) ;  
        
SET @EarliestPossibleStart DATEADD(MINUTE,
                                             -
@MaxEventDurationInMinutes,
                                             
@AsOfTime) ;
        
SELECT  EventID ,
                
StartedAt ,
                
EndedAt
        
FROM    dbo.Events
        
WHERE   @AsOfTime BETWEEN StartedAt AND EndedAt
                
AND StartedAt >= @EarliestPossibleStart ;
    
END ;
 

Let us run this procedure:

SET STATISTICS IO ON ;
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.
(snip)

 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.

Conclusion

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Leave a Comment

(required) 
(required) 
Submit

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 sqlblog.com, 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 simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement