Solving the Running Total and Ordinal Rank Problems (Rewritten)
-
You have to observe all the rules at once, Hugo. I said in the rules that you can't use a JOIN in conjunction with the "Quirky Update" and an IN constitutes a JOIN.
Telll you what... let's shift gears... if it'll make folks happy, I'll take out everything that's undocumented and leave in only the method and the test proofs. But instead of hammering on me for my poor literary skills, show me some code that follows all of the rules where the "Quirky Update" doesn't do the running total correctly. 😉
--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 (11/10/2009)
Alexander Kuznetsov-291390 (11/10/2009)
Jeff,In my experience, if we need running totals, then we have some unique order of rows to calculate those totals with respect to. Because we know the running totals before we insert a batch, we can insert the whole batch just as easily as we insert a single row - precalculating running totals for every row being inserted is simple, and our constraints definitely support multi-row operations. Does it make sense to you?
The constraints you have in your code certainly support multi-row inserts but, correct me if I'm wrong... the code you use to calculate running totals is still done only one row at a time. Correct?
Correct, but in that particular case I was dealing with an inventory, and I had to make sure that my inventory is never negative. That particular scenario required only one row at a time inserts with immediate verification that the inventory is non-negative.
Looks like I should write up another blog post demonstrating how to add multiple rows at a time...
-
Alexander Kuznetsov-291390 (11/10/2009)
Looks like I should write up another blog post demonstrating how to add multiple rows at a time...That would be very cool especially if it used neither RBAR nor the "Quirky Update". Let's us know what the URL is when you're done. Thanks, Alexander.
--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 (11/10/2009)
You have to observe all the rules at once, Hugo. I said in the rules that you can't use a JOIN in conjunction with the "Quirky Update" and an IN constitutes a JOIN.Telll you what... let's shift gears... if it'll make folks happy, I'll take out everything that's undocumented and leave in only the method and the test proofs. But instead of hammering on me for my poor literary skills, show me some code that follows all of the rules where the "Quirky Update" doesn't do the running total correctly. 😉
Hi Jeff,
That'll be MY poor literary skills, then.:-D
The code I posted was not intended to be proof of breaking the quirky update. This particular comment was only posted to show that the presence of a "Clustered Index Update" operator in the query plan doesn't say anything about the order in which rows will be updated.
At least twice in your article, you seem to imply that the presence of a "Clustered Index Update" operator as the last operator in the execution plan (first reference), or anywhere in the plan (second reference) is a token that indicates that rows will be updated in clustered index update. I wanted to prove that the presence of absence of this operator at any location in the execution plan and the order in which rows are actually updated are completely unrelated. To prove this, I posted a query that did not have this operator as the last one, yet did process rows in clustered index order; and another query that did have this operator as the last one but processed rows in a different order. I think this suffices to prove beyond doubt that there is no relation at all between the presence and location of a Clustered Index Update operator in the plan and the order in which rows are processed.
However, even though it was not my intention to show the lack of reliability of the quirky update, you have now challenged me to do exactly that. So I'll bite 😀
First, I think the code I posted did already follow all the rules. IN with a subquery is indeed often transformed into a join when the query is executed. But IN with a list of constants? I bet you actually do know that this is transformed into a list of OR conditions.
And second, if you add "no IN" to the rules, I still have this gem for you:
DECLARE @Counter INTSELECT @Counter = 0
UPDATE dbo.TransactionDetail
SET @Counter = AccountRunningCount = @Counter + 1
FROM dbo.TransactionDetail WITH (TABLOCKX)
WHERE TransactionDetailID BETWEEN 120000 AND 120010
OPTION (MAXDOP 1)
GO
--===== Select all the rows in order by the clustered index
SELECT *
FROM dbo.TransactionDetail
WHERE TransactionDetailID BETWEEN 120000 AND 120010
ORDER BY AccountID, Date, TransactionDetailID
The amount of rows that the optimizer thinks will match the WHERE clause is so low that it decides that reading the rows through a nonclustered index and updating them in random order is faster than reading the entire clustered index or reading a nonclustered index and sorting before updating the clustered index.
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/ -
Well, I've allowed myself to be distracted into a discussion on what can be deducted from particular operators in the execution plan and whether or not the quirky update can currently be broken. But I really wanted to present you an alternative way, that might reduce the need to build production code on undocumented and unreliable constructions.
First, a plug. I have described the technique I will show in much greater depth in one of my two articles in the great new book SQL Server MVP Deep Dives. A book written by no less than 53 MVPs, all with a passion for SQL and a passion for helping children in need. All the author royalties for this book will go to "War Child" - a great organisation that is dedicated to helping children in war zones (war victims, child soldiers, etc) back to a normal childs life. More information about the book can be found at http://www.sqlservermvpdeepdives.com/[/url].
So even though I'll post the code here, I urge you all to buy the book. If not for helping charity, then for the wealth of other information you'll find in its 59 chapters.
So far for the plug. On to the code 😉
SELECT CURRENT_TIMESTAMP;DECLARE @Results TABLE
(AccountID int NOT NULL,
Date datetime NOT NULL,
TransactionDetailID int NOT NULL,
Amount money NOT NULL,
RunningTotal money NULL,
Rnk int NOT NULL,
PRIMARY KEY (Rnk, AccountID));
INSERT INTO @Results(AccountID, Date, TransactionDetailID, Amount,
RunningTotal, Rnk)
SELECT AccountID, Date, TransactionDetailID, Amount, Amount,
RANK() OVER (PARTITION BY AccountID
ORDER BY Date,
TransactionDetailID)
FROM dbo.TransactionDetail;
DECLARE @Rank int,
@RowCount int;
SET @Rank = 1;
SET @RowCount = 1;
WHILE @RowCount > 0
BEGIN;
SET @Rank = @Rank + 1;
UPDATE nxt
SET RunningTotal = prv.RunningTotal + nxt.Amount
FROM @Results AS nxt
INNER JOIN @Results AS prv
ON prv.AccountID = nxt.AccountID
AND prv.Rnk = @Rank- 1
WHERE nxt.Rnk = @Rank;
SET @RowCount = @@ROWCOUNT;
END;
SELECT CURRENT_TIMESTAMP;
UPDATE td
SET AccountRunningTotal = r.RunningTotal,
AccountRunningCount = r.Rnk
FROM dbo.TransactionDetail AS td
INNER JOIN @Results AS r
ON r.TransactionDetailID = td.TransactionDetailID;
SELECT CURRENT_TIMESTAMP;
When executed on my computer, the entire code takes 76.8 seconds. The first 28.3 seconds are for calculating the running totals and running counts; the remaining 48.5 seconds for copying the results back from the working table (@Results) into the original table. I added this step only because I couldn't use EXEC dbo.Verify to check the correctness otherwise.
It is my experience that running totals are often only needed for reports, or calculated and stored in a snapshot copy of the data. Storing them in the actual table, as your code does, is far less common (and in fact bad practice - the running totals will not be updated every time the base data changes, so you are mixing current and stale data in a single table which can be very confusing). That's why I wrote the code to calculate the running totals in a table variable (which of course could also have been a temp table or a permanent table in a reporting database).
Your quirky update code (figure 19 in the article) takes 8.3 seconds on the same machine and the same data. So indeed, my code is quite a bit slower than your code (2.5 times slower, to be exact). And it only takes half as long as the Quirky Update + Verification method you mention in the article's conclusion.
If I had to choose between an 8-minute (or even 4-minute, see my previous post) fully documented cursorbased solution or a 8-second undocumented method, then -depending on the situation, especially the importance of cutting edge performance in the specific circumstances- I might be tempted to take the risk and use the undocumented method.
But with a 30-second fully documented alternative at hand, shaving off those final seconds running time no longer seem worth the price of relying on undocumented features.
Remember - my main gripe with using this method is not that I was able to find some weird cases where it breaks, nor that you have to live by a very limiting set of rules before being able to use it.
My main gripe is that it can change any day. A new version, or a new servicepack, or even a security update, a hotfix, or the position of the moons might cause a sudden change in behaviour. And if there is lots of code in production that uses this trick, then I hope I am not the one on pager duty at the time that happens!
I can understand that people use this method to drop from 8 minutes to less than 10 seconds. But I do hope that, now that I have presented a 30-second alternative that IS completely reliable, people will place all code using this method where it belongs - in the bin.
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 (11/10/2009)
The code I posted was not intended to be proof of breaking the quirky update. This particular comment was only posted to show that the presence of a "Clustered Index Update" operator in the query plan doesn't say anything about the order in which rows will be updated.You know what? You're right... I'll remove that speculation on my part from the article. Thanks, Hugo.
--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) -
Hugo Kornelis (11/10/2009)
I can understand that people use this method to drop from 8 minutes to less than 10 seconds. But I do hope that, now that I have presented a 30-second alternative that IS completely reliable, people will place all code using this method where it belongs - in the bin.First... good plug for a great book and a great cause. And thanks for posting the code. Even though I think you're a bit harsh on something that you admit hasn't been broken yet ("yet" is the big brother to "it depends" ;-)), it's good to see another alternative other than a cursor. I'll check it out. Thanks.
So far as undocumented features go, let's include fully documented features in the "bin" of things that can change virtually without warning. Think about what they did with the very well documented xp_MakeWebTask extended stored procedure in 2k when they came out with sp4... they suddenly changed it to require SA privs... that broke a lot of people's code on, like I said, a very well documented feature. Was there a work around? Sure... just like there's a work around on the "Quirky Update" if it ever breaks.
Again, thanks for posting the code... hopefully it will also inspire folks to buy the book to help a good cause.
--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) -
All I can say is that this is a fantastic article with a great discussion of the different techniques and an explanation to why one is superior.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/ -
Hugo Kornelis (11/10/2009)
I then changed the options from LOCAL FORWARD_ONLY READ_ONLY to LOCAL STATIC READ_ONLY and shaved off another half a mintue - 3:58Hi Hugo,
Heh... just some feedback so you know... I think your machine hates cursors worse than I do 😛 Your optimized cursor only took 2:13 (~133 seconds) on my machine. You've gotten it down to only 18 times worse and that's not a slam... well done.
As a side bar, I was never good at writing cursors and I guess it's a good thing I didn't take them up. :hehe:
Back to the subject...
I added some "timer" code to your "Ninja" code for the book so we don't have to do any manual calculations to determine the run time for each section. Here's the code with all changes clearly marked...
DECLARE @RunStartTime DATETIME, --Added@SectionStartTime DATETIME; --Added
SELECT @RunStartTime = CURRENT_TIMESTAMP, --Added
@SectionStartTime = CURRENT_TIMESTAMP; --Added
--SELECT CURRENT_TIMESTAMP; --Removed
DECLARE @Results TABLE
(AccountID int NOT NULL,
Date datetime NOT NULL,
TransactionDetailID int NOT NULL,
Amount money NOT NULL,
RunningTotal money NULL,
Rnk int NOT NULL,
PRIMARY KEY (Rnk, AccountID));
INSERT INTO @Results(AccountID, Date, TransactionDetailID, Amount,
RunningTotal, Rnk)
SELECT AccountID, Date, TransactionDetailID, Amount, Amount,
RANK() OVER (PARTITION BY AccountID
ORDER BY Date,
TransactionDetailID)
FROM dbo.TransactionDetail;
SELECT STR(DATEDIFF(ss,@SectionStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds to INSERT';
SET @SectionStartTime = CURRENT_TIMESTAMP; --Modified
DECLARE @Rank int,
@RowCount int;
SET @Rank = 1;
SET @RowCount = 1;
WHILE @RowCount > 0
BEGIN;
SET @Rank = @Rank + 1;
UPDATE nxt
SET RunningTotal = prv.RunningTotal + nxt.Amount
FROM @Results AS nxt
INNER JOIN @Results AS prv
ON prv.AccountID = nxt.AccountID
AND prv.Rnk = @Rank- 1
WHERE nxt.Rnk = @Rank;
SET @RowCount = @@ROWCOUNT;
END;
SELECT STR(DATEDIFF(ss,@SectionStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds for Running Total UPDATE';
SET @SectionStartTime = CURRENT_TIMESTAMP; --Modified
UPDATE td
SET AccountRunningTotal = r.RunningTotal,
AccountRunningCount = r.Rnk
FROM dbo.TransactionDetail AS td
INNER JOIN @Results AS r
ON r.TransactionDetailID = td.TransactionDetailID;
SELECT STR(DATEDIFF(ss,@SectionStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds for Final UPDATE';
SELECT STR(DATEDIFF(ss,@RunStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds for Total Run';
On my machine, your good code took the following times...
16 Seconds to INSERT
29 Seconds for Running Total UPDATE
51 Seconds for Final UPDATE
----
96 Seconds for Total Run
That's incredible. Folks now have an alternative not only to the slothfulness of a cursor, but it's fast enough (well, for some folks) to make the future guaranteed stability of the method worth the wait.
Heh... of course, I have to tweek on that code a bit :-D. The first thing I noticed was that there were a bunch of row counts being output. Rumor has it that that costs extra time even if they never make it to the screen. I added a SET NOCOUNT ON to your good code and it chopped about 1 second off the run...
16 Seconds to INSERT
29 Seconds for Running Total UPDATE
50 Seconds for Final UPDATE
----
95 Seconds for Total Run
Surprisingly, the time savings didn't occur on the Running Total UPDATE where all the row counts are generated, so I ran it a couple of times again and the times (on my very quiet test machine) varied from 95 to 97 seconds so it doesn't look like the SET NOCOUNT ON actually helped much here, probably due to the fact that there were only 20,555 lines printed in the message window and half of them were empty string rows.
The 2 UPDATE sections of the code are pretty tight so I couldn't do much there. But, that initial insert and that Table Variable needed a change...
SET NOCOUNT ONDECLARE @RunStartTime DATETIME, --Added
@SectionStartTime DATETIME; --Added
SELECT @RunStartTime = CURRENT_TIMESTAMP, --Added
@SectionStartTime = CURRENT_TIMESTAMP; --Added
--SELECT CURRENT_TIMESTAMP; --Removed
--DECLARE @Results TABLE
-- (AccountID int NOT NULL,
-- Date datetime NOT NULL,
-- TransactionDetailID int NOT NULL,
-- Amount money NOT NULL,
-- RunningTotal money NULL,
-- Rnk int NOT NULL,
-- PRIMARY KEY (Rnk, AccountID));
--
--INSERT INTO @Results(AccountID, Date, TransactionDetailID, Amount,
-- RunningTotal, Rnk)
--SELECT AccountID, Date, TransactionDetailID, Amount, Amount,
-- RANK() OVER (PARTITION BY AccountID
-- ORDER BY Date,
-- TransactionDetailID)
--FROM dbo.TransactionDetail;
--===== Replaced Create Table/Insert/Select from above
SELECT ISNULL(AccountID,0) AS AccountID, --ISNULL makes a NOT NULL column
Date,
ISNULL(TransactionDetailID,0) AS TransactionDetailID, --ISNULL makes a NOT NULL column
--and strips IDENTITY off
Amount,
Amount AS RunningTotal,
ISNULL(RANK() OVER (PARTITION BY AccountID --ISNULL makes a NOT NULL column
ORDER BY Date,
TransactionDetailID),
0) AS Rnk
INTO #Results
FROM dbo.TransactionDetail;
ALTER TABLE #Results --Added to replace the PK we didn't make during the table build.
ADD PRIMARY KEY (Rnk, AccountID);
SELECT STR(DATEDIFF(ss,@SectionStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds to INSERT';
SET @SectionStartTime = CURRENT_TIMESTAMP; --Modified
DECLARE @Rank int,
@RowCount int;
SET @Rank = 1;
SET @RowCount = 1;
WHILE @RowCount > 0
BEGIN;
SET @Rank = @Rank + 1;
UPDATE nxt
SET RunningTotal = prv.RunningTotal + nxt.Amount
FROM #Results AS nxt --Changed to temp table
INNER JOIN #Results AS prv --Changed to temp table
ON prv.AccountID = nxt.AccountID
AND prv.Rnk = @Rank- 1
WHERE nxt.Rnk = @Rank;
SET @RowCount = @@ROWCOUNT;
END;
SELECT STR(DATEDIFF(ss,@SectionStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds for Running Total UPDATE';
SET @SectionStartTime = CURRENT_TIMESTAMP; --Modified
UPDATE td
SET AccountRunningTotal = r.RunningTotal,
AccountRunningCount = r.Rnk
FROM dbo.TransactionDetail AS td
INNER JOIN #Results AS r --Changed to temp table
ON r.TransactionDetailID = td.TransactionDetailID;
SELECT STR(DATEDIFF(ss,@SectionStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds for Final UPDATE';
SELECT STR(DATEDIFF(ss,@RunStartTime,CURRENT_TIMESTAMP),4) --Added
+ ' Seconds for Total Run';
Take a look at those times, Hugo...
9 Seconds to INSERT
27 Seconds for Running Total UPDATE
28 Seconds for Final UPDATE
----
64 Seconds for Total Run
NOW we're cooking with gas! Just a bit over a minute to do a million row grouped running total and grouped running count and it was done without the "Quirky Update" and it beat the heck out of the best cursor anyone could hope to write. And, it's only 8 times slower than the "Quirky Update" on my 7 year old desktop. On a real server, it should fly! This is getting interesting...
Hmmmm... I wonder how it treats IO... let's see what profiler says about the "Quirky Update" vs the tweeked Hugo code...
Ack... almost 1,000 times more IO than the "Quirky Update" even if some of it is memory based. Despite the fact that some folks are concerned about MS doing something to the core code of SQL Server (which I don't think to be likely, too expensive), I'll bank on the fact that the "Quirky Update" worked on SyBase (the original source of SQL Server) and SQL Server versions 6.5, 7, 2000, 2005, and 2008 and has made it through all the service packs and other updates for those major versions. Everything prior to 2008 is pretty much locked down so I don't believe we have to worry about them breaking that code. And, if they manage to break some of the core code (UPDATE) in 2008 or further in the future, it's great that, thanks to you, we have a more performant alternative than using a RBAR cursor to solve the running total problem.
For those that have been playing along with Hugo and I, every run except the profiler runs have gone through the "Verify" code just to be sure that nothing was broken along the way.
--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) -
Lynn Pettis (11/10/2009)
I have found the "quirky update" useful when helping others who needed to capture and update intermediate results along with the final results of a calculation.It helps break down the complexity of some problems.
Thanks for the feedback, ol' friend... several of us have used the "Quirky Update" to solve some rather remarkable problems together.
--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) -
Jacob Bennett (11/10/2009)
Great article Jeff!Forget about the undocumented argument for a minute... This is a wonderfully creative solution that gets around the RBAR mess and doesn't leave that bad after-taste I tend to get after I use a cursor 😛 .
I am interested in how the recursive approach holds up.
Hi Jacob! Heh... Man, I wish everyone would forget about the undocumented argument for a minute. :hehe: They've slammed it in articles, they slammed it in the "24 hours of PASS", they slam it when I post it as a solution, and they slam it in forums (heh, and sometimes me by name) even when I'm no where around. They still haven't broken it, though. 🙂
I'm tickled that Hugo came up with such a neat and fairly performant alternative to cursor code so that folks that are uneasy with the "Quirky Update" now have another alternative other than a cursor but it did take a bit of time to test Hugo's solution. I'll try to get to the recursive CTE approach tommorrow night.
Thanks again for the great feedback, Jacob.
--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) -
lmu92 (11/10/2009)
Thanxalot for the effort in (re-)writing this great article Jeff!Adding cross-references to several other great articles and the amount of code to support/prove your point makes it not "only" an article but a "need to know when arguing Quirky Updates" - regardless if for or against it.
Thanks, Lutz... I have a couple of boo-boo's that I need to fix especially after this discussion, but I appreciate the heck out of folks like you taking the time to recognize the effort. Heh... between Hugo and Alexander's good comments, it would appear that effort isn't over yet. The really great part about this is that they too are putting some great effort into it. They've both coughed up some pretty sharp code to be used for different reasons.
BWAA-HAAA!!!! This discussion on this article reminds me of the old TV commercial... "It's Shake'n'Bake and I helped!" 😛 And I really appreciate everyone's input even if it's from the Con side of the house.
--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) -
Sorry... accidently double posted. Took the double out of this frame...
--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) -
timothyawiseman (11/10/2009)
All I can say is that this is a fantastic article with a great discussion of the different techniques and an explanation to why one is superior.Thanks for the awesome compliment, Timothy. I have to agree... the discussion on this rewrite has surpassed even my expectations. Hugo and Alexander have provided some great alternatives and one good fellow even threw in a recursive CTE example that I'm interested in testing.
--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) -
Table variables are bad when comes to parallellism. It seems that parallellism is not used at all when modifying (insert, update or delete) the content of a table variable in any kind. However, using a table variable in a JOIN makes it possible to use parallellism.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 31 through 45 (of 307 total)
You must be logged in to reply to this topic. Login to reply