Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Brian Ellul » Ways to minimize sort operations
Add to briefcase
21 posts, Page 1 of 3
123»»»

Ways to minimize sort operations Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
brian118
Posted Tuesday, September 14, 2010 8:11 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:31 AM
Points: 228, Visits: 495
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 -
Post #985988
Nils Gustav Stråbø
Posted Wednesday, September 15, 2010 12:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:44 PM
Points: 1,786, Visits: 3,323
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.
Post #986048
Jason-299789
Posted Wednesday, September 15, 2010 1:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122
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
Post #986071
Jonathan AC Roberts
Posted Wednesday, September 15, 2010 2:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:46 AM
Points: 316, Visits: 1,184
So the whole article is basically saying adding an index can assist in the performance of queries containing ORDER BY.
Post #986090
steveb.
Posted Wednesday, September 15, 2010 3:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103
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.
Post #986103
hallidayd
Posted Wednesday, September 15, 2010 3:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 03, 2013 2:50 AM
Points: 112, Visits: 294
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.
Post #986114
Nils Gustav Stråbø
Posted Wednesday, September 15, 2010 3:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:44 PM
Points: 1,786, Visits: 3,323
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
Post #986119
Rob Ashton
Posted Wednesday, September 15, 2010 5:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:16 AM
Points: 219, Visits: 93
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.
Post #986186
WayneS
Posted Wednesday, September 15, 2010 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 6,367, Visits: 8,226
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #986226
brian118
Posted Wednesday, September 15, 2010 7:22 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:31 AM
Points: 228, Visits: 495
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 -
Post #986249
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.