Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5
-
Jack Corbett (3/3/2008)
Jeff, Thanks for keeping me up too late again. As it seems I say often, I thought I was an expert then I came to SSC to find I was wrong.Oh and for printing to PDF you can also get PDF995 free an it prints to PDF as well. Sure there's an annoying add, but still not bad.
Or - you pay the 9.95, and *poof* no ads....It's a pretty nice product, considering Adobe wants 30 times that much for its standard edition (and I know you get other things with Acrobat 8, but still....).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Ridiculously late addendum, but Cute-PDF is free and has worked wonderfully for me so far, at least with things that are properly formatted for printing, it chokes a bit on printing webpages.
Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am. -
srienstr (4/1/2008)
Ridiculously late addendum, but Cute-PDF is free and has worked wonderfully for me so far, at least with things that are properly formatted for printing, it chokes a bit on printing webpages.Ridiculously useful addendum, too! 😛 Thanks for the tip, Srienstr!
--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) -
I picked these link from another discussion. Your articles are really good. What is more interesting is the discussion that follows your articles. Amazing. 🙂
-
Thanks, Anirban!
I absolutely agree. Steve Jones recently wrote an article asking the quesiton "Why do you write"? One of the reasons I pointed out was the incredible amount of discussion that occurs after the article. We've got folks that did some performance tests and shared the code... we've got folks that suggest alternatives and shared the code... we've got folks that explained how they used something and, perhaps, the success story that followed. And, we 've got folks that politely (or not) disagree with the article and that spurs on even more discussion.
And, it's all good... makes everyone think and try new things. Even a couple of related (or not) sidebars show up and more discussion occurs.
You've just gotta love all the folks that take the time to get involved in these discussions! I probably learn more from the discussions than what I learn from the orginal article... even if I'm the one that wrote the article! 😀
--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) -
Abosolutely. What I like most about the discussions is that you can't just write 'casual' writing. You have to be absolutely sure about the content before putting it. So we are getting very good stuffs on this forum.
🙂
-
Anirban Paul (5/11/2008)
Abosolutely. What I like most about the discussions is that you can't just write 'casual' writing. You have to be absolutely sure about the content before putting it. So we are getting very good stuffs on this forum.🙂
I agree with you...I posted a solution I thought was better than Jeff's one time...turned out that was precisely what he was explaining in his article on trying to avoid...Doh! :hehe:
Christopher Ford
-
*Wow*
I just found this article and cannot wait to run some tests and eventually try it out in prod. I've only read the first page of comments, so no doubt someone has already checked this code in SQL 2k8 (not running it yet, but...).
It's not often I find "a whole new way" to get from a to b (as fast as possible)... thanks again.
Cursors are useful if you don't know SQL -
Christopher Ford (5/11/2008)
Anirban Paul (5/11/2008)
Abosolutely. What I like most about the discussions is that you can't just write 'casual' writing. You have to be absolutely sure about the content before putting it. So we are getting very good stuffs on this forum.🙂
I agree with you...I posted a solution I thought was better than Jeff's one time...turned out that was precisely what he was explaining in his article on trying to avoid...Doh! :hehe:
Heh... just a reality check... I'm not always right :blush:, but THAT is precisely what makes forums so very powerful... especially this one. Most folks that take the time to challenge something are good, well mannered professionals that seem to ALWAYS look for a better way. In the process, a better way frequently comes to the surface or a darned good reason not to do something a certain way does. Everybody learns through such dialog.
Thanks for the great posts, Christopher. And, I'm not talking about just this one. 🙂
p.s. I'm truly sorry for the late response... just noticed your post was from 5/11. Pretty much delinquent for me.
--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) -
Jeff Moden (1/31/2008)
James Goodwin (1/31/2008)
Did you test using the Clustered Index with the ORDER BY subquery? It seems to me that the ORDER BY Would enforce the order, but since you are using a Clustered Index it wouldn't cause much overhead unless you were going to get bit by a disordered resultset anyway.--
JimFive
No... I didn't test for that and the article got a wee bit long anyway. Was really trying to show that either an Order By (regardless of what the Clustered Index was) would work or that a Clustered Index on the correct columns would work.
Thanks for the feedback and the suggestion, Jim. I'll give it a try a post the results...
Sorry for the very long delay... I ran the "Order By" example from the article without the clustered index... as expected, a "Sort" icon appeared in the Execution Plan. Then, I added the clustered index without changing the code a bit... the "Sort" icon went away. Apparently, SQL Server also knows it doesn't need to sort when the clustered index (ie, the table) goes through a whole table scan as that which the update causes.
By the way... just to make something perfectly clear (we all learn something as time passes by)... the Index Hint used in the article will NOT guarantee the order of a Select... you must use an Order By for that. But, it does guarantee the order in an Update as used. If anyone still has doubts, then go ahead an use the Order By method... with or without the clustered index (which made virtually no difference in speed because of full index scan), it's still faster than a Cursor, a While Loop, or a Triangular Join.
As a sidebar, I reran the indes hint code with only 100 accounts across the million rows to guarantee some pretty nasty page splits... the verification code still shows no errors.
--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) -
mstjean (11/21/2008)
*Wow*I just found this article and cannot wait to run some tests and eventually try it out in prod. I've only read the first page of comments, so no doubt someone has already checked this code in SQL 2k8 (not running it yet, but...).
It's not often I find "a whole new way" to get from a to b (as fast as possible)... thanks again.
Absolutely my pleasure. Thanks for the feedback. If you get a chance, post your "post test" feedback. I've obviously got a vested interest on any testing folks do on this.
Thanks, again.
--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) -
Just used some of the concepts behind running totals to help eliminate RBAR in another post. The concept used here isn't just useable for running totals. It can also be used to capture intermediary results that need to be used in calculations "later" in the update.
Amazing what I have picked up here! Thanks, Jeff!
-
Dang it... I don't know how I missed your latest post on this, Lynn.
Heh... yeah... the concept can be used for a huge number of things. I remember seeing that post and a whole bunch of others where folks used this concept to just blow away WHILE loops of any kind.
Thanks for the feedback, Lynn!
--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) -
Hi.
Firstly I am not trying to dispute the actual solution you are providing. In fact, I came across the phenomenon several years ago (by accident) but never found a use for it because of the ordering issue.....
I am fully aware of the "internal Row" produced by "triangular" joins" and usually include some coding for optimization, and prefer code that limits the number of internal rows.
I find it interesting that this relise on a clustered index. What happens for for tables that the rows are not in a clustered index with the right order.
What I REALLY question though, is that you suggest this solution instead of "triangular joins" as TJ's are "hidden RBAR" when this solution uses exactly that....RBAR. It realise on the Variable being updated "row by agonizing row"
As I said. This is not to dispute the Solution just the Definition.....
-
I agree... this one looks like RBAR because it does something for each row... but so do all of the queries built in SQL Server. The difference here is we're letting the optimizer handle the update of rows using a single query instead of a declared loop or a triangular join.
In fact, some folks call this method a "pseudo-cursor" and that's an appropriate name for it. So it might be RBR... but if you consider the speed, it's definitely not RBAR.
--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)
Viewing 15 posts - 211 through 225 (of 250 total)
You must be logged in to reply to this topic. Login to reply