467,917 Members | 1,358 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,917 developers. It's quick & easy.

ranged datetime predicates & cardinality estimates

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

Mar 27 '06 #1
  • viewed: 1762
Share:
2 Replies
(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
Mar 27 '06 #2
Why thank you, that took care of the issue. I knew that it was
something reasonably simple.

Scott

Mar 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Shaun | last post: by
4 posts views Thread by Negroup | last post: by
2 posts views Thread by Thong Nguyen | last post: by
2 posts views Thread by Rizyak | last post: by
5 posts views Thread by Jason Madison | last post: by
4 posts views Thread by Arun Srinivasan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.