Posted by Erland Sommarskog on 02/08/07 23:15

brianlanning (brianlanning@gmail.com) writes:
> We have a stored procedure that we've tried with two slightly
> different designs. It needs to take a 30 day date range and return a
> result set.
>
> Design 1 takes one date as a parameter. The other date is calculated
> in a local variable to be 30 days before the one that was passed in.
> Both data types are datetime and are in the where clause.
>
> Design 2 takes two dates as parameters with the 30 days being
> calculated outside the stored procedure, both in the where clause.
>...
> Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
> faster.
>
> The plan says that Design 1 is doing a table scan on the 20 million
> row table. For Design 2, the plan says it's doing a bookmark lookup
> on the date in question.
>
> Why?

When the optimizer compiles the query plan, it works the stored procedure
as a whole. Thus it has no knowledge of the values of local variables.
Instead it applies standarad assumption which for BETWEEN is 20%, if
memory serves.

However, the optimizer does look at the parameter values, and use
these as guidance. When it knwos both end of the period, it can
estimate more exactly how many rows you will select. Note however
that this plan is cached, and if you the next time call the procedure
with an interval of, say, two years, the same plan will be used,
although that plan may not be good for this longer interval.

What you also could try is:

datecol BETWEEN @startdate AND dateadd(DAY, 30, @startday)

Hopefully, the optimizer understands the condition and can act
accordingly.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]