Optimisation of unpivot/insert code

• c.johnson

  SSC Enthusiast

  Points: 184

  More actions

  September 11, 2015 at 10:39 am

  #300476

  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;

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 11, 2015 at 11:18 am

  #1826086

  You can try this option: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 11, 2015 at 9:27 pm

  #1826184

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  September 13, 2015 at 12:10 am

  #1826232

  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.

  

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  September 13, 2015 at 9:52 am

  #1826247

  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

  ---

  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)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  September 13, 2015 at 10:53 am

  #1826250

  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

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 13, 2015 at 3:54 pm

  #1826267

  Oh, ouch. That's painful slow. <hanging head>

• c.johnson

  SSC Enthusiast

  Points: 184

  More actions

  September 14, 2015 at 3:41 am

  #1826306

  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

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  September 14, 2015 at 8:02 am

  #1826369

  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

  ---

  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)