SQL Server Performance Forum – Threads Archive

Performance majorly hurt by ORDER BY

I’m amazed at my one query which takes about 30 seconds to run but less than a second if I remove the ORDER BY. [:0] Not only that, but I find that the execution plan is drastically different, just because of the ORDER BY clause. Here is the actual SQL statement (Shop_Activity is actually a view): <pre> SET ROWCOUNT 50 SELECT OrgName, VoidDate, City, Province, LastName, FirstName, CONVERT(varchar, TimeStamp, 111) AS TransDate, Class, SubClass, Shop_Activity.TransID, serial, Description, tt.TransType, tt.transdesc FROM Shop_Activity inner join transtype tt on tt.transtype = shop_activity.transtype WHERE City = ‘Calgary’ AND Shop_Activity.TransType IN (1,3,5) order by transdate desc</pre> Now, here is the execution plan for the query with the ORDER BY (you can tell by this that I’m using a dual CPU server): <pre> |–Parallelism(Gather Streams, ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1015] DESC)) |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1015] DESC)) |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1015]=Convert([Transactions].[TimeStamp]))) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tt].[TransType])=([Transactions].[TransType])) |–Parallelism(Broadcast) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[TransType] AS [tt])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ItemSubClasses].[ID])=([Property].[SubClassID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[SubClassID]=[ItemSubClasses].[ID])) |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ItemSubClasses].[ID])) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[ItemSubClasses])) |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[SubClassID])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ItemClasses].[ID])=([Property].[ClassID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[ClassID]=[ItemClasses].[ID])) |–Parallelism(Broadcast) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[ItemClasses])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TransItemsXRF].[PropertyID])=([Property].[PropertyID])) |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TransItemsXRF].[PropertyID]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1018])) | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TransItemsXRF].[PropertyID])) | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[TransID])=([TransItemsXRF].[TransID])) | |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[TransID]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1017])) | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[TransID])) | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[CustID])=([Customer].[CustId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[CustID]=[Customer].[CustId])) | | |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[CustID]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1016])) | | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[CustID])) | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Organizations].[OrgID]) WITH PREFETCH) | | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Organizations]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Organizations].[City]=’Calgary’)) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Transactions].[IX_Transactions_OrgIDCustID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[OrgID]=[Organizations].[OrgID]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([Transacti | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Customer].[CustId]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1016])=TRUE)) | | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Customer].[Customer_LastFirstName])) | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TransItemsXRF].[TransID]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1017])=TRUE)) | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[TransItemsXRF].[IX_TransItemsXRF_TransId])) |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[PropertyID]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1018])=TRUE)) |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Property]))</pre> And there is the execution plan without the ORDER BY: <pre> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1015]=Convert([Transactions].[TimeStamp]))) |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1002]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[TransType] AS [tt])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[TransType])) |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1013]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[ItemClasses])) | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[ClassID])) | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1011]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[ItemSubClasses])) | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[SubClassID])) | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[CustID]) WITH PREFETCH) | | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1009]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Property])) | | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TransItemsXRF].[PropertyID]) WITH PREFETCH) | | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[TransID]) WITH PREFETCH) | | | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Organizations].[OrgID]) WITH PREFETCH) | | | | | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Organizations]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Organizations].[City]=’Calgary’)) | | | | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Transactions].[IX_Transactions_OrgIDCustID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Transactions].[OrgID]=[Organizations].[OrgID]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([Transactions].[TransType]=5 OR [Tr | | | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[TransItemsXRF].[IX_TransItemsXRF_TransId]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TransItemsXRF].[TransID]=[Transactions].[TransID]) ORDERED FORWARD) | | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Property].[IX_Property_PropertyIDDescription]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Property].[PropertyID]=[TransItemsXRF].[PropertyID]) ORDERED FORWARD) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[Customer].[IX_Customer_CustIDLastName]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Customer].[CustId]=[Transactions].[CustID]) ORDERED FORWARD) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[ItemSubClasses].[PK_ItemSubClasses]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ItemSubClasses].[ID]=[Property].[SubClassID]) ORDERED FORWARD) | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[ItemClasses].[PK_ItemClasses]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[ItemClasses].[ID]=[Property].[ClassID]) ORDERED FORWARD) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[BWIdent].[dbo].[TransType].[PK_TransType] AS [tt]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tt].[TransType]=[Transactions].[TransType]) ORDERED FORWARD) </pre> Am I missing something here? What could I do to improve the performance? The only thing I could think of is to do the query with an INSERT INTO #temp and then do a SELECT * FROM #temp ORDER BY TransDate. Is there anything that can be done to the query iteself? [?] Query Analyzer says that no new indexes need to be created. BTW, I love this site [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]. Absolutely fabulous source of information… Thanks, Francois Lachance

When I run into problems like these, I do a lot of trial and error tuning. One of the major differences between the two execution plans is that the first one (with ORDER BY) uses hash joins, while the second one (without ORDER BY) uses nested loop joins. In general, a nested look join is faster than a hash join. Because of this, one of the things I would test for (and it may or may not help much) is to use a hint in order to force the query to use a nested loop join instead of a hash join. Secondly, I would take a look at the indexing columns affected by this query, checking to be sure that the join columns are properly indexed, and that the ORDER BY column is properly indexes. A clustered index on the ORDER BY column might be helpful, but again, a clustered index might be more useful on another column. You won’t know until you try. And one more thing to try, sometimes parallelism can do more harm than good. You might experiment with turning off parallelism for this query to see what happens, just for the fun of it. Use a hint to turn it off for this query only. See this url for more information on using hints: http://www.sql-server-performance.com/hints.asp
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Thanks Brad! My solution was totally different (, but I will try what you have suggested. My solution was to run the query without the ORDER BY but with a INSERT INTO #temp. Then I do a SELECT * FROM #temp ORDER BY TransDate. That fixed the problem. Now, I’m sure you’re thinking "eeks, he’s using a temporary table!" [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!] I agree, I don’t like it, but it works. I’ll try your hints suggestion. It’s kinda tough to know where to start when it comes to hints. You have to understand the different kind of action that your execution plan shows you. I mean, I haven’t exactly found a list anywhere that says that a hash join is usually worst performance then nested loop for example. The link you gave for example explains how to apply hints, but doesn’t say much about which one is better in what situation (got something like that? [?]) Thanks, Francois

Unfortunately, I don’t have a lot of information on my website about how to best apply hints, as in most cases, their use requires a lot of intimate knowledge of SQL Server execution plans, and lots of just plain trial and error. But a merge join is faster than a hash join, based on my research and my own experience. As I gain more experience myself with hints, I will publish my findings on my website. Does anyone else have any input they would like to provide on how they have used hints, and their successes and failures?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

You may want to check to make sure that your table statistics are updated as well. You will see drastic differences in execution plans based on the validity of this data. Also, you may want to try removing individual columns from your SELECT in order to troubleshoot index issues. -dataninja- "The normalcy of a database is inversely proportional to that of it’s DBA."

If you are inserting into a temp table then ordering a select * from #tmp you might want to try removing the temp table altogether and use a derived table. SELECT * FROM
( SELECT OrgName, VoidDate, City, Province, LastName, FirstName,
CONVERT(varchar, TimeStamp, 111) AS TransDate, Class, SubClass, Shop_Activity.TransID, serial, Description,
tt.TransType, tt.transdesc
FROM Shop_Activity inner join transtype tt
on tt.transtype = shop_activity.transtype
WHERE City = ‘Calgary’ AND Shop_Activity.TransType IN (1,3,5)
) AS temp
ORDER BY TransDate That may save some time by removing the overhead of using a temp table, then again SQL might try to optimise it and turn it back into your original query. Test it and see how it works. (Though check the indexes as Brad suggested first – indexes can do wonders for poorly performing queries!)

I like the insert into temp tables and then sort solution. Atleast, you know what you are getting. Just add a comment to the code and write down the original query. When you go back in later to change something, you know what is the result you wanted and what was the otpimization you did. IMHO, using hints and forcing table order in a query makes you look like a clever fellow in the short run but it will bite you in the long run. Here is one experience: Optimizer was doing a hash join of two very large tables when we knew from the application viewpoint that the top half of the join would only have a few rows after all the contraints were applied. So we forced it to use the nested loop join. Order of magnitude performance gain. Fabulous. We patted ourselves on the back that day and walked away. Everything was fine till one day an obscure part of the application decided to use the same query with none of the contraints on the top half of the join. That it turned out to be the day that brought down the production server!

]]>