July 8, 2015 at 10:39 am
Hi Jeff,
Congratulations for the nice article and thanks for sharing your fantastic "Quirky Update". Very fast!
With SQL Server 2012, I run the scripts in your article until Figure 18 without a problem. But when I run the script in Figure 19, it fails. Nothing is updated in the table TransactionDetail. Then, casually, I commented the WHERE CLAUSE and it worked. See below:
--CODE FROM FIGURE 19 - The "Quirky Update" Running Total and Running Count
/*************************************************************************************
Pseduo-cursor update using the "Quirky Update" to calculate both Running Totals and
a Running Count that start over for each AccountID.
Takes 24 seconds with the INDEX(0) hint and 6 seconds without it on my box.
*************************************************************************************/
--===== Supress the auto-display of rowcounts for speed an appearance
SET NOCOUNT ON
--===== Declare the working variables
DECLARE @PrevAccountID INT
DECLARE @AccountRunningTotal MONEY
DECLARE @AccountRunningCount INT
--===== Update the running total and running count for this row using the "Quirky
-- Update" and a "Pseudo-cursor". The order of the UPDATE is controlled by the
-- order of the clustered index.
UPDATE dbo.TransactionDetail
SET @AccountRunningTotal = AccountRunningTotal = CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal + Amount
ELSE Amount
END,
@AccountRunningCount = AccountRunningCount = CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningCount + 1
ELSE 1
END,
@PrevAccountID = AccountID
FROM dbo.TransactionDetail WITH (TABLOCKX)
--Where accountID = 1
OPTION (MAXDOP 1)
GO
With the commented WHERE CLAUSE, the script runs in my machine in less then 1 sec.
This problem happens with SQL Server 2014 also.
Regards
Iracildo
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy