|
Posted by brianlanning on 02/08/07 17:03
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.
There's some joins, but the main table has maybe 20 million rows.
This is sql server 2000.
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?
brian
Navigation:
[Reply to this message]
|