Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Ways to minimize sort operations Expand / Collapse
Author
Message
Posted Wednesday, September 15, 2010 7:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:58 AM
Points: 1,837, Visits: 3,420
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

Again, I'm sorry. Its a good article !
Post #986252
Posted Wednesday, September 15, 2010 7:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 238, Visits: 535
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 -
Post #986253
Posted Wednesday, September 15, 2010 7:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 238, Visits: 535
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

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 -
Post #986256
Posted Wednesday, September 15, 2010 9:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:28 AM
Points: 461, Visits: 230
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!
Post #986365
Posted Wednesday, September 15, 2010 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 9:10 PM
Points: 26, Visits: 263
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!
Post #986396
Posted Wednesday, September 15, 2010 9:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
there were a few times i changed the sort order of indexes to make things a bit faster. when you can't spend $6000 on extra storage because all the internal hard drive bays are full but you need to optimize I/O performance you look at every little thing.

i'm waiting for the SSD server drives to get cheaper to try them out by putting tempdb on them


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #986415
Posted Wednesday, September 15, 2010 11:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
alen teplitsky (9/15/2010)
there were a few times i changed the sort order of indexes to make things a bit faster. when you can't spend $6000 on extra storage because all the internal hard drive bays are full but you need to optimize I/O performance you look at every little thing.

i'm waiting for the SSD server drives to get cheaper to try them out by putting tempdb on them


After playing with SSDs for tempdb, I've concluded it's best to make all drives SSDs to get the max bang for the migration. Just tempDB seems to be an awfully small improvement for the level of disruption on the main box. On the other hand, for external raids and such, SSDs can be slowly implemented as prices come down.
Post #986544
Posted Wednesday, September 15, 2010 11:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
the enterprise SSD's are something like $7000 for 200GB or so. forgot the exact numbers from the HP branded ones. a 146GB hard drive is less than $300. for the data i can buy up a lot of hard drives to spit the IO up. the new Proliant G7's support up to 16 hard drives in a 2U server

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #986551
Posted Wednesday, September 15, 2010 12:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:55 PM
Points: 21,204, Visits: 14,889
Nice article - thanks. Good examples.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #986583
Posted Thursday, September 16, 2010 9:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:05 AM
Points: 90, Visits: 132
A more efficient and clean way to do it is on client side

Bouarroudj Mohamed
www.sqldbtools.com
Post #987371
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse