Referencing index when recordset open not supported with SQL Server

[emuti]

We are converting an Access database to a SQL Server back end. In doing so, we have found that our DAO method of referencing an index when opening the recordset is not supported with SQL Server.

We have gotten to the point that we have to use an "Order By" clause in our ADO-based SQL query in order to get the result set back in the order that we want. However, even with indexes on the appropriate fields, the order by is much slower than the original Access-based method (assigning the index property of the recordset).

Does anyone know of a faster method to retrieve data in a particular order from a SQL Server database using any faster method than an "Order By" clause?

 

[tlbroadbent]

Some questions:

1- How many rows are you retrieving and how many bytes in each row? This will impact Order By speed.
2- Have you measured the query time without the Order By clause? How does it compare to the query time when using Order By?
3- Have you tested the query in Query Analyzer to see how long it runs? This will help you determine if the bottleneck is at SQL Server or in the client application.
4- Does your table have a clustered index? Is the index on the Order By column? Results will be returned in clustered index order so Order By would be unnecessary.
5- Have you examined the query execute plan in Query Analyzer to determine where the bottlenecks might be? The execution plan will also tell you which indexes, if any, are being used in the query?
6- Have you performed Index Analysis to see if your indexes are appropriate for the query in question?

The preceding questions are of little worth if you don't have a where clause in your query. If you are returning all of the rows in a table to the client, the most likely areas of slowdown are the client application and the network. Or the Order By clause if the number of rows is very large.