Issue With DateTime Variable + Execution Plan

[sendba]

Hi,

I am having issues with Datetime Variables in the execution plan. I wanted to select the data for a given date from a table. When I use values in the where clause the execution plan shows as Index Seek (0% Cost). The same query if I assign the value to a variable and use it in the where clause the execution plan show as Index Scan.

For example :

Select * from tablename where dtcreated between '2009-01-01' and '2009-01-02'

The above statement shows the Index Seek.

Declare @begin datetime, @end datetime
set @begin = getdate() - 1
set @end = getdate()

select * from tablename where dtcreated between @begin and @end.

The above statement shows the Index SCAN

Can someone please let me know if you have come across these kind of issue and how to resolve it.

Thanks in advance

Sen

 

[gmmastros]

What do you get for this:

Select Data_Type 
From   Information_Schema.Columns 
Where  Table_Name = 'YourTableName' 
       And Column_Name = 'dtcreated'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sendba]

Hi GMmastros,

I am getting Datetime for the query which you have sent.

 

[gmmastros]

Hmmm.....

Obviously, you must have an index on the dtcreated column, or else the first query would have scanned too.

The next thing I would try (if this were happening in my database) would be to recreate the index, which should also update the statistics. You see... even with an index on a column, if MOST of the data you are selecting would be returned, it's faster to scan the clustered index instead of seeking on a non-clustered index. If I'm not mistaken, it's actually the statistics that determine this.

To re-build the existing indexes...

dbcc dbreindex('YourTableName')

Please understand that it may take a little while to re-build the indexes for a table. If you have millions of rows in this table, you may want to wait until there is some acceptable down-time before rebuilding the indexes.

If you are using SQL2005 or above, you can rebuild the indexes ONLINE so that the data in the table is still available while you rebuild the indexes. Info found here:

http://blogs.techrepublic.com.com/datacenter/?p=249

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sendba]

George,

I have tried all the things including Droppping and recreating the indices and updating the statistics but nothing helps. Its a 20 Million record table. If have that
kind of table please check it yourself you will know.

Please help

Thanks