SQL Server query freezes when I use "where"

Question

I'm using SQL Server 2005 Express, and I'm running into a strange issue. I have a table called "DailyPrice" that has about 24 million records (I was able to make this table thanks to all your help in this thread: http://stackoverflow.com/questions/1983611/sql-server-2005-slows-down-as-i-keep-adding-rows)

Now, I'm running a different fancy function in VB.NET where I need to get a list of entries based on a ticker symbol (just 1), and I need them organized from oldest to newest.

This query works:

SELECT     Ticker, DateStamp, ClosePrice
FROM         DailyPrice
WHERE     (Ticker = 'DD')

This one is either agonizing slow, or I get a timeout error:

SELECT     Ticker, DateStamp, ClosePrice
FROM         DailyPrice 
WHERE     (Ticker = 'DD')
ORDER BY DateStamp

The fields Ticker and DateStamp are my primary index, in case that matters. Why does this slow down when i use ORDER BY? A couple hours ago the problem query ran speedily and fine, but only once - and now I'm plagued by timeouts.

Answer 1

UPDATE your statistics or rebuild your indexes.

This will re-index an entire database (caution if database is very large!):

exec sp_msforeachtable "dbcc dbreindex('?')" 

Answer 2

Query with ORDER BY will always take longer to return than the query without it. This is because SQL Server has to get all the records first and then sort them before it can return the result to the client. You may be able to speed up the query by adding indexes that match the fields you use in ORDER BY and WHERE clauses. But be careful, too many indexes can cause insert/update to be much slower and also SQL Server can decide to use the wrong index. You can specify the index as part of the table hint in FROM clause but that will make your query dependent on the presence of that index. Finally having 24+ mil records in one table will always cause issues. I would suggest you consider partitioning you table, specially if you can find a field that splits the data in several smaller sets of similar size - look at SQL Server Books online for more info on partitioning (http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx)