ranged datetime predicates & cardinality estimates

[scott.swank@gmail.com]

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.[color=blue]
>From there the rest of the query's explain plan falls together nicely.[/color]
So my question is: how do I get the optimizer to realize that one hour
ago is pretty recent?

Many thanks,
Scott

[Erland Sommarskog]

Re: ranged datetime predicates & cardinality estimates

(scott.swank@gm ail.com) writes:[color=blue]
> 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.[color=green]
>>From there the rest of the query's explain plan falls together nicely.[/color]
> So my question is: how do I get the optimizer to realize that one hour
> ago is pretty recent?[/color]

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, esquel@sommarsk og.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

[scott.swank@gmail.com]

Re: ranged datetime predicates & cardinality estimates

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

Scott