Query datetime and its performance

Hi all,

I know I miss something very obvious here, but I can see it at the moment - I am very appreciated if someone could help.

Why am I have a table scan when I run the query below - note created_date is indexed.

DECLARE @.LastDate datetime

SET @.lastDate = (SELECT DATEADD(hh,-1,getdate())

SELECT * FROM Report WHERE Created_Date >@.lastDate

However, I don't have table scan when I do the following query

SELECT * FROM Report WHERE Created_Date > (Getdate()-1)

Thanks in advance

Because you are using SELECT statement to obtain the value. Use the following sentences instead:

DECLARE @.LastDate datetime;
SET @.lastDate = DATEADD(hh,-1,getdate();
SELECT * FROM Report WHERE Created_Date>@.lastDate;

Regards,|||

Thanks for replying Ronald

but nope, tried that - I still have table scan. :-(

BTW I am using SQL 2000

|||

Philly:

I think that the answer is in two parts. First, your SELECT * query requires a bookmark lookup for each row selected by your where clause whenever it uses an nonclustered index that does not include all columns of the table as part of the index -- and that is the case with your index based only on this date. The optimizer "knows" that if there are enough bookmark lookups that it is more efficient to do a table scan than an index scan that includes bookmark lookups. Second, (somebody PLEASE check me on this one) the optimizer has special handling for "getdate" that it does not have for the variable. Therefore, it can "figure out" that there are not enough rows in your GETDATE()-1 query so that the cost of the bookmark lookups do not outweigh the cost of the table scan. It does NOT make this optimization when you compare to your variable.

Dave

|||In this case, the table scan is probably the most effienct way to get the data, according to the query optimizer. Table scans are not always bad.

Your select statement is getting all fields in the table and excluding probably a handful of rows. If it uses an index, it will have to find each matching record in the index, get the PK from the index, look up the PK in the clustered index to get the record. Not using the secondary index, it just reads the table and does the compare and it is done.

Now, if you were doing a "SELECT Created_Date FROM ...." That should use the index, because the data is all contained in the index and doesn't need to reference the table at all.|||

Thanks all for replying,

I guess this is one case that query optimizer try to out smart us, which is not a bad idea in some case. The problem I have is the table I am query from is very large, several million records, so table scan is bad for me. I don't know if Getdate() does some special handling as Dave mentioned, but it definetly handle different than the variable - which is interesting. However, I understand Tom's suggestion is use "SELECT Created_Date FROM..". I tried that and it did works nicely, I wonder if there is other way to force sql use index here instead of including the created_field after the select statement.

Thanks again

|||

You can force SQL Server to use an index. This usually isn't recommended, but may solve your problem. The syntax would be...

SELECT * FROM Report With(Index=IndexNameHere) WHERE Created_Date>@.lastDate;