June 21, 2009 at 4:28 pm
Paul White (6/21/2009)
'lmu92' sent me a few PMs about it!Paul
@paul-2: The PM's I sent you just made one thing clear to me: I'm addicted to SSC. I stared at the screen, clicking the mouse like I would have to send todays news via Morse Signal, banging the keyboard and almost started to cry.
Ok, taking the first step: Hello, my name is Lutz and I'm Threadicted.
June 21, 2009 at 4:31 pm
Just noticed how many posts on The Thread so far. Pretty sure we will be having our 6000th post party before the end of the week!
June 21, 2009 at 4:58 pm
Jeff Moden (6/21/2009)
Do you have a link to the post for the "holding variable"? I want to see what you're talking about before I respond...
I was just continuing on about the "Cursors Be Gone" thread we were discussing yesterday - it's not a new post. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 21, 2009 at 5:07 pm
lmu92 (6/21/2009)
Ok, taking the first step: Hello, my name is Lutz and I'm Threadicted.
Funny! 😀 :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 21, 2009 at 8:03 pm
Paul White (6/21/2009)
Jeff Moden (6/21/2009)
Do you have a link to the post for the "holding variable"? I want to see what you're talking about before I respond...I was just continuing on about the "Cursors Be Gone" thread we were discussing yesterday - it's not a new post. 🙂
Heh... it's a long thread and I thought you might be able to provide the link to the specific response. No problem... I'll find it.
--Jeff Moden
June 21, 2009 at 8:08 pm
Jeff Moden (6/21/2009)
Paul White (6/21/2009)
Jeff Moden (6/21/2009)
Do you have a link to the post for the "holding variable"? I want to see what you're talking about before I respond...I was just continuing on about the "Cursors Be Gone" thread we were discussing yesterday - it's not a new post. 🙂
Heh... it's a long thread and I thought you might be able to provide the link to the specific response. No problem... I'll find it.
Sorry... couldn't find the world "holding" anywhere in the entire thread.
--Jeff Moden
June 21, 2009 at 8:39 pm
Jeff Moden (6/21/2009)
Sorry... couldn't find the world "holding" anywhere in the entire thread.
Clearly I am not having one of my more lucid days. Let me try again - after I apologise for the confusion.
(Sorry) <-- there we go 🙂
So, the 'holding variable' was my own expression for referring to the 'Quirky Update' or 'Pseudo-Cursor'.
I wasn't referring to any particular post* - I was trying to say that this method is as set-based as pretty much any other.
The odd cursor-zealot says the 'pseudo-cursor' is not really all that set-based at all (as I know you know they do).
I have never heard the cursor lobby say that any plan with a loop join is a cursor (as opposed to hash or merge**) - so why single out the holding variable/quirky/pseudo method as being less-than-completely-set based?
Even for those who insist that set-based means a single statement, the method qualifies - the running totals are all written by a single UPDATE statement.
Hope that helps. I hadn't realised how much of the context for my statement has occurred entirely inside my own head 🙂
Paul
* Though one of jacroberts' posts came close (emphasis is mine):
Jeff, That's a neat way of doing it. I hadn't read up on setting working variables through update statement process so that's new to me. In fact combined with a CASE statement in the sql it's almost like using a cursor, it could be argued that this isn't set based logic, though I've no doubt it runs fast which is the aim of this excercise!
** Actually I'm not so sure that this is helpful. All operations are ultimately per-row, but I can sort of see how someone might see a loop join as more like a cursor than a hash join. A merge join would be somewhere between the two I guess. Ever wish you had never started out on a line of thinking?!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 21, 2009 at 10:41 pm
Paul White (6/21/2009)
Jeff Moden (6/20/2009)
Oh yeah... now I remember. One column needed to be split (a portion isolated, really) from the middle.Fascinating thread. I just spent the last couple of hours reading every post from the discussion of "Cursors Be Gone!" - very entertaining.
(above emphasis mine)... I found it extremely educational... simple code that is blazing fast. And people completely convinced that because they could only come up with a cursor-based solution, that a set-based solution isn't available. I understand egos and vanity, but jeez, come on. Try asking the gurus here for help.
I have spent far too much time on this site this weekend 🙂 It's all good.
I know what you mean. But I think Jeff lives here... every time that I come on (up late at 1:30, up early at 5), he's nearly always on.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 22, 2009 at 3:10 am
WayneS (6/21/2009)
(above emphasis mine)... I found it extremely educational... simple code that is blazing fast. And people completely convinced that because they could only come up with a cursor-based solution, that a set-based solution isn't available. I understand egos and vanity, but jeez, come on. Try asking the gurus here for help.
Absolutely. That was the primary source of the 'entertainment' I referred to. Over the years I have had various debates with people convinced that a cursor was the only (or fastest) solution to a given problem - not to the same extent as Jeff, but still. Some of those included the seemingly endless debate over whether Jeff's super-fast running total solution is kosher or not. I never get bored with that one - though I did give myself a scare last week with the trigger...happily I am over that now 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 22, 2009 at 3:11 am
Did anyone feel the disturbance in the Force when the name Celko was invoked earlier?
Apparently triangular joins are fantastic for running-total or allocation problems!
/sarcasm
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 22, 2009 at 3:32 am
Paul White (6/21/2009)
** Actually I'm not so sure that this is helpful. All operations are ultimately per-row, but I can sort of see how someone might see a loop join as more like a cursor than a hash join. A merge join would be somewhere between the two I guess. Ever wish you had never started out on a line of thinking?!
Exactly... once you realize that SQL Server is nothing more than a file server on steriods, you're all set. Most people don't realize that the following code really does do RBAR to the max behind the scenes...
SELECT somecol
FROM sometable
... except that it allows the optimizer to pick which looping routine is the best for now. 😛
--Jeff Moden
June 22, 2009 at 3:45 am
... RBAR to the max behind the scenes ...
Well RBAR is better than FUBAR 😛
Far away is close at hand in the images of elsewhere.
Anon.
June 22, 2009 at 4:03 am
Paul White (6/22/2009)
Did anyone feel the disturbance in the Force when the name Celko was invoked earlier?Apparently triangular joins are fantastic for running-total or allocation problems!
/sarcasm
There's going to be a huge disturbance in the Force tonight when I get back to that post. Carl posted code for the problem and is proud of the fact that 80,000 rows were resolved in 13 seconds on a freakin' 16 processor monster server and 19 seconds on his laptop. His first chunk of code is the creation of a 32K row Tally table that's created using a While Loop... and get this, he creates it in the Master database. The first high velocity pork chop in a 32 course meal has just been fired about that.
Does anyone have some apple-sauce? 😛
--Jeff Moden
June 22, 2009 at 4:20 am
Jeff Moden (6/22/2009)
... The first high velocity pork chop in a 32 course meal has just been fired about that. ...
I was on my way to provide an "alternative" using triangular join together with the execution plans for both options...
Even with the few lines to deal with the triangular join is already 20% behind the Non-RBAR version.
Since I don't want to be blamed for advertising triangular joins I kinda refuse to post it though...
I just did it for private CYA - just in case the discussion would heat up. 🙂
But since Jeff is already involved I'll step back a few lines - and enjoy the pork-chop fireworks.
June 22, 2009 at 4:23 am
Jeff Moden (6/22/2009)
There's going to be a huge disturbance in the Force tonight when I get back to that post. Carl posted code for the problem and is proud of the fact that 80,000 rows were resolved in 13 seconds on a freakin' 16 processor monster server and 19 seconds on his laptop. His first chunk of code is the creation of a 32K row Tally table that's created using a While Loop... and get this, he creates it in the Master database. The first high velocity pork chop in a 32 course meal has just been fired about that.Does anyone have some apple-sauce? 😛
*passes the jar*
I ran the code (after moving the Tally table from master!) and was less than impressed. For some reason, I just didn't fancy a Celko debate tonight, and the code is a pig to follow (no comments!), so I left it and contented myself with complementing Lutz on his solution.
A more challenging puzzle might be to come up with a way of doing this on 80K rows that takes more than 13 seconds on that monster server 😉
Nah. Too easy. I've half-written the dynamic cursor in my head already. 😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 5,926 through 5,940 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply