Solving the Running Total and Ordinal Rank Problems (Rewritten)

Question

Solving the Running Total and Ordinal Rank Problems (Rewritten)

Viewing 15 posts - 271 through 285 (of 307 total)

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

charles.southey SSC Enthusiast Points: 178 More actions · Answer 1

This is a good example of the hoops you sometimes have to go through to carry out some conceptually quite simple tasks in basic T-SQL. For a much simpler approach to this problem (and many others) you might want to check out our T-SQL function libraries: http://www.totallysql.com/products/sqlrollingstats.

Charles Southey
www.totallysql.com

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

charles.southey (1/27/2012)
This is a good example of the hoops you sometimes have to go through to carry out some conceptually quite simple tasks in basic T-SQL. For a much simpler approach to this problem (and many others) you might want to check out our T-SQL function libraries: http://www.totallysql.com/products/sqlrollingstats.

It's a shame you decided to release this as a commercial product (with ambitious pricing!), rather than as open source on a site like CodePlex. The choice to use SQLCLR UDTs is an interesting one; I have not found these to perform very well in general. It is concerning that you use shared state in a core UNSAFE assembly; it seems unlikely you are able to clean up successfully in all situations e.g. after a 'rude abort'. Nice idea in some ways, pity about the commercialism.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

PBaekdal Grasshopper Points: 11 More actions · Answer 3

Nice and enlightening ... on a par with your original tally table article.

Cheers,

Pete.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

PBaekdal (1/29/2012)
Nice and enlightening ... on a par with your original tally table article.
Cheers,
Pete.

Thanks Pete. I really appreciate the feedback.

--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)

GrumpyDBA SSC-Addicted Points: 412 More actions · Answer 5

Great article, and an excellent technique I've been utilising (with care!) for a while.

I do however raise an argument against your statement that "The "Quirky Update" will work just fine with all 2 part SET statements".

Try making the Update @OMG statement into this, using only 2 part statements, and see what you get 🙂

UPDATE @OMG

SET

@N = @N + 1,

SomeInt = @N + 1

FROM @OMG

OPTION (MAXDOP 1)

Again we see that it works fine for the first row, but then fails after that. As mentioned elsewhere, it is more a factor of updating the variable twice rather than it being in 2 or 3 part statements.

Tim

[font="Verdana"]Of course I'm grumpy, I'm a DBA.[/font]
The Grumpy DBA[/url][/size]

gcresse SSCrazy Points: 2286 More actions · Answer 6

I just read this article about SQL Server 2012's new features (http://blog.tallan.com/2011/12/08/sql-server-2012-windowing-functions-part-1-of-2-running-and-sliding-aggregates/) and I'm thinking it *might* solve my problem, as long as I can join to other tables. I had to give up on the Quirky Update because I had to try to jam every column I needed into the same row and in some cases the row was too wide for SQL Server to handle.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

gcresse (2/13/2012)
I just read this article about SQL Server 2012's new features (http://blog.tallan.com/2011/12/08/sql-server-2012-windowing-functions-part-1-of-2-running-and-sliding-aggregates/) and I'm thinking it *might* solve my problem, as long as I can join to other tables. I had to give up on the Quirky Update because I had to try to jam every column I needed into the same row and in some cases the row was too wide for SQL Server to handle.

whilst you wait for 2012.....could you consider creating a temp table from all of your sub queries / joins etc....and then running QU on the temp table?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

gcresse SSCrazy Points: 2286 More actions · Answer 8

The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

gcresse (2/13/2012)
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.

ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"

anyway good luck.

kind regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

J Livingston SQL (2/13/2012)
gcresse (2/13/2012)
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.
ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"
anyway good luck.
kind regards

I agree... think rows instead of columns here even if you don't use the QU.

As a sidebar, any process that has thousands of steps should probably be reevaluated a bit. 😉

As another sidebar, can you move the discussion about such a wide table to a new thread, please? Thanks.

--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)

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 11

Jeff - on behalf of my client 😛

see http://www.sqlservercentral.com/Forums/Topic1550386-392-1.aspx

I took a stored procedure, modified (but not enough) which had used cursors in Oracle that ran for 88 minutes 2 nights ago

last night it ran in 4 minutes

you are so right RBAR is truly evil

autoexcrement SSCertifiable Points: 5956 More actions · Answer 12

Has anyone done any significant testing of the Quirky Update method in SQL Server 2008, 2012, or 2014? Does it work in those versions too?

It's easy enough to test out a few million rows myself, but I trust the gurus here a lot more than I trust myself to make any authoritative determination on this.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

WayneS SSC Guru Points: 95460 More actions · Answer 13

autoexcrement (5/28/2014)
Has anyone done any significant testing of the Quirky Update method in SQL Server 2008, 2012, or 2014? Does it work in those versions too?
It's easy enough to test out a few million rows myself, but I trust the gurus here a lot more than I trust myself to make any authoritative determination on this.

http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/[/url]

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

autoexcrement SSCertifiable Points: 5956 More actions · Answer 14

Thanks, looks like QU is still a winner even in 2012. 🙂

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

corey lawson Hall of Fame Points: 3773 More actions · Answer 15

I came across the Quirky Update initially just last week in one of Phil Factor's "Speed Phreak" challenges, where in the lengthy commentary regarding posted candidate solutions, someone did compare it against the upgraded partition functions in SQL Server 2012, and it's still faster. I wasn't looking for it in particular, but saw "quirky update", and had to find out more!

It's hard to call this undocumented with all its "the evil" connotations people give this, when the syntax for this use of SET is right there in BOL (but not really explained well/in depth). This is a vestige from the mother code from Sybase, where it's probably been a standard, known speed-up for quite some time...

(yes, the syntax for it still in BOL for the UPDATE statement for SQL Server 2014, but no examples)