June 4, 2009 at 3:29 pm
Jeff Moden (6/3/2009)
The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times. After that, the graphic I captured from that code running on Profiler on my poor old 7 year old P4 1.8GHz 1GB Ram desktop computer running 2k5 DE sp2. As usual, the details are in the embedded comments in the code.
FYI Jeff: I posted a link to this post on the feedback for the article at sqlmag.com.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 4:37 pm
WayneS (6/4/2009)
Hey friends, please help me out here. I'd really like to have an understanding of why this changed.Thanks!
Helped. I think. 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 4, 2009 at 5:42 pm
Adam Machanic (6/4/2009)
With regard to the UPDATE, something you might want to play with instead of relying on the hints is using a CTE and forcing the data to be ordered. This will not actually cause a sort to occur unless you don't have a supporting index. And I did test to make sure that if you do not have a supporting index, a sort WILL in fact occur. This should give you a better guarantee of ordered processing than the INDEX and TABLOCKX hints.
;WITH p
AS
(
SELECT TOP(2147483647)
*
FROM #pcWork
ORDER BY
AccountNumber,
TransactionDate,
CheckBookId
)
UPDATE p
SET @PrevBalance = AccountBalance = CASE
WHEN AccountNumber = @PrevAccount
THEN @PrevBalance + Amount
ELSE Amount
END,
@PrevAccount = AccountNumber
OPTION (MAXDOP 1)
Ok, I tried the CTE above. You may have made the same mistake I did once and forgot to drop the clustered index during testing. UPDATE on a single table and following all the other rules will always do the update in the same order as the Clustered Index even in the presence of a contrary ORDER BY. The CTE above does the same thing as an "ordered" derived table... it updates in order by the Clustered Index despite the presence of the ORDER BY.
Further, the addition of other indexes doesn't matter and even if you force the scan of a non-clustered index, you can suffer from what is known as a "merry-go-round" index where the index has a "fold" in it.
However, I don't expect anyone to ever just take my word for it. Only the code knows the truth. With that thought in mind, run the cursor code in my previous code posting (because everyone trusts that does things correctly, not because I'll ever use it for anything else :-P) and then run your code as follows. Note that the Clustered Index has been changed on the target Temp Table to exemplify what I'm talking about. The details are in the embedded comments in the code.
[font="Courier New"]--===== Create the necessary Temp table to store the result set so we can use
-- the result set as a table if we need to. The Psuedo-Cursor run will
-- build and populate it on the fly.
-- The condition drop is just for reruns. It wouldn't be needed
-- in production but it sure helps troubleshooting.
IF OBJECT_ID('TempDB..#amWork','U') IS NOT NULL
DROP TABLE #amWork
--===== Create the result set table using the "zero row" trick
SELECT CAST(CheckBookID AS INT) AS CheckBookID, --Strips off IDENTITY
AccountNumber,
Amount,
TransactionDate,
CAST(0 AS DECIMAL(9,2)) AS AccountBalance --Creates new column
INTO #amWork
FROM dbo.CheckBook
--===== Add a clustered index to the #psWork which is contrary to the desired update order.
-- This is also the key to the Pseudo-Cursor or "quirky" update
-- method for doing running totals.
CREATE CLUSTERED INDEX IX_#amWork_CheckBookID
ON #amWork (CheckBookID)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX IX_amWork_AccountNumber_TransactionDate_CheckBookID
ON #amWork (AccountNumber, TransactionDate, CheckBookID)
WITH FILLFACTOR = 100
--===== Declare local variables for the run
DECLARE @PrevBalance DECIMAL(9,2),
@PrevAccount INT
--===== Preset the necessary variables
SELECT @PrevBalance = 0,
@PrevAccount = 0
;WITH p
AS
(
SELECT TOP(2147483647)
*
FROM #amWork
ORDER BY
AccountNumber,
TransactionDate,
CheckBookId
)
UPDATE p
SET @PrevBalance = AccountBalance = CASE
WHEN AccountNumber = @PrevAccount
THEN @PrevBalance + Amount
ELSE Amount
END,
@PrevAccount = AccountNumber
OPTION (MAXDOP 1)
GO
--===== Prove that the ordered update didn't work correctly
-- because UPDATE will always update in the same order
-- as the clustered index. This one is in the correct
-- order for the cursor.
SELECT c.*,am.*
FROM #cWork c
INNER JOIN #amWork am
ON c.CheckBookID = am.CheckBookID
AND c.AccountBalance <> am.AccountBalance
AND c.AccountNumber <= 2
ORDER BY c.AccountNumber, c.TransactionDate, c.CheckBookID
--===== Prove that the ordered update didn't work correctly
-- because UPDATE will always update in the same order
-- as the clustered index. This one is in the correct
-- order for the Clustered Index.
SELECT c.*,am.*
FROM #cWork c
INNER JOIN #amWork am
ON c.CheckBookID = am.CheckBookID
AND c.AccountBalance <> am.AccountBalance
AND c.AccountNumber <= 2
ORDER BY am.CheckBookID[/font]
The first output is in the same order as the cursor which is known to be correct. The output from the ordered CTE is not correct.
The second ouput is identical to the first except that it is sorted in the same order as the Clustered Index and it shows that's how the ordered CTE did the update despite the apparent ORDER BY in the CTE.
--Jeff Moden
June 4, 2009 at 5:45 pm
WayneS (6/4/2009)
Jeff Moden (6/3/2009)
The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times. After that, the graphic I captured from that code running on Profiler on my poor old 7 year old P4 1.8GHz 1GB Ram desktop computer running 2k5 DE sp2. As usual, the details are in the embedded comments in the code.FYI Jeff: I posted a link to this post on the feedback for the article at sqlmag.com.
BWAA-HAAA!!!! You really do want me to install the handrails, huh? 😛
--Jeff Moden
June 4, 2009 at 5:47 pm
Paul White (6/4/2009)
Jeff Moden (6/4/2009)
There was some really cool things I was going to show about the Clustered Index but guess folks will just have to wait for the "book" now. 😛Don't be a tease!
😀
Heh... "kitchy-kitchy-coo!" 😀
--Jeff Moden
June 4, 2009 at 5:50 pm
Paul White (6/4/2009)
The last time I tried this, the 'intermediate materialization' forced a sort as one would expect, but the QO inserted a later sort to ensure updates were performed in clustered index order.
Once again, absolutely spot on except now you have some code to prove it. 🙂
--Jeff Moden
June 4, 2009 at 6:05 pm
Oh shoot. I almost forgot. In the absence of all indexes on the Temp Table, the ordered CTE or an ordered Derived Table may appear to work just fine. That's because in the absence of a Clustered Index, UPDATE will use what some folks refer to as the "natural order". By now, we should all know that the "natural order", even on a freshly built Temp Table, simply cannot be trusted. I won't take the time to prove that because there has to be at least a hundred threads on SQLServerCentral.com that have proven that over and over.
The bottom line is that, amongst other rules like no parallelism, problems with partitioned tables, etc, etc, if you want to use the "quirky" update to do running totals and the like, you must have a Clustered Index on the table and it must be in the same logical order as what the running total calculations expect.
If you don't trust it, then use a cursor or write a CLR. But, many people have tried to make the "quirky" update go wrong and they haven't been able to do it without breaking at least one of the rules for it's usage. I'll be listing those rules in the article I'm rewriting.
As a sidebar and a bit of a tease, I've already got code for the article that proves you can use the Pseudo-Cursor update in a partitioned view. I haven't had the time, yet, but the next stop is to show what's necessary to use it in a partitioned table. The real problem is that the rules for using it in either a partitioned view or partitioned table are, many times, contrary to why people want to do the partioning... ease of archival.
--Jeff Moden
June 4, 2009 at 6:09 pm
WayneS (6/4/2009)
Jeff Moden (6/3/2009)
The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times. After that, the graphic I captured from that code running on Profiler on my poor old 7 year old P4 1.8GHz 1GB Ram desktop computer running 2k5 DE sp2. As usual, the details are in the embedded comments in the code.Jeff, I have a (simple) question. Is this the million-row test you were whipping up for Adam, or your approach for Itzik's article --- or were you able to handle both of these in one?
Both and neither. This is for anyone who wants to know how to do it.
--Jeff Moden
June 4, 2009 at 6:11 pm
Paul White (6/4/2009)
Jeff Moden (6/4/2009)
Here's the reason why I use Index(0) instead of Index(1). From BOL:Cheers Jeff - it was your motivation in including the hint I was after really (though the subsequent IAM discussion was excellent).
/P
Heh... actually.... the motivation is to try to keep the critics from installing handrails. 😛 Think of it as a passive pork chop. :w00t:
Like you and I've both said, the hint is fluff and absolutely not necessary. UPDATE will do it's thing in the same order as the Clustered Index even in the presence of ordering attempts.
--Jeff Moden
June 4, 2009 at 6:51 pm
Jeff Moden (6/4/2009)
...various posts...
Very cool stuff Jeff. And amusing too.
I do hope the term "passive pork chop" sticks. I like it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 4, 2009 at 7:12 pm
Paul White (6/4/2009)
WayneS (6/4/2009)
Hey friends, please help me out here. I'd really like to have an understanding of why this changed.Thanks!
Helped. I think. 😀
Help you did, I do think. FYI, I left a question for you on the forum about the links you found.
I appreciate your help very much!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 7:15 pm
Jeff Moden (6/4/2009)
WayneS (6/4/2009)
Jeff Moden (6/3/2009)
The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times. After that, the graphic I captured from that code running on Profiler on my poor old 7 year old P4 1.8GHz 1GB Ram desktop computer running 2k5 DE sp2. As usual, the details are in the embedded comments in the code.FYI Jeff: I posted a link to this post on the feedback for the article at sqlmag.com.
BWAA-HAAA!!!! You really do want me to install the handrails, huh? 😛
I have to admit, that would be an interesting, albeit scary, sight.:w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2009 at 9:28 pm
Jan Van der Eecken (6/4/2009)
Gaby Abed (6/4/2009)
And now for something completely different...I'm visiting Amsterdam this December, staying with a sister. I'm not Dutch but was wondering what some great places to visit outside Amsterdam would be. I've been there before but never really been outside the city itself, which is beautiful in and of itself.
Anything fun I should see at least once in the rest of the country occurring around the second half of December?
Gaby, one thing you should see is the inside of a heated room. Not really kidding. Although it doesn't quite get as cold as in other places in Europe, the humidity and windchill factor in that part of the world in December can be quite hard on your body.
OK. I can't give advise about Amsterdam's surrounds (will be there in 3 weeks time, would love to see it eventually, but plane schedule doesn't quite work out, late arrival from Vienna and early morning flight back to South Africa), but if I may suggest (since I was born south of the border) that you take a fast train down to Belgium. Lots of ancient cities, well preserved. Especially Ghent and Bruges (I'm not impartial on the latter one since that's my Mother City). Brussels and Antwerp aren't without charm either. And while you're on the train, why not extend your trip to Paris? If I'm not too badly informed it is a comfortable 2 1/2 hour trip from Amsterdam.
Excellent, I've always wanted to do a nice train trip in Europe. I don't know how hectic it would be around holiday time (i'm there for about two weeks, including Christmas), so I might try a day trip as far as Brussels and/or Rotterdam and the Hague. My brother-in-law (he's the Dutch member of our extended family) has suggested as much. As for the cold, when I was in Amsterdam in January 2008, I wore my "light" autumn jacket almost all the days (I guess it's me getting used to the Canadian winters here). However, it was more damp than I'm used to.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
June 4, 2009 at 9:49 pm
WayneS (6/4/2009)
I appreciate your help very much!
No worries - you helped me earlier!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 5, 2009 at 1:34 am
Jeff Moden (6/4/2009)
If you don't trust it, then use a cursor or write a CLR. But, many people have tried to make the "quirky" update go wrong and they haven't been able to do it without breaking at least one of the rules for it's usage.
Jeff, I just don't have words enough to thank you for coming up with this technique: I've been using it on a quite big table (2 mln rows) over the last 5 months on a production db (risk is my middle name...) and it took a mission-critical procedure from 100 to 5 minutes (it was originally written with cursors in 2002 when the number of rows in the table was 20 times fewer). Since I applied this method, nothing went wrong and everything works like a charm, twice a day since january.
I don't know if this is a proof, but for sure it is "half a proof".
Regards,
Gianluca
-- Gianluca Sartori
Viewing 15 posts - 5,371 through 5,385 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply