ranged datetime predicates & cardinality estimates

[scott.swank]

Hello all. I'm running SQL Server 2000 and I'm trying to get a very
few, recent rows of data from a table based on an indexed datetime
column. Here's my predicate:

where order_date > dateadd(hour, -1, getdate())

i.e. everything more recent than one hour ago. This corresponds to the
3 or 4 rows in which I'm interested. I have order_date indexed and I
have current statistics. When I check the explain plan for this query
I see expected rows returned: 114,000. When I go on to join to several
other tables I end up with unnecessary hash joins -- due to the
inaccurate cardinality estimates on this table.

However, if I use the following predicate (which corresponds to data
within the last 3 days):

where order_date > '2006-03-24'

then I see an estimated rows returned: 6 -- which is pretty accurate.

From there the rest of the query's explain plan falls together nicely.

So my question is: how do I get the optimizer to realize that one hour
ago is pretty recent?

Many thanks,
Scott

[Erland Sommarskog]

(sc*********@gmail.com) writes:

Hello all. I'm running SQL Server 2000 and I'm trying to get a very
few, recent rows of data from a table based on an indexed datetime
column. Here's my predicate:

where order_date > dateadd(hour, -1, getdate())

i.e. everything more recent than one hour ago. This corresponds to the
3 or 4 rows in which I'm interested. I have order_date indexed and I
have current statistics. When I check the explain plan for this query
I see expected rows returned: 114,000. When I go on to join to several
other tables I end up with unnecessary hash joins -- due to the
inaccurate cardinality estimates on this table.

However, if I use the following predicate (which corresponds to data
within the last 3 days):

where order_date > '2006-03-24'

then I see an estimated rows returned: 6 -- which is pretty accurate.

From there the rest of the query's explain plan falls together nicely.

So my question is: how do I get the optimizer to realize that one hour
ago is pretty recent?

To do this properly, you need to add another call level. One way is
to write an inner procedure and pass that procedure the computation
of dateadd(hour, -1, getdate() to that procedure. As alternative
you could call sp_executesql, but this reqiures the user to have
SELECT permission on the table.

The problem is that getdate() is a moving target. SQL Server does
not know that order_date is only in the past. Since getdate() is
an unknown value, it makes a standard assumption of a 30% hit-rate,
and then it goes downhill from there.

Another alternative is to use an query hint of some sort.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[scott.swank]

Why thank you, that took care of the issue. I knew that it was
something reasonably simple.

Scott