April 4, 2015 at 11:12 am
Kathi Kellenberger (4/4/2015)
Jeff -- For the running totals functionality, I think the performance is pretty good if you use ROWS and have the correct index in place. Where I really wish they did things better is windows aggregates without the ORDER BY in the OVER clause.
It's not bad according to most people's standards and if you're doing the whole table, you don't actually need an index but it's still more than 7 times slower than it should have been, IMHO. See the following test results from an early test.
http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2015 at 12:42 pm
I'm not an expert on the Quirky Update, but don't you have to run the update which reads the entire table and then select the data after that which also reads the table?
Generally, the running total with ROWS will read about the same number of pages as selecting the same data without the calculation. Looks like the Quirky Update is better if you want to do an actual update, but if you just want to select the data, using the windows running total with ROWS is better.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 4, 2015 at 12:55 pm
Kathi Kellenberger (4/4/2015)
Jeff -- For the running totals functionality, I think the performance is pretty good if you use ROWS and have the correct index in place. Where I really wish they did things better is windows aggregates without the ORDER BY in the OVER clause.
I have been looking into few of the running totals tests lately and found many of them quite flawed, including Wayne's, here are two results using his test data set with a slight modifications to the harness. Difference is more like 2x rather than 7x.
😎
WINDOW FUNCTION ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TransactionDetail'. Scan count 1, logical reads 6085, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 1248 ms, elapsed time = 1245 ms.
QUIRKY UPDATE------------------------------------------
Table 'TransactionDetail'. Scan count 1, logical reads 6085, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 639 ms, elapsed time = 645 ms.
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
April 4, 2015 at 1:12 pm
Kathi Kellenberger (4/4/2015)
I'm not an expert on the Quirky Update, but don't you have to run the update which reads the entire table and then select the data after that which also reads the table?Generally, the running total with ROWS will read about the same number of pages as selecting the same data without the calculation. Looks like the Quirky Update is better if you want to do an actual update, but if you just want to select the data, using the windows running total with ROWS is better.
My findings are that the Quirky Updates are still around 2x faster although the cardinality and the distribution of the set does have quite an impact on the performance.
😎
April 4, 2015 at 1:22 pm
In your test, are you just performing the update or are you also selecting the data?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 4, 2015 at 1:25 pm
Kathi Kellenberger (4/4/2015)
In your test, are you just performing the update or are you also selecting the data?
I'm selecting the data into "bucket" variables
😎
Edit: typo^2
April 4, 2015 at 8:00 pm
Eirikur Eiriksson (4/4/2015)
Kathi Kellenberger (4/4/2015)
Jeff -- For the running totals functionality, I think the performance is pretty good if you use ROWS and have the correct index in place. Where I really wish they did things better is windows aggregates without the ORDER BY in the OVER clause.I have been looking into few of the running totals tests lately and found many of them quite flawed, including Wayne's, here are two results using his test data set with a slight modifications to the harness. Difference is more like 2x rather than 7x.
😎
WINDOW FUNCTION ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ------------------------------------------Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TransactionDetail'. Scan count 1, logical reads 6085, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 1248 ms, elapsed time = 1245 ms.
QUIRKY UPDATE------------------------------------------
Table 'TransactionDetail'. Scan count 1, logical reads 6085, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 639 ms, elapsed time = 645 ms.
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
Got code and test data for that, Eirikur? And what is wrong with Wayne's test harness? Please be sure to include yours.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2015 at 2:39 am
patricklambin (4/3/2015)
Interesting question but maybe too easy with some minutes to spend :- Plenty of RAM : too easy to be the good choice
- to use OPTIMIZE : never seen this parameter in the OVER clause
- to use older methods : surprising
only one left choice
Good analysis Patrick 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
April 5, 2015 at 3:08 am
Hi Hany ,
Thank you for your comment.
My own full comment about the older method should be ended by : why not if they can be applied without doing the reading of the query ?
But it is only today I found a not too bad formulation about my thoughts ( always this dreadful problem to translate from French towards English ... )
April 5, 2015 at 5:03 pm
Jeff Moden (4/4/2015)
Got code and test data for that, Eirikur? And what is wrong with Wayne's test harness? Please be sure to include yours.
Thought you would be interested;-)
Still a work in progress, busy chasing Easter/Dust bunnies these days, will post more complete testing and finding as soon as I have time. In the meantime, here is a partial testing code which strongly indicates that the original testing is somewhat flawed.
😎
Quick note, moved the testing from tempdb into a dedicated Test database for more realistic results.
The first part times each operation including the reset update of the result table, the second runs the same test with STATISTICS IO.
USE Test;
GO
SET NOCOUNT ON;
GO
/*************************************************************************************
Timer table
*************************************************************************************/
IF OBJECT_ID(N'dbo.TBL_TIMER') IS NOT NULL DROP TABLE dbo.TBL_TIMER;
CREATE TABLE dbo.TBL_TIMER
(
TT_TEXT VARCHAR(200) NOT NULL
,TT_TIME DATETIME2(7) NOT NULL CONSTRAINT DFLT_DBO_TT_TIMER_TT_TIME DEFAULT (SYSDATETIME())
);
GO
DECLARE @INT_BUCKET_01 INT = 0;
DECLARE @DATE_BUCKET DATETIME = NULL;
DECLARE @INT_BUCKET_02 INT = 0;
DECLARE @MONEY_BUCKET_01 MONEY = 0;
DECLARE @INT_BUCKET_03 INT = 0;
DECLARE @MONEY_BUCKET_02 MONEY = 0;
DECLARE @INT_BUCKET_04 INT = 0;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 1');
EXEC dbo.ResetTestTable;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 1');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 UPDATE');
;WITH AGGREGATED_DATA AS
(
SELECT
TD.TransactionDetailID
,SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningTotal
,COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningCount
FROM dbo.TransactionDetail TD
)
UPDATE TD
SET TD.AccountRunningTotal = AD.AccountRunningTotal
,TD.AccountRunningCount = AD.AccountRunningCount
FROM dbo.TransactionDetail TD
INNER JOIN AGGREGATED_DATA AD
ON TD.TransactionDetailID = AD.TransactionDetailID;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 UPDATE');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 2');
EXEC dbo.ResetTestTable;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 2');
IF OBJECT_ID(N'dbo.TBL_TMP_RESULT') IS NOT NULL DROP TABLE dbo.TBL_TMP_RESULT;
CREATE TABLE dbo.TBL_TMP_RESULT
(
TransactionDetailID INT NOT NULL PRIMARY KEY CLUSTERED
,AccountRunningTotal MONEY NOT NULL
,AccountRunningCount INT NOT NULL
);
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 UPDATE 2');
;WITH AGGREGATED_DATA AS
(
SELECT
TD.TransactionDetailID
,SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningTotal
,COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningCount
FROM dbo.TransactionDetail TD
)
INSERT INTO dbo.TBL_TMP_RESULT WITH (TABLOCK) (TransactionDetailID,AccountRunningTotal,AccountRunningCount)
SELECT
AD.TransactionDetailID
,AD.AccountRunningTotal
,AD.AccountRunningCount
FROM AGGREGATED_DATA AD
UPDATE TD WITH (TABLOCK)
SET TD.AccountRunningTotal = AD.AccountRunningTotal
,TD.AccountRunningCount = AD.AccountRunningCount
FROM dbo.TransactionDetail TD
INNER JOIN dbo.TBL_TMP_RESULT AD
ON TD.TransactionDetailID = AD.TransactionDetailID;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 UPDATE 2');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 3');
EXEC dbo.ResetTestTable;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 3');
IF OBJECT_ID(N'dbo.TBL_TMP_RESULT') IS NOT NULL DROP TABLE dbo.TBL_TMP_RESULT;
CREATE TABLE dbo.TBL_TMP_RESULT
(
TransactionDetailID INT NOT NULL PRIMARY KEY CLUSTERED
,AccountRunningTotal MONEY NOT NULL
,AccountRunningCount INT NOT NULL
);
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 INSERT SELECT');
;WITH AGGREGATED_DATA AS
(
SELECT
TD.TransactionDetailID
,SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningTotal
,COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningCount
FROM dbo.TransactionDetail TD
)
INSERT INTO dbo.TBL_TMP_RESULT WITH (TABLOCK) (TransactionDetailID,AccountRunningTotal,AccountRunningCount)
SELECT
AD.TransactionDetailID
,AD.AccountRunningTotal
,AD.AccountRunningCount
FROM AGGREGATED_DATA AD;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 INSERT SELECT');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 4');
EXEC dbo.ResetTestTable;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 4');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 SELECT');
SELECT
@INT_BUCKET_01 = TD.TransactionDetailID
,@DATE_BUCKET = TD.Date
,@INT_BUCKET_02 = TD.AccountID
,@MONEY_BUCKET_01 = TD.Amount
,@INT_BUCKET_03 = TD.NCID
,@MONEY_BUCKET_02 = SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) --AS AccountRunningTotal
,@INT_BUCKET_04 = COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) --AS AccountRunningCount
FROM dbo.TransactionDetail TD;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('METHOD 1 SELECT');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 5');
EXEC dbo.ResetTestTable;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 5');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('DENALI CTP3 Running Totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW');
;WITH cte AS
(
SELECT AccountRunningTotal,
AccountRunningCount,
ART = SUM(Amount) OVER (PARTITION BY AccountID
ORDER BY Date, TransactionDetailID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
ARC = SUM(1) OVER (PARTITION BY AccountID
ORDER BY Date, TransactionDetailID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM dbo.TransactionDetail
)
UPDATE cte
SET AccountRunningTotal = ART,
AccountRunningCount = ARC
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('DENALI CTP3 Running Totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 6');
EXEC dbo.ResetTestTable;
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('EXEC dbo.ResetTestTable 6');
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('Quirky Update running totals');
-- Quirky Update running totals
--===== 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)
OPTION (MAXDOP 1);
INSERT INTO dbo.TBL_TIMER(TT_TEXT) VALUES('Quirky Update running totals');
SELECT
T.TT_TEXT
,DATEDIFF(MICROSECOND,MIN(T.TT_TIME),MAX(T.TT_TIME)) AS DURATION
FROM dbo.TBL_TIMER T
GROUP BY T.TT_TEXT
ORDER BY DURATION;
Timer Results
TT_TEXT DURATION
------------------------------------------------------------------------------------ -----------
EXEC dbo.ResetTestTable 4 770044
EXEC dbo.ResetTestTable 5 776044
EXEC dbo.ResetTestTable 1 1201069
EXEC dbo.ResetTestTable 2 1274073
EXEC dbo.ResetTestTable 6 1282073
EXEC dbo.ResetTestTable 3 1291073
Quirky Update running totals 1572090
METHOD 1 SELECT 1736099
METHOD 1 INSERT SELECT 2648151
METHOD 1 UPDATE 7251415
METHOD 1 UPDATE 2 8545489
DENALI CTP3 Running Totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 13900795
USE Test;
GO
SET NOCOUNT ON;
GO
GO
DECLARE @INT_BUCKET_01 INT = 0;
DECLARE @DATE_BUCKET DATETIME = NULL;
DECLARE @INT_BUCKET_02 INT = 0;
DECLARE @MONEY_BUCKET_01 MONEY = 0;
DECLARE @INT_BUCKET_03 INT = 0;
DECLARE @MONEY_BUCKET_02 MONEY = 0;
DECLARE @INT_BUCKET_04 INT = 0;
EXEC dbo.ResetTestTable;
RAISERROR(N'METHOD 1 UPDATE -------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO ON;
;WITH AGGREGATED_DATA AS
(
SELECT
TD.TransactionDetailID
,SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningTotal
,COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningCount
FROM dbo.TransactionDetail TD
)
UPDATE TD
SET TD.AccountRunningTotal = AD.AccountRunningTotal
,TD.AccountRunningCount = AD.AccountRunningCount
FROM dbo.TransactionDetail TD
INNER JOIN AGGREGATED_DATA AD
ON TD.TransactionDetailID = AD.TransactionDetailID;
SET STATISTICS IO OFF;
EXEC dbo.ResetTestTable;
IF OBJECT_ID(N'dbo.TBL_TMP_RESULT') IS NOT NULL DROP TABLE dbo.TBL_TMP_RESULT;
CREATE TABLE dbo.TBL_TMP_RESULT
(
TransactionDetailID INT NOT NULL PRIMARY KEY CLUSTERED
,AccountRunningTotal MONEY NOT NULL
,AccountRunningCount INT NOT NULL
);
RAISERROR(N'METHOD 1 UPDATE 2 -------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO ON;
;WITH AGGREGATED_DATA AS
(
SELECT
TD.TransactionDetailID
,SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningTotal
,COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningCount
FROM dbo.TransactionDetail TD
)
INSERT INTO dbo.TBL_TMP_RESULT WITH (TABLOCK) (TransactionDetailID,AccountRunningTotal,AccountRunningCount)
SELECT
AD.TransactionDetailID
,AD.AccountRunningTotal
,AD.AccountRunningCount
FROM AGGREGATED_DATA AD
UPDATE TD WITH (TABLOCK)
SET TD.AccountRunningTotal = AD.AccountRunningTotal
,TD.AccountRunningCount = AD.AccountRunningCount
FROM dbo.TransactionDetail TD
INNER JOIN dbo.TBL_TMP_RESULT AD
ON TD.TransactionDetailID = AD.TransactionDetailID;
SET STATISTICS IO OFF;
EXEC dbo.ResetTestTable;
IF OBJECT_ID(N'dbo.TBL_TMP_RESULT') IS NOT NULL DROP TABLE dbo.TBL_TMP_RESULT;
CREATE TABLE dbo.TBL_TMP_RESULT
(
TransactionDetailID INT NOT NULL PRIMARY KEY CLUSTERED
,AccountRunningTotal MONEY NOT NULL
,AccountRunningCount INT NOT NULL
);
RAISERROR(N'METHOD 1 INSERT SELECT -------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO ON;
;WITH AGGREGATED_DATA AS
(
SELECT
TD.TransactionDetailID
,SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningTotal
,COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AccountRunningCount
FROM dbo.TransactionDetail TD
)
INSERT INTO dbo.TBL_TMP_RESULT WITH (TABLOCK) (TransactionDetailID,AccountRunningTotal,AccountRunningCount)
SELECT
AD.TransactionDetailID
,AD.AccountRunningTotal
,AD.AccountRunningCount
FROM AGGREGATED_DATA AD;
SET STATISTICS IO OFF;
EXEC dbo.ResetTestTable;
RAISERROR(N'METHOD 1 SELECT -------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO ON;
SELECT
@INT_BUCKET_01 = TD.TransactionDetailID
,@DATE_BUCKET = TD.Date
,@INT_BUCKET_02 = TD.AccountID
,@MONEY_BUCKET_01 = TD.Amount
,@INT_BUCKET_03 = TD.NCID
,@MONEY_BUCKET_02 = SUM(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) --AS AccountRunningTotal
,@INT_BUCKET_04 = COUNT(TD.Amount) OVER
(
PARTITION BY TD.AccountID
ORDER BY TD.Date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) --AS AccountRunningCount
FROM dbo.TransactionDetail TD;
SET STATISTICS IO OFF;
EXEC dbo.ResetTestTable;
-- DENALI CTP3 Running Totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RAISERROR(N'DENALI CTP3 Running Totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO ON;
;WITH cte AS
(
SELECT AccountRunningTotal,
AccountRunningCount,
ART = SUM(Amount) OVER (PARTITION BY AccountID
ORDER BY Date, TransactionDetailID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
ARC = SUM(1) OVER (PARTITION BY AccountID
ORDER BY Date, TransactionDetailID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM dbo.TransactionDetail
)
UPDATE cte
SET AccountRunningTotal = ART,
AccountRunningCount = ARC
SET STATISTICS IO OFF;
EXEC dbo.ResetTestTable;
RAISERROR(N'Quirky Update running totals -------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO ON;
-- Quirky Update running totals
--===== 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)
OPTION (MAXDOP 1);
SET STATISTICS IO OFF;
IO Statistics
METHOD 1 UPDATE -------------------------------------
Table 'TransactionDetail'. Scan count 10, logical reads 3011623, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
METHOD 1 UPDATE 2 -------------------------------------
Table 'TransactionDetail'. Scan count 5, logical reads 8852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_TMP_RESULT'. Scan count 5, logical reads 3121, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TransactionDetail'. Scan count 5, logical reads 3065272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
METHOD 1 INSERT SELECT -------------------------------------
Table 'TransactionDetail'. Scan count 5, logical reads 8844, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
METHOD 1 SELECT -------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TransactionDetail'. Scan count 1, logical reads 8722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
DENALI CTP3 Running Totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -------------------------------------
Table 'TransactionDetail'. Scan count 1, logical reads 5956654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Quirky Update running totals -------------------------------------
Table 'TransactionDetail'. Scan count 1, logical reads 8722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
April 5, 2015 at 5:20 pm
Eirikur Eiriksson (4/5/2015)
here is a partial testing code which strongly indicates that the original testing is somewhat flawed.
Sorry... I don't have the time to wade through a ton of undocumented code. What indication and how so?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2015 at 5:33 pm
Jeff Moden (4/5/2015)
Eirikur Eiriksson (4/5/2015)
here is a partial testing code which strongly indicates that the original testing is somewhat flawed.Sorry... I don't have the time to wade through a ton of undocumented code. What indication and how so?
No worries, I didn't have time to comment/document either. The main flaw is the excessive read in the CTE update implementation in the original test harness, even by doing an insert into a table and join update just matches the number of reads. A different kind of update with CTE as a source has half the number of reads compared to the original. There is more and I will elaborate on this as soon as I have time.
😎
April 5, 2015 at 5:37 pm
Eirikur Eiriksson (4/5/2015)
Jeff Moden (4/5/2015)
Eirikur Eiriksson (4/5/2015)
here is a partial testing code which strongly indicates that the original testing is somewhat flawed.Sorry... I don't have the time to wade through a ton of undocumented code. What indication and how so?
No worries, I didn't have time to comment/document either. The main flaw is the excessive read in the CTE update implementation in the original test harness, even by doing an insert into a table and join update just matches the number of reads. A different kind of update with CTE as a source has half the number of reads compared to the original. There is more and I will elaborate on this as soon as I have time.
😎
Looking forward to it. Thanks, Eirikur.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2015 at 5:15 am
Jeff Moden (4/5/2015)
Eirikur Eiriksson (4/5/2015)
Jeff Moden (4/5/2015)
Eirikur Eiriksson (4/5/2015)
here is a partial testing code which strongly indicates that the original testing is somewhat flawed.Sorry... I don't have the time to wade through a ton of undocumented code. What indication and how so?
No worries, I didn't have time to comment/document either. The main flaw is the excessive read in the CTE update implementation in the original test harness, even by doing an insert into a table and join update just matches the number of reads. A different kind of update with CTE as a source has half the number of reads compared to the original. There is more and I will elaborate on this as soon as I have time.
😎
Looking forward to it. Thanks, Eirikur.
Me too. Some of these performance comparisons are my favorite threads. 😉
April 6, 2015 at 6:38 am
Thanks for the question.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply