Query results faster for larger criteria range

[tmccoy1]

I have a SQL Server 2008 query that runs by a date range for transactions. If I limit the date range to a period of 5 days or less, the query takes almost 2 full minutes to execute. However, if I set the date range for over 5 days, even for a period of several months, the results come back almost instantly. This is a good thing in general, but curiousity is killing me as to why this would happen. Is there anything in general with SQL, SQL Server or databases that might lead to this that anyone knows of?

 

[gmmastros]

My best guess is that you have outdated statistics.

Run this query to find out...

SELECT name AS index_name,
       STATS_DATE(OBJECT_ID, index_id) As StatsUpdated
FROM   sys.indexes
WHERE  object_id = OBJECT_ID('[!]Your Table Name Here[/!]'

If you see any old dates in the StatsUpdated column, then it's likely the problem. If this is the problem, let me know and I will show you how to fix it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tmccoy1]

Thanks. That is likely the cause. The query returns NULL NULL on all of the tables. I don't have admin access here and would have to take this up with the DBA, but this is also a data warehouse copy of an Intersystems Cache database, and I guess there may be some reason it is this way. I'll have to inquire as I'm not familiar with the Cache database platform.

 

[gmmastros]

If it is showing NULL, NULL that indicates you do not have any indexes on that particular table. If you get NULL, NULL for all tables, that would indicate there are no indexes anywhere.

I would strongly encourage you to use indexes.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom