Large table, really slow queries

I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.

The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:

select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0

As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).

may be you can try table partitioning in sql 2005..............but i am not sure about this....refer,

http://dumthata.com/sqlchn/articles/dpart.htm

|||

You should rewrite the query to remove the DATEDIFF function from the WHERE clause. This causes SQL Server to scan the table.

It is better to use WHERE dt_date BETWEEN '2007-07-26' AND '2007-07-27' for example.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wow, what a world of difference it makes if I don't use datediff()!

Is there a general rule that will tell me whether the functions I use in my WHERE clause will cause a table scan versus index look-up? I'm not a DBA, just a .NET developer working on the back-end at the moment.

Immensely helpful, thank-you.
|||

Not just a rule of thumb, but an absolute no-no.

If you 'wrap' a field with a function, the query processor MUST apply that function to every row in the table (or index) to determine if the 'functionalized' value meets the criteria. As a result, the query will NOT efficiently utilize any indexing. It will require at least an index scan, and perhaps even a scan of the entire table.

If in a query, all rows newer than seven days ago are sought, you can either add seven days to the stored value and compare to the current datetime, or you can subract seven days from the current datetime and compare to the stored value.

For example, in this WHERE clause, :

WHERE dateadd( day, 7, MyColumn ) >= getdate()

Indexing on MyColumn cannot be properly used, the query will need to do an index scan (if MyColumn is indexed), and a table scan otherwise.

However you can easily use a function on the non-field side of the equality and still use indexes.

WHERE MyColumn <= dateadd( day, -7, getdate() )