ORDER BY efficiency

Hi,

I'm using a table with about 5 million records, i'm preforming a select query onthe table. I retrieve from that query about 2-4 million records when i'm not sorting the data the query return very fast. If I'm sorting the data (ORDER BY timestamp - when timestamp is a BIG_INT with an index) the query takes a long time to return about 30 seconds.

I tryed to do the same thing with access DB and i get better results: the query with ORDER BY takes about 1 second.

I dont think it's possible that access DB have better performance then SQL server, does any one have any idea what can be me problem?

thanks ishay.

can you set this filed as clustered index?|||

It will normally take much longer to sort 2-4 million rows than it will to simply select the data. Also, is the access database on your local machine? If this is the case, then YES, Access might well APPEAR WAY more efficient than SQL Server on an external machine because with the Access database you do not have to spend telecommunication time transceiving the data between your machine and the server.

Dave

|||What's the definition of the index on the timestamp column? Is it the clustered index? Have you looked at the query plan? In SQL Server Management Studio select "Include Actual Execution Plan" under the query menu to see the query plan. You want to see a clustered index scan over the index for the timestamp column for best performance.|||

Thank you all. I set the index to be clustered index and it's working prepectly!

Ishay