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 «««1920212223»»»

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten) Expand / Collapse
Author
Message
Posted Sunday, September 5, 2010 4:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Wayne,

The post you're looking for is: http://www.sqlservercentral.com/Forums/FindPost816917.aspx

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #980774
Posted Sunday, September 5, 2010 5:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Thanks Paul.

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 #980777
Posted Sunday, September 5, 2010 3:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 6,043, Visits: 8,323
Paul White NZ (9/4/2010)
Hugo Kornelis (9/4/2010)
My examples on page 11 in this topic still produce incorrect results.

If we add the safety check, things work out nicely:
(...)
Correct results and an optimal query plan!

Indeed. The check itself changes the options for the optimizer. The method now even appears to be immune to parallel execution problems, since the parallel streams have to be gathered and re-synched before the row numbers can be calculated.

There may be a way to break this method on current versions of SQL Server, but it takes someone smarter then me to find it. And even if you accidentally stumble over it, or if new versions of the optimizer start to wreck this method, you're still safe because of the builtin safety check.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #980840
Posted Sunday, September 5, 2010 3:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 6,043, Visits: 8,323
Jeff Moden (9/4/2010)
Hugo, it takes one awesome professional to post something like that above. Even if we vehemently disagree (and you'll be surprised that we disagree less than you think) on something, the tone of my future conversations with you will be markedly different. You're just trying to do the same thing I am... help people. Thank you for your courtesy and good will.

Jeff, please allow me to throw your words right back at you. Your post displays the same awesome level of professionalism that you are "accusing" me of.

I am very glad that you recognise and appreciate the ulterior motive for my participation in this topic. And let there be no misunderstanding - I am fullly aware that your motives are the same. We both just want what's best for all the people who come to SQL Server Central to get their problems solved, or just to learn. We just disagree on what is best for them - but that will never hold me from sharing some beer with you, should we ever get to meet in person! (As long as you accept that the first one's on me)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #980841
Posted Sunday, September 5, 2010 3:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 6,043, Visits: 8,323
WayneS (9/5/2010)
Hugo Kornelis (9/3/2010)
Please go back to page 4 of this topic. One of my posts on that page includes a very fast algorithm that completely avoids undocumented features. And further down, Jeff even posted an optimized version of that code.

Hugo Kornelis (9/4/2010)
My examples on page 11 in this topic ...


Hugo, I have a small request. Instead of posting the page #, could you post the url of the post (in case you're not aware of this, click the post # at the bottom left of the post and up pops a message box with the complete url to the post).

The reason I ask is that the # of posts to display in a page is configurable. If yours is set to 10 per page, then your post on page 4 is between post 31-40. If yours is set to 20, then your post is between 61-80. Mine is set to 50 per page - which is between 151-200.

I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

For me, page 11 would be for posts 550-600, and we're not that high yet.

Just give it a few days...



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #980842
Posted Sunday, September 5, 2010 7:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Hugo Kornelis (9/5/2010)
I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Not a problem. FYI, you can adjust this at http://www.sqlservercentral.com/Forums/EditForumSettings.aspx
For me, page 11 would be for posts 550-600, and we're not that high yet.

Just give it a few days...

.... I hope not! We just broke 200!.


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 #980875
Posted Sunday, September 5, 2010 7:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:08 PM
Points: 35,362, Visits: 31,899
WayneS (9/5/2010)
Hugo Kornelis (9/5/2010)
I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Not a problem. FYI, you can adjust this at http://www.sqlservercentral.com/Forums/EditForumSettings.aspx
For me, page 11 would be for posts 550-600, and we're not that high yet.

Just give it a few days...

.... I hope not! We're just broke 200!.


Heh... and that's just on the rewrite!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980877
Posted Monday, September 6, 2010 11:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Hugo Kornelis (9/5/2010)
Indeed. The check itself changes the options for the optimizer. The method now even appears to be immune to parallel execution problems, since the parallel streams have to be gathered and re-synched before the row numbers can be calculated.

Sadly I fear it is not so. While it is true that the Sequence Project that calculates row numbers must run serially, there is no guarantee that the optimizer will not choose to restart parallelism after that iterator. If the critical Compute Scalar runs in parallel, bad things will happen.

With the current costing model, this is outrageously unlikely in practice because Compute Scalars are barely costed at all (reference 1; reference 2 - both from Conor). This costing arrangement may well change in SQL11.

Nevertheless, I can produce a plan today where parallelism is restarted for the Compute Scalar by choosing a suitably large CPU multiplier for the costing model. I illustrated the method just a few days ago on my blog: http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx

Hugo Kornelis (9/5/2010)
There may be a way to break this method on current versions of SQL Server, but it takes someone smarter then me to find it. And even if you accidentally stumble over it, or if new versions of the optimizer start to wreck this method, you're still safe because of the builtin safety check.

I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #981196
Posted Monday, September 6, 2010 4:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
Paul White NZ (9/6/2010)

I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul

We can make a small code change which I believe guarantees that that particular quirk is impossible. If we modify one piece of your original code
SET     @AccountRunningTotal = AccountRunningTotal = 
CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal+Amount
ELSE Amount
END,

to be instead
SET     @AccountRunningTotal = AccountRunningTotal = 
CASE
WHEN Sequence = @Sequence+1
THEN CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal+Amount
ELSE Amount
END
ELSE 1/0
END,

then I think that the compound case statement which is the RHS of the assignment has to be evaluated at a single row - allowing the optimiser to split the evaluation of a single scalar expression so that parts of it are evaluated using elements from different rows would quite independently of quirky update render the assignment components of a SET clause so ill-determined as to be useless.
But of course the optimizer is a law unto itself, according to some, so I could be wrong.
edit: had an extra [/quote] in there which jumbled thiongs.


Tom
Post #981258
Posted Monday, September 6, 2010 8:51 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Very nice, Tom! That would indeed be sufficient to avoid the issue.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #981289
« Prev Topic | Next Topic »

Add to briefcase «««1920212223»»»

Permissions Expand / Collapse