Ways to minimize sort operations

Question

Ways to minimize sort operations

brian118

Hall of Fame

Points: 3506
More actions
September 14, 2010 at 8:11 pm

#240283

Comments posted to this topic are about the item Ways to minimize sort operations
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

I'm a little confused here. What is the intention of the article? To avoid unnecessary Sort operators in the execution plan or to eliminate ORDER BY and still get the results sorted?

If the idea is to eliminate the need for ORDER BY then what this article describes should not be trusted. There is no guarantee that an index (clustered or non-clustered) will return the result in an ordered manner. IT might work today, but what happens when the amount of data or statistics changes? Will the QO find a better plan and havoc the ordered results that used to be returned to the client? It might very well do so.

There is only one way to guarantee ordered results. Say it after me: ORDER BY.

On the other hand, if you don't want SQL Server to waste memory, disk I/O and CPU on sorting, then you could send the result to the client and let it do the sorting. There is usually lots of unused CPU capacity on the client computers.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

Like Nils, I dont quite understand the reason for the article, in 95% of cases the sort operation is the least of your worries in a plan, especially when you've joined 2 or 3 tables together to bring back an amalgamaged dataset, such as Stock levels by product, and unfilled orders.

I'm not a fan of the Non-clustered Index with all other columns in an Include, one or two columns that are commonly associated with the business key, eg Indexed on ProductNumber, with the ProductName and ProductCode as include columns is reasonable.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

So the whole article is basically saying adding an index can assist in the performance of queries containing ORDER BY.

steveb. SSC-Forever Points: 46733 More actions · Answer 4

I dont see how people are saying this is dangerous, from looking at his code he does put Order by on each of the select statments so the data will always be ordered.

hallidayd SSCrazy Points: 2490 More actions · Answer 5

I agree with Steve - I'm not sure you have paid full attention to the SQL in the article. He is not suggesting removing order by, but creating indexes to optimise for the clause.

Having said that, I can't remember ever creating an index to support an order by. As said, there are countless other factors far more important.

I do have one issue with the article: there is no reason the IO should have been greater for the first query than the subsequent ones. I suspect that this was actually due to fragmentation of the original clustered index that of course was not present in the indexes created specifically for the tests. As such, this measure of improvement is false.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 6

steveb. (9/15/2010)
I dont see how people are saying this is dangerous, from looking at his code he does put Order by on each of the select statments so the data will always be ordered.

Oops, my bad!! I apologize :crying:

Rob Ashton Right there with Babe Points: 785 More actions · Answer 7

This might be a little nit-picky, so I apologise in advance, but I understood the DBCC DROPCLEANBUFFERS command to only clear clean buffers from the buffer pool. If you don't use the CHECKPOINT command before calling DBCC DROPCLEANBUFFERS, you haven't forced dirty pages out of the page cache onto disk to maximise the effectiveness of DBCC DROPCLEANBUFFERS. (See http://msdn.microsoft.com/en-us/library/ms187762.aspx)

If I'm right, this may also cause misleading performance results (or at least difficult-to-repeat performance results) between executions.

WayneS SSC Guru Points: 95460 More actions · Answer 8

Very nice article - Thanks!

I'm especially glad to see the effect of an update statement on the different methods discussed. And as you so properly point out, the proper answer is "it depends".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

brian118 Hall of Fame Points: 3506 More actions · Answer 9

Nils Gustav Stråbø (9/15/2010)
I'm a little confused here. What is the intention of the article? To avoid unnecessary Sort operators in the execution plan or to eliminate ORDER BY and still get the results sorted?
If the idea is to eliminate the need for ORDER BY then what this article describes should not be trusted. There is no guarantee that an index (clustered or non-clustered) will return the result in an ordered manner. IT might work today, but what happens when the amount of data or statistics changes? Will the QO find a better plan and havoc the ordered results that used to be returned to the client? It might very well do so.
There is only one way to guarantee ordered results. Say it after me: ORDER BY.
On the other hand, if you don't want SQL Server to waste memory, disk I/O and CPU on sorting, then you could send the result to the client and let it do the sorting. There is usually lots of unused CPU capacity on the client computers.

Thanks for your feedback!

The scope behind this article is to show how an already sorted result set will improve query performance since the data is already sorted. Let's take a DWH were having multiple indexes and/or Indexed views are not an issue since the data is preloaded during the ETL process. This method will improve query performance when running SQL or Reports on the source table.

And yes, I'll say it after you.... the ORDER BY will always guarantee that the data set is ordered, in fact that's why I did also specify that in the SELECT statements. 🙂

With regards to your last suggestion, yes that can be an option, however I'm trying to keep all processing centralized on the server, which is accessible by all. I would argue that if a data set is always required ordered by a specific column/s and multiple users need to access this data, why not sort it for everyone in the first place.

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

Brian, I'm have to apologize to you because for some reason my eyes and brain didn't see that you kept the ORDER BY clause. For some reason I thought that the article explained a way to get rid of the ORDER BY, but when I read it again I don't know where that idea came from :rolleyes:

Again, I'm sorry. Its a good article !

brian118 Hall of Fame Points: 3506 More actions · Answer 11

Jason Lees-299789 (9/15/2010)
Like Nils, I dont quite understand the reason for the article, in 95% of cases the sort operation is the least of your worries in a plan, especially when you've joined 2 or 3 tables together to bring back an amalgamaged dataset, such as Stock levels by product, and unfilled orders.
I'm not a fan of the Non-clustered Index with all other columns in an Include, one or two columns that are commonly associated with the business key, eg Indexed on ProductNumber, with the ProductName and ProductCode as include columns is reasonable.

Thanks for your feedback!

I would put my worries when studying a plan where I have the biggest cost, which in this case I wanted it to be the sort operator, and that's why I didn't use a complicated SELECT statement. Yes, I agree, if the SORT operator is only a fraction of the total query cost I would not bother with improving it!

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

brian118 Hall of Fame Points: 3506 More actions · Answer 12

Nils Gustav Stråbø (9/15/2010)
Brian, I'm have to apologize to you because for some reason my eyes and brain didn't see that you kept the ORDER BY clause. For some reason I thought that the article explained a way to get rid of the ORDER BY, but when I read it again I don't know where that idea came from :rolleyes:
Again, I'm sorry. Its a good article !

No problem and welcome 🙂

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

salman.samad Right there with Babe Points: 799 More actions · Answer 13

It's always refreshing to read clear, consise articles that get your grey matter spinning, even though they might not apply to you directly.

Nice job Brian!

deanroush SSC-Addicted Points: 474 More actions · Answer 14

Thank you for taking the time and effort to submit:-) this article. I always appreciate reading about how to optimize queries and actually being able to understand what the writer is trying to convey. I also appreciate the other feedback as a reality check!