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,StartedAt, EndedAt) ;
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.