Solving the Running Total and Ordinal Rank Problems (Rewritten)

Question

Solving the Running Total and Ordinal Rank Problems (Rewritten)

Viewing 15 posts - 196 through 210 (of 307 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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. For me, page 11 would be for posts 550-600, and we're not that high yet.

Thanks!

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

Wayne,

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

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 3

Thanks Paul.

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

WayneS SSC Guru Points: 95460 More actions · Answer 7

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!. :w00t:

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

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!. :w00t:

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 9

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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

Paul White SSC Guru Points: 150467 More actions · Answer 11

Very nice, Tom! That would indeed be sufficient to avoid the issue. 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 12

Paul White NZ (9/6/2010)
Very nice, Tom! That would indeed be sufficient to avoid the issue. 😎

Hey guys - don't take this wrong. But I just love it when someone says to a guru "hey, what about this?" and the guru does a "OMG - you're absolutely right" type of thing. Just another example of not seeing the forest for the trees, even for the guru (who might have seen most of it, but missed something). Plus, I also love how the community works together to make things better.

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

Paul White SSC Guru Points: 150467 More actions · Answer 13

Paul White NZ (9/3/2010)
...my strong preference is for INDEX(1) over INDEX(0) for performance reasons: QO may choose to introduce an explicit sort with INDEX(0) since the TABLOCK hint allows for an IAM-ordered scan.

I need to correct myself here - or at least clarify - INDEX(1) does not guarantee order in the case of scans (it does for seeks of course).

Any index scan can be performed in allocation order (rather than index order) if TABLOCK is specified, or if the effective isolation level is READ UNCOMMITTED.

There is an optimizer quirk that means that some plans using INDEX(0) may include an unnecessary sort (which does not apply to INDEX(1)). I will be blogging the details for next week's T-SQL Tuesday (the topic is indexes).

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mjswart SSC Enthusiast Points: 169 More actions · Answer 14

Hi Jeff,

Records aren't always stored physically ordered on a data page. Most of the time they are physically ordered correctly, but the actual order comes from the slot array. I was curious to see whether a different physical order breaks the quirky update.

Turns out it does not break the quirky update:

-- create the table

USE tempdb

GO

CREATE TABLE test

(

id int not null,

value uniqueidentifier,

deltaFromPrevious int,

constraint pk_test primary key clustered (id)

)

INSERT test (id,value)

VALUES

(2,NEWID()),

(4,NEWID()),

(6,NEWID()),

(8,NEWID()),

(10,NEWID())

GO

-- run quirky update

DECLARE @dfp int, @prev int;

UPDATE test

SET @dfp = deltaFromPrevious = id-@prev,

@prev = id;

-- everything looks fine here.

select * from test

-- mess up the physical order:

UPDATE test SET id = 5 WHERE id = 10;

GO

-- dbcc page now tells us that physically the

-- rows are out of order.

-- run the quirky update again:

DECLARE @dfp int, @prev int;

UPDATE test

SET @dfp = deltaFromPrevious = id-@prev,

@prev = id;

-- everything still looks fine here:

select * from test

Paul White SSC Guru Points: 150467 More actions · Answer 15

Paul White NZ (9/7/2010)
There is an optimizer quirk that means that some plans using INDEX(0) may include an unnecessary sort (which does not apply to INDEX(1)). I will be blogging the details for next week's T-SQL Tuesday (the topic is indexes).

Yeah well I missed T-SQL Tuesday by some margin, but it's up now:

http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi