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

Oracle:RowId <=> Sql-Server:?

Hi all

I have an easy question. In Oracle I can retrieve a column named "ROWID" which returns an unique identifier of the row in the
database. I want to have the same element in SQL Server.

Do you know how is this handle in SQL Server ?

Thanks in Advance

Fabian BonillaRefer this link (http://www.sqlteam.com/item.asp?ItemID=283) is any help.|||ROWID is maintained internally and is not accessible through T-SQL.|||Originally posted by rdjabarov
ROWID is maintained internally and is not accessible through T-SQL.

That pity: thanks for all .|||Originally posted by Satya
Refer this link (http://www.sqlteam.com/item.asp?ItemID=283) is any help.

Thanks for all|||Originally posted by rdjabarov
ROWID is maintained internally and is not accessible through T-SQL.
@.@.IDENTITY ?|||It returns the last-inserted identity value.