SQL Server Performance Forum – Threads Archive

ORDER BY is Slow

I have a CRM app and I am having some bad performance on various queries.
The queries that are bad performing bad happed to have an Order By statement
When I run the Query without the Order By the performance gain is huge.
And it doesn#%92t matter if you get one row returned or 10,000 rows the query is still slow. I am wondering what are some reasons why the Order By can case such bad performance?? I am thinking it has to do with an index, but what do you index? Do I need to create a non clustered index that covers the two order by columns? Why does the Where Clause not speed up the query? The Order By: T1.OWNER_EMP_ID DESC, T1.ACT_OPEN_DT DESC. The select query itself is massive (273 columns, 45 joins). The main table has over 2 million records. And no I can#%92t change this.
Thanks.

---

follow the procedures outline below as applies to SQL 2000
for the slow query with ORDER BY and not slow w/o ORDER BY http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19595

---

Are they "Select top" queries?

---

Also check if the columns in the ORDER BY are covered by a single index. If the columns are from different tables, you might try using an indexed view instead of this query. Or try adding an index with DESC order for both columns.

---

The two columns are in the same table.
When you say a single index that covers the ORDER BY Clause what exactly do you mean? There are over 65 different indexes for this table, and I don’t see these two columns together in any of the indexes.
They are located in indexes but not together. The queries in the application don’t use a Top command instead they use option (fast 40).

---

A covering index for the ORDER BY clause would be one with your ORDER BY columns as the first two columns, and both in descending order (OWNER_EMP_ID DESC, ACT_OPEN_DT DESC). There will be a trade-off to some degree for adding the 66th index to this table, inasmuch as it will speed up this particular query, but may cause longer processing time for inserts, updates and/or deletes on the table. However, as the table already has 65 indexes (YIKES!) I doubt that this will be a noticeable change.

---

Put the Query into Analyzer and did the Graphics Execution Plan:
With the Order By:
First Operation: Index Seek on the EmployeeID Cost 0%
Second Operation: Bookmark Lookup COST 93% —I am taking this is a problem?
Without the ORDER BY:
Index Seek on EmployeeID Cost: 1%
Bookmark Lookup Cost 34%
Will building a covering index lower the Bookmark Lookup with the Order BY?

---

This is without the suggested index? The percentages are proportionate to the whole query. The bookmark lookup probably weighs in extremely heavy because of the double descending ORDER BY, with no supporting index.

---

With (fast 40) your query optimizer probably chooses non-clustered index when table scan would be more efficient(because of low selectivity criteria). Remove fast 40 hint and you may have faster queries. Also, 65 indexes on singe table is way too much. How many rows are in the table?

---

Agreed on the number of indices. However, that shouldn’t affect SELECT performance anyway. But I guess modification performance is also not the best around. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

I didn’t mean that too many indexes afects select performance. My tought was that since there are so many indexes, then there are probably not many changes (inserts/updates/deletes), eitherwise jchiles would probably report data modifications performance problem. Not much changes means there are not too many rows in the table (up to 100000 or so). Hence, table scan would probably give better perfromance, but since the hint fast 40 is used, query optimizer decided to optimize the time first 40 rows will be returned and used non-clustered index because of that. My guess is that not too much rows is the reason or not selective enough criteria.

---

Thanks everyone for the reply.
I did find the answer; the application uses ODBC cursor operations.
When a statement with an ORDER BY comes into SQL and the Order By does not have Index it changes its cursor type, which causes a massive slow down. When I tested the query out in query analyzer it was fast, but I didn’t put the query into a cursor.

---

]]>