September 11, 2015 at 10:39 am
Hi all,
Trying to optimise the below query, I believe it's do with the estimated rows on the unpivot using Supratimes this seems to be the only sticking point.
The query below is an example replicating what I'm trying to do in live, it takes around 2 seconds to run on my pc.
TIA
Create --drop --alter
Table #Actuals
(
Period1 FLOAT
, Period2 FLOAT
, Period3 FLOAT
, Period4 FLOAT
, Period5 FLOAT
, Period6 FLOAT
, Period7 FLOAT
, Period8 FLOAT
, Period9 FLOAT
, Period10 FLOAT
, Period11 FLOAT
, Period12 FLOAT
);
Insert #Actuals
( Period1
, Period2
, Period3
, Period4
, Period5
, Period6
, Period7
, Period8
, Period9
, Period10
, Period11
, Period12
)
Values
( 0.01 -- Period1 - float
, 0.0 -- Period2 - float
, 0.0 -- Period3 - float
, 0.0 -- Period4 - float
, 0.0 -- Period5 - float
, 0.0 -- Period6 - float
, 0.0 -- Period7 - float
, 0.0 -- Period8 - float
, 0.0 -- Period9 - float
, 0.0 -- Period10 - float
, 0.0 -- Period11 - float
, 0.0 -- Period12 - float
);
Declare @1 INT = 1;
While @1 < 7000
Begin
Insert #Actuals
( Period1
, Period2
, Period3
, Period4
, Period5
, Period6
, Period7
, Period8
, Period9
, Period10
, Period11
, Period12
)
Select
Period1
, Period2 + ( @1 / 10 )
, Period3 + ( @1 / 10 )
, Period4 + ( @1 / 100 )
, Period5 + ( @1 / 1000 )
, Period6 + ( @1 / 10 )
, Period7 + ( @1 / 10 )
, Period8 + ( @1 / 10 )
, Period9 + ( @1 / 10 )
, Period10 + ( @1 / 10 )
, Period11 + ( @1 / 10 )
, Period12 + ( @1 / 10 )
From
#Actuals;
Select
@1 = COUNT(1)
From
#Actuals;
End;
Create --drop --alter
Table #MonthlyAmounts
(
BudgetPeriod VARCHAR(15)
, [Movement] FLOAT
);
Insert #MonthlyAmounts
( Movement
, BudgetPeriod
)
Select
Movement = Actual
, BudgetPeriod
From
#Actuals Unpivot ( Actual For BudgetPeriod In ( Period1, Period2,
Period3, Period4,
Period5, Period6,
Period7, Period8,
Period9, Period10,
Period11, Period12 ) ) As ASMT;
Select
*
From
#MonthlyAmounts;
Drop Table #Actuals;
Drop Table #MonthlyAmounts;
September 11, 2015 at 11:18 am
You can try this option: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
September 11, 2015 at 9:27 pm
Something like...
CREATE TABLE Actuals (
FY INT
,P1 SMALLMONEY
,P2 SMALLMONEY
,P3 SMALLMONEY
,P4 SMALLMONEY
,P5 SMALLMONEY
,P6 SMALLMONEY
,P7 SMALLMONEY
,P8 SMALLMONEY
,P9 SMALLMONEY
,P10 SMALLMONEY
,P11 SMALLMONEY
,P12 SMALLMONEY
CONSTRAINT pkActuals PRIMARY KEY (FY)
);
GO
-- insert some dummy values...
INSERT INTO dbo.Actuals(FY,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P2)
-- 2000 is base year
SELECT 2000 + t.Num As Yr
-- random values
,ABS(CHECKSUM(NEWID()))%1000 AS P1
,ABS(CHECKSUM(NEWID()))%1000 AS P2
,ABS(CHECKSUM(NEWID()))%1000 AS P3
,ABS(CHECKSUM(NEWID()))%1000 AS P4
,ABS(CHECKSUM(NEWID()))%1000 AS P5
,ABS(CHECKSUM(NEWID()))%1000 AS P6
,ABS(CHECKSUM(NEWID()))%1000 AS P7
,ABS(CHECKSUM(NEWID()))%1000 AS P8
,ABS(CHECKSUM(NEWID()))%1000 AS P9
,ABS(CHECKSUM(NEWID()))%1000 AS P10
,ABS(CHECKSUM(NEWID()))%1000 AS P11
,ABS(CHECKSUM(NEWID()))%1000 AS P12
FROM (SELECT 1 AS Num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10) AS t;
-- unpivot
SELECT x.FY
,Mo=ROW_NUMBER() OVER (PARTITION BY x.fy ORDER BY x.fy)
, x.Amount
FROM dbo.Actuals
CROSS APPLY (
VALUES (FY, P1)
,(FY, P2)
,(FY, P3)
,(FY, P4)
,(FY, P5)
,(FY, P6)
,(FY, P7)
,(FY, P8)
,(FY, P9)
,(FY, P10)
,(FY, P11)
,(FY, P12)
) x (FY, Amount);
(Fourth time's a charm!)
HTH
Pieter
September 13, 2015 at 12:10 am
pietlinden (9/11/2015)
Something like...
CREATE TABLE Actuals (
FY INT
,P1 SMALLMONEY
,P2 SMALLMONEY
,P3 SMALLMONEY
,P4 SMALLMONEY
,P5 SMALLMONEY
,P6 SMALLMONEY
,P7 SMALLMONEY
,P8 SMALLMONEY
,P9 SMALLMONEY
,P10 SMALLMONEY
,P11 SMALLMONEY
,P12 SMALLMONEY
CONSTRAINT pkActuals PRIMARY KEY (FY)
);
GO
-- insert some dummy values...
INSERT INTO dbo.Actuals(FY,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P2)
-- 2000 is base year
SELECT 2000 + t.Num As Yr
-- random values
,ABS(CHECKSUM(NEWID()))%1000 AS P1
,ABS(CHECKSUM(NEWID()))%1000 AS P2
,ABS(CHECKSUM(NEWID()))%1000 AS P3
,ABS(CHECKSUM(NEWID()))%1000 AS P4
,ABS(CHECKSUM(NEWID()))%1000 AS P5
,ABS(CHECKSUM(NEWID()))%1000 AS P6
,ABS(CHECKSUM(NEWID()))%1000 AS P7
,ABS(CHECKSUM(NEWID()))%1000 AS P8
,ABS(CHECKSUM(NEWID()))%1000 AS P9
,ABS(CHECKSUM(NEWID()))%1000 AS P10
,ABS(CHECKSUM(NEWID()))%1000 AS P11
,ABS(CHECKSUM(NEWID()))%1000 AS P12
FROM (SELECT 1 AS Num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10) AS t;
-- unpivot
SELECT x.FY
,Mo=ROW_NUMBER() OVER (PARTITION BY x.fy ORDER BY x.fy)
, x.Amount
FROM dbo.Actuals
CROSS APPLY (
VALUES (FY, P1)
,(FY, P2)
,(FY, P3)
,(FY, P4)
,(FY, P5)
,(FY, P6)
,(FY, P7)
,(FY, P8)
,(FY, P9)
,(FY, P10)
,(FY, P11)
,(FY, P12)
) x (FY, Amount);
(Fourth time's a charm!)
HTH
Pieter
This solution works but it has some serious pitfalls such as unnecessary blocking sort, segmentation and sequence projection. The cost of the query is therefore roughly 5 times what it should be, here is a more efficient solution (using Pieter's sample data set).
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Actuals') IS NOT NULL DROP TABLE dbo.Actuals;
/* Pieter's sample data set */
CREATE TABLE dbo.Actuals (
FY INT
,P1 SMALLMONEY
,P2 SMALLMONEY
,P3 SMALLMONEY
,P4 SMALLMONEY
,P5 SMALLMONEY
,P6 SMALLMONEY
,P7 SMALLMONEY
,P8 SMALLMONEY
,P9 SMALLMONEY
,P10 SMALLMONEY
,P11 SMALLMONEY
,P12 SMALLMONEY
CONSTRAINT pkActuals PRIMARY KEY (FY)
);
GO
-- insert some dummy values...
INSERT INTO dbo.Actuals(FY,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)
-- 2000 is base year
SELECT 2000 + t.Num As Yr
-- random values
,ABS(CHECKSUM(NEWID()))%1000 AS P1
,ABS(CHECKSUM(NEWID()))%1000 AS P2
,ABS(CHECKSUM(NEWID()))%1000 AS P3
,ABS(CHECKSUM(NEWID()))%1000 AS P4
,ABS(CHECKSUM(NEWID()))%1000 AS P5
,ABS(CHECKSUM(NEWID()))%1000 AS P6
,ABS(CHECKSUM(NEWID()))%1000 AS P7
,ABS(CHECKSUM(NEWID()))%1000 AS P8
,ABS(CHECKSUM(NEWID()))%1000 AS P9
,ABS(CHECKSUM(NEWID()))%1000 AS P10
,ABS(CHECKSUM(NEWID()))%1000 AS P11
,ABS(CHECKSUM(NEWID()))%1000 AS P12
FROM (SELECT 1 AS Num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10) AS t;
/* Unpivot */
SELECT
XF.FY
,XF.M
,XF.P
FROM dbo.Actuals ACT
CROSS APPLY
(
SELECT FY,1 ,P1 UNION ALL
SELECT FY,2 ,P2 UNION ALL
SELECT FY,3 ,P3 UNION ALL
SELECT FY,4 ,P4 UNION ALL
SELECT FY,5 ,P5 UNION ALL
SELECT FY,6 ,P6 UNION ALL
SELECT FY,7 ,P7 UNION ALL
SELECT FY,8 ,P8 UNION ALL
SELECT FY,9 ,P9 UNION ALL
SELECT FY,10,P10 UNION ALL
SELECT FY,11,P11 UNION ALL
SELECT FY,12,P12
) AS XF(FY,M,P);
Looking at the graphical representation of the two execution plans, the difference is obvious.
September 13, 2015 at 9:52 am
Because it's still an estimate of cost, I don't trust even the Actual Execution Plan when trying to determine which code will be faster or use fewer resources. It's the "opinion" of the optimizer and, as a very wise man once said, "One good test is worth a thousand expert opinions".
In this case, the Actual Execution Plan does point out the more efficient code but let's prove it.
Here's a bit more test data to work with. It's understood that the FY's produced aren't practical in any sense but that's not what we're trying to demonstrate here.
--===== Since we're going to drop objects, do this in a nice, ssafe place everyone has
USE tempdb;
GO
--===== Suppress any informational output for now
SET NOCOUNT ON;
SET STATISTICS TIME,IO OFF;
GO
--===== If the test table already exists, drop it to make reruns in SSMS easier
IF OBJECT_ID(N'dbo.Actuals') IS NOT NULL DROP TABLE dbo.Actuals;
GO
--===== Create the test table and the clustered index
CREATE TABLE dbo.Actuals
(
FY INT
,P1 SMALLMONEY
,P2 SMALLMONEY
,P3 SMALLMONEY
,P4 SMALLMONEY
,P5 SMALLMONEY
,P6 SMALLMONEY
,P7 SMALLMONEY
,P8 SMALLMONEY
,P9 SMALLMONEY
,P10 SMALLMONEY
,P11 SMALLMONEY
,P12 SMALLMONEY
CONSTRAINT pkActuals PRIMARY KEY CLUSTERED (FY)
);
GO
--===== Populate the table with 10,001 dummy values
WITH
E1 (N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))d(N)) --RowSource of 10E1 or 10 rows
,Tally (N) AS (SELECT 0 UNION ALL -- 1 row containing the value "0"
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E1 a, E1 b, E1 c, E1 d --RowSource of up to 10E4 or 10,000 rows
)
INSERT INTO dbo.Actuals
(FY,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)
SELECT 1900 + t.N As Yr --"1900" is the "base" year
,ABS(CHECKSUM(NEWID()))%1000 AS P1
,ABS(CHECKSUM(NEWID()))%1000 AS P2
,ABS(CHECKSUM(NEWID()))%1000 AS P3
,ABS(CHECKSUM(NEWID()))%1000 AS P4
,ABS(CHECKSUM(NEWID()))%1000 AS P5
,ABS(CHECKSUM(NEWID()))%1000 AS P6
,ABS(CHECKSUM(NEWID()))%1000 AS P7
,ABS(CHECKSUM(NEWID()))%1000 AS P8
,ABS(CHECKSUM(NEWID()))%1000 AS P9
,ABS(CHECKSUM(NEWID()))%1000 AS P10
,ABS(CHECKSUM(NEWID()))%1000 AS P11
,ABS(CHECKSUM(NEWID()))%1000 AS P12
FROM JBMTest.dbo.fnTally(0,10000) AS t
;
GO
Here's the test harness code...
PRINT '========= Eirikur''s Code ============================================';
SET STATISTICS TIME,IO ON
;
SELECT
XF.FY
,XF.M
,XF.P
FROM dbo.Actuals ACT
CROSS APPLY
(
SELECT FY,1 ,P1 UNION ALL
SELECT FY,2 ,P2 UNION ALL
SELECT FY,3 ,P3 UNION ALL
SELECT FY,4 ,P4 UNION ALL
SELECT FY,5 ,P5 UNION ALL
SELECT FY,6 ,P6 UNION ALL
SELECT FY,7 ,P7 UNION ALL
SELECT FY,8 ,P8 UNION ALL
SELECT FY,9 ,P9 UNION ALL
SELECT FY,10,P10 UNION ALL
SELECT FY,11,P11 UNION ALL
SELECT FY,12,P12
) AS XF(FY,M,P);
SET STATISTICS TIME,IO OFF
;
GO 5
PRINT REPLICATE('*',80); --Just a section separator for the output.
PRINT REPLICATE('*',80); --Just a section separator for the output.
GO
PRINT '========= pietlinden''s Code ============================================';
SET STATISTICS TIME,IO ON
;
SELECT x.FY
,Mo=ROW_NUMBER() OVER (PARTITION BY x.fy ORDER BY (SELECT NULL))
,x.Amount
FROM dbo.Actuals
CROSS APPLY (
VALUES (FY, P1)
,(FY, P2)
,(FY, P3)
,(FY, P4)
,(FY, P5)
,(FY, P6)
,(FY, P7)
,(FY, P8)
,(FY, P9)
,(FY, P10)
,(FY, P11)
,(FY, P12)
) x (FY, Amount);
SET STATISTICS TIME,IO OFF
;
GO 5
Here are the results. Again, it does show that the execution plan does show the faster and less resource intensive code but it won't always do that. Always do a real test to make the final determination.
The results also show that returning result sets to the display is the "great equalizer" when it comes to duration and is the reason why people frequently short-circuit the output to disposable variables.
Beginning execution loop
========= Eirikur's Code ============================================
Table 'Actuals'. Scan count 1, logical reads 79, 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 = 31 ms, elapsed time = 725 ms.
========= Eirikur's Code ============================================
Table 'Actuals'. Scan count 1, logical reads 79, 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 = 0 ms, elapsed time = 656 ms.
========= Eirikur's Code ============================================
Table 'Actuals'. Scan count 1, logical reads 79, 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 = 15 ms, elapsed time = 635 ms.
========= Eirikur's Code ============================================
Table 'Actuals'. Scan count 1, logical reads 79, 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 = 32 ms, elapsed time = 632 ms.
========= Eirikur's Code ============================================
Table 'Actuals'. Scan count 1, logical reads 79, 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 = 31 ms, elapsed time = 637 ms.
Batch execution completed 5 times.
********************************************************************************
********************************************************************************
Beginning execution loop
========= pietlinden's Code ============================================
Table 'Actuals'. Scan count 5, logical reads 232, 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 = 92 ms, elapsed time = 683 ms.
========= pietlinden's Code ============================================
Table 'Actuals'. Scan count 5, logical reads 232, 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 = 95 ms, elapsed time = 724 ms.
========= pietlinden's Code ============================================
Table 'Actuals'. Scan count 5, logical reads 232, 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 = 156 ms, elapsed time = 694 ms.
========= pietlinden's Code ============================================
Table 'Actuals'. Scan count 5, logical reads 232, 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 = 126 ms, elapsed time = 724 ms.
========= pietlinden's Code ============================================
Table 'Actuals'. Scan count 5, logical reads 232, 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 = 91 ms, elapsed time = 899 ms.
Batch execution completed 5 times.
As a bit of a sidebar, I take exception to storing such periodic values as either FLOAT or SMALLMONEY. If you need to store monthly monetary values for something like an amortization table, then use the DECIMAL datatype with a scale of at least 15 decimal places. If not, then store the values as a DECIMAL with a scale of 2.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2015 at 10:53 am
Jeff Moden (9/13/2015)
Because it's still an estimate of cost, I don't trust even the Actual Execution Plan when trying to determine which code will be faster or use fewer resources. It's the "opinion" of the optimizer and, as a very wise man once said, "One good test is worth a thousand expert opinions".
I don't trust the cost estimates either, just thought that the graphical representation showed rather accurately what was happening.
😎
Slightly expanded JBM test set of 10^6 rows
--===== Since we're going to drop objects, do this in a nice, safe place everyone has
USE tempdb;
GO
--===== Suppress any informational output for now
SET NOCOUNT ON;
SET STATISTICS TIME,IO OFF;
GO
--===== If the test table already exists, drop it to make reruns in SSMS easier
IF OBJECT_ID(N'dbo.Actuals') IS NOT NULL DROP TABLE dbo.Actuals;
GO
--===== Create the test table and the clustered index
CREATE TABLE dbo.Actuals
(
FY INT
,P1 SMALLMONEY
,P2 SMALLMONEY
,P3 SMALLMONEY
,P4 SMALLMONEY
,P5 SMALLMONEY
,P6 SMALLMONEY
,P7 SMALLMONEY
,P8 SMALLMONEY
,P9 SMALLMONEY
,P10 SMALLMONEY
,P11 SMALLMONEY
,P12 SMALLMONEY
CONSTRAINT pkActuals PRIMARY KEY CLUSTERED (FY)
);
GO
--===== Populate the table with 10,001 dummy values
WITH
E1 (N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))d(N)) --RowSource of 10E1 or 10 rows
,Tally (N) AS (SELECT 0 UNION ALL -- 1 row containing the value "0"
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f --RowSource of up to 10E4 or 10,000 rows
)
INSERT INTO dbo.Actuals
(FY,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)
SELECT 1900 + t.N As Yr --"1900" is the "base" year
,ABS(CHECKSUM(NEWID()))%1000 AS P1
,ABS(CHECKSUM(NEWID()))%1000 AS P2
,ABS(CHECKSUM(NEWID()))%1000 AS P3
,ABS(CHECKSUM(NEWID()))%1000 AS P4
,ABS(CHECKSUM(NEWID()))%1000 AS P5
,ABS(CHECKSUM(NEWID()))%1000 AS P6
,ABS(CHECKSUM(NEWID()))%1000 AS P7
,ABS(CHECKSUM(NEWID()))%1000 AS P8
,ABS(CHECKSUM(NEWID()))%1000 AS P9
,ABS(CHECKSUM(NEWID()))%1000 AS P10
,ABS(CHECKSUM(NEWID()))%1000 AS P11
,ABS(CHECKSUM(NEWID()))%1000 AS P12
FROM Tally AS t
;
GO
Test harness
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @BIGINT_BUCKET BIGINT = 0;
DECLARE @INT_BUCKET INT = 0;
DECLARE @SMALLMONEY_BUCKET SMALLMONEY = 0;
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
/* Round #1 */
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
SELECT
@INT_BUCKET = A.FY
,@SMALLMONEY_BUCKET = A.P1
,@SMALLMONEY_BUCKET = A.P2
,@SMALLMONEY_BUCKET = A.P3
,@SMALLMONEY_BUCKET = A.P4
,@SMALLMONEY_BUCKET = A.P5
,@SMALLMONEY_BUCKET = A.P6
,@SMALLMONEY_BUCKET = A.P7
,@SMALLMONEY_BUCKET = A.P8
,@SMALLMONEY_BUCKET = A.P9
,@SMALLMONEY_BUCKET = A.P10
,@SMALLMONEY_BUCKET = A.P11
,@SMALLMONEY_BUCKET = A.P12
FROM dbo.Actuals A
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
/* Unpivot */
INSERT INTO @timer(T_TEXT) VALUES('EE');
SELECT
@BIGINT_BUCKET = XF.FY
,@INT_BUCKET = XF.M
,@SMALLMONEY_BUCKET = XF.P
FROM dbo.Actuals ACT
CROSS APPLY
(
SELECT FY,1 ,P1 UNION ALL
SELECT FY,2 ,P2 UNION ALL
SELECT FY,3 ,P3 UNION ALL
SELECT FY,4 ,P4 UNION ALL
SELECT FY,5 ,P5 UNION ALL
SELECT FY,6 ,P6 UNION ALL
SELECT FY,7 ,P7 UNION ALL
SELECT FY,8 ,P8 UNION ALL
SELECT FY,9 ,P9 UNION ALL
SELECT FY,10,P10 UNION ALL
SELECT FY,11,P11 UNION ALL
SELECT FY,12,P12
) AS XF(FY,M,P)
;
INSERT INTO @timer(T_TEXT) VALUES('EE');
-- unpivot
INSERT INTO @timer(T_TEXT) VALUES('PL');
SELECT
@BIGINT_BUCKET = x.FY
,@INT_BUCKET = ROW_NUMBER() OVER (PARTITION BY x.fy ORDER BY x.fy)
,@SMALLMONEY_BUCKET = x.Amount
FROM dbo.Actuals
CROSS APPLY (
VALUES (FY, P1)
,(FY, P2)
,(FY, P3)
,(FY, P4)
,(FY, P5)
,(FY, P6)
,(FY, P7)
,(FY, P8)
,(FY, P9)
,(FY, P10)
,(FY, P11)
,(FY, P12)
) x (FY, Amount)
;
INSERT INTO @timer(T_TEXT) VALUES('PL');
/* Round #2 */
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN 2');
SELECT
@INT_BUCKET = A.FY
,@SMALLMONEY_BUCKET = A.P1
,@SMALLMONEY_BUCKET = A.P2
,@SMALLMONEY_BUCKET = A.P3
,@SMALLMONEY_BUCKET = A.P4
,@SMALLMONEY_BUCKET = A.P5
,@SMALLMONEY_BUCKET = A.P6
,@SMALLMONEY_BUCKET = A.P7
,@SMALLMONEY_BUCKET = A.P8
,@SMALLMONEY_BUCKET = A.P9
,@SMALLMONEY_BUCKET = A.P10
,@SMALLMONEY_BUCKET = A.P11
,@SMALLMONEY_BUCKET = A.P12
FROM dbo.Actuals A
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN 2');
/* Unpivot */
INSERT INTO @timer(T_TEXT) VALUES('EE 2');
SELECT
@BIGINT_BUCKET = XF.FY
,@INT_BUCKET = XF.M
,@SMALLMONEY_BUCKET = XF.P
FROM dbo.Actuals ACT
CROSS APPLY
(
SELECT FY,1 ,P1 UNION ALL
SELECT FY,2 ,P2 UNION ALL
SELECT FY,3 ,P3 UNION ALL
SELECT FY,4 ,P4 UNION ALL
SELECT FY,5 ,P5 UNION ALL
SELECT FY,6 ,P6 UNION ALL
SELECT FY,7 ,P7 UNION ALL
SELECT FY,8 ,P8 UNION ALL
SELECT FY,9 ,P9 UNION ALL
SELECT FY,10,P10 UNION ALL
SELECT FY,11,P11 UNION ALL
SELECT FY,12,P12
) AS XF(FY,M,P)
;
INSERT INTO @timer(T_TEXT) VALUES('EE 2');
-- unpivot
INSERT INTO @timer(T_TEXT) VALUES('PL 2');
SELECT
@BIGINT_BUCKET = x.FY
,@INT_BUCKET = ROW_NUMBER() OVER (PARTITION BY x.fy ORDER BY x.fy)
,@SMALLMONEY_BUCKET = x.Amount
FROM dbo.Actuals
CROSS APPLY (
VALUES (FY, P1)
,(FY, P2)
,(FY, P3)
,(FY, P4)
,(FY, P5)
,(FY, P6)
,(FY, P7)
,(FY, P8)
,(FY, P9)
,(FY, P10)
,(FY, P11)
,(FY, P12)
) x (FY, Amount)
;
INSERT INTO @timer(T_TEXT) VALUES('PL 2');
/* Display the test results */
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
ORDER BY DURATION;
Results (2nd. gen i5 laptop)
T_TEXT DURATION
---------- ---------
DRY RUN 2 410023
DRY RUN 420024
EE 2 2411138
EE 2439139
PL 7190412
PL 2 7562432
September 13, 2015 at 3:54 pm
Oh, ouch. That's painful slow. <hanging head>
September 14, 2015 at 3:41 am
Hi All,
Thanks for the responses, as per the original query I have tried the union all in comparison to the unpivot and for the first run the data takes 3 times longer than an unpivot... 110ms vs 40ms
I have tried adding indexes and the time generating them is in excess of the original query I think I'm going to have to suck it up as an inefficient query and move on. Boo
Create --drop --alter
Table #Actuals
(
Period1 FLOAT
, Period2 FLOAT
, Period3 FLOAT
, Period4 FLOAT
, Period5 FLOAT
, Period6 FLOAT
, Period7 FLOAT
, Period8 FLOAT
, Period9 FLOAT
, Period10 FLOAT
, Period11 FLOAT
, Period12 FLOAT
);
Insert #Actuals
( Period1
, Period2
, Period3
, Period4
, Period5
, Period6
, Period7
, Period8
, Period9
, Period10
, Period11
, Period12
)
Values
( 0.01 -- Period1 - FLOAT
, 0.0 -- Period2 - FLOAT
, 0.0 -- Period3 - FLOAT
, 0.0 -- Period4 - FLOAT
, 0.0 -- Period5 - FLOAT
, 0.0 -- Period6 - FLOAT
, 0.0 -- Period7 - FLOAT
, 0.0 -- Period8 - FLOAT
, 0.0 -- Period9 - FLOAT
, 0.0 -- Period10.0 - FLOAT
, 0.0 -- Period11 - FLOAT
, 0.0 -- Period12 - FLOAT
);
Declare @1 INT = 1;
While @1 < 7000
Begin
Insert #Actuals
( Period1
, Period2
, Period3
, Period4
, Period5
, Period6
, Period7
, Period8
, Period9
, Period10
, Period11
, Period12
)
Select
Period1
, Period2 + ( @1 / 10.0 )
, Period3 + ( @1 / 10.0 )
, Period4 + ( @1 / 100.0 )
, Period5 + ( @1 / 1000.0 )
, Period6 + ( @1 / 10.0 )
, Period7 + ( @1 / 10.0 )
, Period8 + ( @1 / 10.0 )
, Period9 + ( @1 / 10.0 )
, Period10 + ( @1 / 10.0 )
, Period11 + ( @1 / 10.0 )
, Period12 + ( @1 / 10.0 )
From
#Actuals;
Select
@1 = COUNT(1)
From
#Actuals;
End;
Create --drop --alter
Table #MonthlyAmounts
(
BudgetPeriod VARCHAR(15)
, [Movement] FLOAT
);
--Set Timer Table
DECLARE @timer TABLE (RunID INT,T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()))
Declare @Runs INT, @CurrentRun INT =1
Set @Runs=10
While @CurrentRun<=@Runs
Begin
Insert @timer
( RunID, T_TEXT, T_TS )
Select @CurrentRun, 'union all'
,GETDATE()
Insert #MonthlyAmounts
( BudgetPeriod
, Movement
)
Select
act2.Period
, act2.Movement
From
#Actuals ACT
Cross Apply (
Select
Period = 1
, Movement = Period1
Union All
Select
2
, Period2
Union All
Select
3
, Period3
Union All
Select
4
, Period4
Union All
Select
5
, Period5
Union All
Select
6
, Period6
Union All
Select
7
, Period7
Union All
Select
8
, Period8
Union All
Select
9
, Period9
Union All
Select
10
, Period10
Union All
Select
11
, Period11
Union All
Select
12
, Period12
) act2;
Insert @timer
( RunID, T_TEXT, T_TS )
Select @CurrentRun, 'union all'
,GETDATE()
Insert @timer
( RunID, T_TEXT, T_TS )
Select @CurrentRun, 'unpivot'
,GETDATE()
Insert #MonthlyAmounts
( Movement
, BudgetPeriod
)
Select
Movement = Actual
, BudgetPeriod
From
#Actuals Unpivot ( Actual For BudgetPeriod In ( Period1, Period2,
Period3, Period4,
Period5, Period6,
Period7, Period8,
Period9, Period10,
Period11, Period12 ) ) As ASMT;
Insert @timer
( RunID, T_TEXT, T_TS )
Select @CurrentRun, 'unpivot'
,GETDATE()
Select @CurrentRun=@CurrentRun+1
End
SELECT T_TEXT
,RunID
, TimeToRun = DATEDIFF(Millisecond,min(T_TS),MAX(T_TS))
Into #Test
From @timer
Group By T_TEXT,RunID
Order By RunID a, DATEDIFF(Millisecond,min(T_TS),MAX(T_TS)) Asc
Select * FROM #Test
Select T_TEXT
--,COUNT(RunID)
, AverageTimeToRun = SUM(TimeToRun)/COUNT(RunID)
From #Test
Group By T_TEXT
Drop Table #Actuals;
Drop Table #MonthlyAmounts;
Drop Table #Test
September 14, 2015 at 8:02 am
Indexes probably won't help at all because you're looking at the whole table.
Thanks for the feedback on the true UNPIVOT method. I'll give that a try, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply