Order By Queries - Performance impact and improvement recommendations

Abstract: Order By Queries - Performance impact and improvement recommendations

Problem: Why do order by queries seem slow and what can be done to improve an order by query? Solution: Suppose you have a table with 1 million records. And a query which will select 10000 of them. And an Order By. In order to know which record is the FIRST of the ordered set you must find all 10000 of them. And then a sort. This is why any query with an Order by or Group by takes longer to retrieve the first record. Now, if you have an appropriate Index, it's possible for the optimizer to follow the index to implement the Order By - resulting in faster first-record retrievals. Incidently, the other thing that can go on here is the buffering that's occuring on the server side. The server is trying to fill a packet-full of records before it sends any results to the client -- if it it takes several seconds to find each of the 10000 records in the result set, then it will take longer to fill the buffer - again resulting in a slower retrieval. Sometimes, the fastest way to retreive records in ordered fashion is to - create a temporary index - do your query (with order by) - drop the index (Of course, if you aren't as concerned with disk space, update/insert/delete time, then you can just leave the index). Also, for small result sets, sometimes it is more efficient to sort on the client side. (You do have to walk the cursor to the end of the result set, and have enough memory for the results). Source: David Brookstone Schnepper - Mers List 3/14/00