Convert Columns to Rows

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 14, 2016 at 2:23 pm

  #1900959

  j-1064772 (9/14/2016)

  ---

  Clear explanation, thank you all.

  Still not in the mindset of a million rows ... My DB's are considerably more modest.

  "Still not in the mindset of a million rows"......neither was I when I started on this forum....but having a test harness for 1/10 M rows does demonstrate the efficency in your code.

  I think Jeff Moden has an article on this site re building test data...and if you keep reviewing posts on this site you will also see many examples/variants....worth bearing in mind in my opinion when testing.

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  September 14, 2016 at 2:26 pm

  #1900961

  J Livingston SQL (9/14/2016)

  ---

  j-1064772 (9/14/2016)

  ---

  Clear explanation, thank you all.

  Still not in the mindset of a million rows ... My DB's are considerably more modest.

  "Still not in the mindset of a million rows"......neither was I when I started on this forum....but having a test harness for 1/10 M rows does demonstrate the efficency in your code.

  I think Jeff Moden has an article on this site re building test data...and if you keep reviewing posts on this site you will also see many examples/variants....worth bearing in mind in my opinion when testing.

  Got it. Tks.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 14, 2016 at 2:30 pm

  #1900964

  j-1064772 (9/14/2016)

  ---

  J Livingston SQL (9/14/2016)

  ---

  j-1064772 (9/14/2016)

  ---

  Clear explanation, thank you all.

  Still not in the mindset of a million rows ... My DB's are considerably more modest.

  "Still not in the mindset of a million rows"......neither was I when I started on this forum....but having a test harness for 1/10 M rows does demonstrate the efficency in your code.

  I think Jeff Moden has an article on this site re building test data...and if you keep reviewing posts on this site you will also see many examples/variants....worth bearing in mind in my opinion when testing.

  Got it. Tks.

  found the links I referred to

  http://www.sqlservercentral.com/articles/Data+Generation/87901/

  http://www.sqlservercentral.com/articles/Test+Data/88964/

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  September 14, 2016 at 2:34 pm

  #1900965

  Thank you for taking the time of finding the links.

  Will definitely look them up.

  Regards

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  September 15, 2016 at 12:26 am

  #1901021

  Here is a simple test harness which you can use

  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  -- TEST HARNESS

  DECLARE @timer TABLE (

  T_TXT VARCHAR(50) NOT NULL

  ,T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())

  ,T_CPU FLOAT NOT NULL DEFAULT (@@CPU_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))

  ,T_IDLE FLOAT NOT NULL DEFAULT (@@IDLE * CONVERT(FLOAT,@@TIMETICKS,0))

  );

  --/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATA SET CREATION

  INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');

  IF OBJECT_ID(N'dbo.TBL_TEST_UNPIVOT_AGGREGATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE;

  CREATE TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE

  (

  ROW_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_TEST_UNPIVOT_AGGREGATE_ROW_ID PRIMARY KEY CLUSTERED

  ,COL001 INT NOT NULL

  ,COL002 INT NOT NULL

  ,COL003 INT NOT NULL

  ,COL004 INT NOT NULL

  ,COL005 INT NOT NULL

  ,COL006 INT NOT NULL

  ,COL007 INT NOT NULL

  ,COL008 INT NOT NULL

  ,COL009 INT NOT NULL

  ,COL010 INT NOT NULL

  ,COL011 INT NOT NULL

  ,COL012 INT NOT NULL

  ,COL013 INT NOT NULL

  ,COL014 INT NOT NULL

  ,COL015 INT NOT NULL

  ,COL016 INT NOT NULL

  ,COL017 INT NOT NULL

  ,COL018 INT NOT NULL

  ,COL019 INT NOT NULL

  ,COL020 INT NOT NULL

  );

  -- Set the data sample's parameters

  DECLARE @SAMPLE_SIZE INT = 1000000;

  DECLARE @MAX_VALUE INT = 1025;

  ;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) AS N

  FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

  INSERT INTO dbo.TBL_TEST_UNPIVOT_AGGREGATE WITH (TABLOCK)

  (

  ROW_ID,COL001,COL002,COL003,COL004,COL005,COL006,COL007,COL008,COL009,COL010

  ,COL011,COL012,COL013,COL014,COL015,COL016,COL017,COL018,COL019,COL020

  )

  SELECT

  NM.N

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  FROM NUMS NM;

  INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');

  -- */

  DECLARE @INT_BUCKET INT = 0;

  DECLARE @VCHAR_BUCKET VARCHAR(12) = '';

  INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

  SELECT

  @INT_BUCKET = TUA.ROW_ID

  ,@INT_BUCKET = TUA.COL001

  ,@INT_BUCKET = TUA.COL002

  ,@INT_BUCKET = TUA.COL003

  ,@INT_BUCKET = TUA.COL004

  ,@INT_BUCKET = TUA.COL005

  ,@INT_BUCKET = TUA.COL006

  ,@INT_BUCKET = TUA.COL007

  ,@INT_BUCKET = TUA.COL008

  ,@INT_BUCKET = TUA.COL009

  ,@INT_BUCKET = TUA.COL010

  ,@INT_BUCKET = TUA.COL011

  ,@INT_BUCKET = TUA.COL012

  ,@INT_BUCKET = TUA.COL013

  ,@INT_BUCKET = TUA.COL014

  ,@INT_BUCKET = TUA.COL015

  ,@INT_BUCKET = TUA.COL016

  ,@INT_BUCKET = TUA.COL017

  ,@INT_BUCKET = TUA.COL018

  ,@INT_BUCKET = TUA.COL019

  ,@INT_BUCKET = TUA.COL020

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA;

  INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');

  ;WITH BASE_AGGREGATE AS

  (

  SELECT

  SUM(TUA.COL001) AS SUM_COL001

  ,SUM(TUA.COL002) AS SUM_COL002

  ,SUM(TUA.COL003) AS SUM_COL003

  ,SUM(TUA.COL004) AS SUM_COL004

  ,SUM(TUA.COL005) AS SUM_COL005

  ,SUM(TUA.COL006) AS SUM_COL006

  ,SUM(TUA.COL007) AS SUM_COL007

  ,SUM(TUA.COL008) AS SUM_COL008

  ,SUM(TUA.COL009) AS SUM_COL009

  ,SUM(TUA.COL010) AS SUM_COL010

  ,SUM(TUA.COL011) AS SUM_COL011

  ,SUM(TUA.COL012) AS SUM_COL012

  ,SUM(TUA.COL013) AS SUM_COL013

  ,SUM(TUA.COL014) AS SUM_COL014

  ,SUM(TUA.COL015) AS SUM_COL015

  ,SUM(TUA.COL016) AS SUM_COL016

  ,SUM(TUA.COL017) AS SUM_COL017

  ,SUM(TUA.COL018) AS SUM_COL018

  ,SUM(TUA.COL019) AS SUM_COL019

  ,SUM(TUA.COL020) AS SUM_COL020

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  )

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = X.VAL

  FROM BASE_AGGREGATE BA

  CROSS APPLY

  (

  SELECT 'COL001', BA.SUM_COL001 UNION ALL

  SELECT 'COL002', BA.SUM_COL002 UNION ALL

  SELECT 'COL003', BA.SUM_COL003 UNION ALL

  SELECT 'COL004', BA.SUM_COL004 UNION ALL

  SELECT 'COL005', BA.SUM_COL005 UNION ALL

  SELECT 'COL006', BA.SUM_COL006 UNION ALL

  SELECT 'COL007', BA.SUM_COL007 UNION ALL

  SELECT 'COL008', BA.SUM_COL008 UNION ALL

  SELECT 'COL009', BA.SUM_COL009 UNION ALL

  SELECT 'COL010', BA.SUM_COL010 UNION ALL

  SELECT 'COL011', BA.SUM_COL011 UNION ALL

  SELECT 'COL012', BA.SUM_COL012 UNION ALL

  SELECT 'COL013', BA.SUM_COL013 UNION ALL

  SELECT 'COL014', BA.SUM_COL014 UNION ALL

  SELECT 'COL015', BA.SUM_COL015 UNION ALL

  SELECT 'COL016', BA.SUM_COL016 UNION ALL

  SELECT 'COL017', BA.SUM_COL017 UNION ALL

  SELECT 'COL018', BA.SUM_COL018 UNION ALL

  SELECT 'COL019', BA.SUM_COL019 UNION ALL

  SELECT 'COL020', BA.SUM_COL020

  ) AS X(COL,VAL)

  OPTION (MAXDOP 1);

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');

  ;WITH BASE_AGGREGATE AS

  (

  SELECT

  SUM(TUA.COL001) AS SUM_COL001

  ,SUM(TUA.COL002) AS SUM_COL002

  ,SUM(TUA.COL003) AS SUM_COL003

  ,SUM(TUA.COL004) AS SUM_COL004

  ,SUM(TUA.COL005) AS SUM_COL005

  ,SUM(TUA.COL006) AS SUM_COL006

  ,SUM(TUA.COL007) AS SUM_COL007

  ,SUM(TUA.COL008) AS SUM_COL008

  ,SUM(TUA.COL009) AS SUM_COL009

  ,SUM(TUA.COL010) AS SUM_COL010

  ,SUM(TUA.COL011) AS SUM_COL011

  ,SUM(TUA.COL012) AS SUM_COL012

  ,SUM(TUA.COL013) AS SUM_COL013

  ,SUM(TUA.COL014) AS SUM_COL014

  ,SUM(TUA.COL015) AS SUM_COL015

  ,SUM(TUA.COL016) AS SUM_COL016

  ,SUM(TUA.COL017) AS SUM_COL017

  ,SUM(TUA.COL018) AS SUM_COL018

  ,SUM(TUA.COL019) AS SUM_COL019

  ,SUM(TUA.COL020) AS SUM_COL020

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  )

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = X.VAL

  FROM BASE_AGGREGATE BA

  CROSS APPLY

  (

  SELECT 'COL001', BA.SUM_COL001 UNION ALL

  SELECT 'COL002', BA.SUM_COL002 UNION ALL

  SELECT 'COL003', BA.SUM_COL003 UNION ALL

  SELECT 'COL004', BA.SUM_COL004 UNION ALL

  SELECT 'COL005', BA.SUM_COL005 UNION ALL

  SELECT 'COL006', BA.SUM_COL006 UNION ALL

  SELECT 'COL007', BA.SUM_COL007 UNION ALL

  SELECT 'COL008', BA.SUM_COL008 UNION ALL

  SELECT 'COL009', BA.SUM_COL009 UNION ALL

  SELECT 'COL010', BA.SUM_COL010 UNION ALL

  SELECT 'COL011', BA.SUM_COL011 UNION ALL

  SELECT 'COL012', BA.SUM_COL012 UNION ALL

  SELECT 'COL013', BA.SUM_COL013 UNION ALL

  SELECT 'COL014', BA.SUM_COL014 UNION ALL

  SELECT 'COL015', BA.SUM_COL015 UNION ALL

  SELECT 'COL016', BA.SUM_COL016 UNION ALL

  SELECT 'COL017', BA.SUM_COL017 UNION ALL

  SELECT 'COL018', BA.SUM_COL018 UNION ALL

  SELECT 'COL019', BA.SUM_COL019 UNION ALL

  SELECT 'COL020', BA.SUM_COL020

  ) AS X(COL,VAL)

  OPTION (MAXDOP 0);

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = SUM(X.VAL)

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  CROSS APPLY

  (

  SELECT 'COL001', COL001 UNION ALL

  SELECT 'COL002', COL002 UNION ALL

  SELECT 'COL003', COL003 UNION ALL

  SELECT 'COL004', COL004 UNION ALL

  SELECT 'COL005', COL005 UNION ALL

  SELECT 'COL006', COL006 UNION ALL

  SELECT 'COL007', COL007 UNION ALL

  SELECT 'COL008', COL008 UNION ALL

  SELECT 'COL009', COL009 UNION ALL

  SELECT 'COL010', COL010 UNION ALL

  SELECT 'COL011', COL011 UNION ALL

  SELECT 'COL012', COL012 UNION ALL

  SELECT 'COL013', COL013 UNION ALL

  SELECT 'COL014', COL014 UNION ALL

  SELECT 'COL015', COL015 UNION ALL

  SELECT 'COL016', COL016 UNION ALL

  SELECT 'COL017', COL017 UNION ALL

  SELECT 'COL018', COL018 UNION ALL

  SELECT 'COL019', COL019 UNION ALL

  SELECT 'COL020', COL020

  ) AS X(COL,VAL)

  GROUP BY X.COL

  OPTION (MAXDOP 1);

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = SUM(X.VAL)

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  CROSS APPLY

  (

  SELECT 'COL001', COL001 UNION ALL

  SELECT 'COL002', COL002 UNION ALL

  SELECT 'COL003', COL003 UNION ALL

  SELECT 'COL004', COL004 UNION ALL

  SELECT 'COL005', COL005 UNION ALL

  SELECT 'COL006', COL006 UNION ALL

  SELECT 'COL007', COL007 UNION ALL

  SELECT 'COL008', COL008 UNION ALL

  SELECT 'COL009', COL009 UNION ALL

  SELECT 'COL010', COL010 UNION ALL

  SELECT 'COL011', COL011 UNION ALL

  SELECT 'COL012', COL012 UNION ALL

  SELECT 'COL013', COL013 UNION ALL

  SELECT 'COL014', COL014 UNION ALL

  SELECT 'COL015', COL015 UNION ALL

  SELECT 'COL016', COL016 UNION ALL

  SELECT 'COL017', COL017 UNION ALL

  SELECT 'COL018', COL018 UNION ALL

  SELECT 'COL019', COL019 UNION ALL

  SELECT 'COL020', COL020

  ) AS X(COL,VAL)

  GROUP BY X.COL

  OPTION (MAXDOP 0);

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');

  SELECT

  T.T_TXT

  ,MAX(T.T_CPU) - MIN(T.T_CPU) AS CPU

  ,MAX(T.T_IDLE) - MIN(T.T_IDLE) AS IDLE

  ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

  FROM @timer T

  GROUP BY T.T_TXT

  ORDER BY DURATION;

  Output on my old i5 laptop

  T_TXT CPU IDLE DURATION

  --------------------- ----------- ---------- -----------

  PRE AGG MD 0 1343750 531250 464044

  DRY RUN 625000 1875000 671516

  PRE AGG MD 1 812500 2437500 838622

  POST AGG MD 0 15562500 4937500 5231409

  DATA SET GENERATION 8250000 24718750 8476825

  POST AGG MD 1 14375000 31468750 11743607

• tripleAxe

  SSCertifiable

  Points: 5706

  More actions

  September 15, 2016 at 3:11 am

  #1901049

  Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with the CROSS APPLY query taking 15 seconds.

  The queries used are below.

  The UNION ALL query

  SELECT 'AZ' [STATE], SUM(AZ) [VAL] FROM EXAMPLE

  UNION ALL

  SELECT 'NY' [STATE], SUM(NY) [VAL] FROM EXAMPLE

  UNION ALL

  SELECT 'PA' [STATE], SUM(PA) [VAL] FROM EXAMPLE

  The CROSS APPLY query

  SELECT

  x.State,

  SumValue = SUM(x.Value)

  FROM

  Example e

  CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)

  GROUP BY

  x.State;

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  September 15, 2016 at 4:12 am

  #1901054

  Added the Union All to the test harness

  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  --http://www.sqlservercentral.com/Forums/FindPost1817858.aspx

  -- TEST HARNESS

  DECLARE @timer TABLE (

  T_TXT VARCHAR(50) NOT NULL

  ,T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())

  ,T_CPU FLOAT NOT NULL DEFAULT (@@CPU_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))

  ,T_IDLE FLOAT NOT NULL DEFAULT (@@IDLE * CONVERT(FLOAT,@@TIMETICKS,0))

  );

  --/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATA SET CREATION

  INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');

  IF OBJECT_ID(N'dbo.TBL_TEST_UNPIVOT_AGGREGATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE;

  CREATE TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE

  (

  ROW_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_TEST_UNPIVOT_AGGREGATE_ROW_ID PRIMARY KEY CLUSTERED

  ,COL001 INT NOT NULL

  ,COL002 INT NOT NULL

  ,COL003 INT NOT NULL

  ,COL004 INT NOT NULL

  ,COL005 INT NOT NULL

  ,COL006 INT NOT NULL

  ,COL007 INT NOT NULL

  ,COL008 INT NOT NULL

  ,COL009 INT NOT NULL

  ,COL010 INT NOT NULL

  ,COL011 INT NOT NULL

  ,COL012 INT NOT NULL

  ,COL013 INT NOT NULL

  ,COL014 INT NOT NULL

  ,COL015 INT NOT NULL

  ,COL016 INT NOT NULL

  ,COL017 INT NOT NULL

  ,COL018 INT NOT NULL

  ,COL019 INT NOT NULL

  ,COL020 INT NOT NULL

  );

  -- Set the data sample's parameters

  DECLARE @SAMPLE_SIZE INT = 1000000;

  DECLARE @MAX_VALUE INT = 1025;

  ;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) AS N

  FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

  INSERT INTO dbo.TBL_TEST_UNPIVOT_AGGREGATE WITH (TABLOCK)

  (

  ROW_ID,COL001,COL002,COL003,COL004,COL005,COL006,COL007,COL008,COL009,COL010

  ,COL011,COL012,COL013,COL014,COL015,COL016,COL017,COL018,COL019,COL020

  )

  SELECT

  NM.N

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  ,ABS(CHECKSUM(NEWID())) % @MAX_VALUE

  FROM NUMS NM;

  INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');

  -- */

  DECLARE @INT_BUCKET INT = 0;

  DECLARE @VCHAR_BUCKET VARCHAR(12) = '';

  INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

  SELECT

  @INT_BUCKET = TUA.ROW_ID

  ,@INT_BUCKET = TUA.COL001

  ,@INT_BUCKET = TUA.COL002

  ,@INT_BUCKET = TUA.COL003

  ,@INT_BUCKET = TUA.COL004

  ,@INT_BUCKET = TUA.COL005

  ,@INT_BUCKET = TUA.COL006

  ,@INT_BUCKET = TUA.COL007

  ,@INT_BUCKET = TUA.COL008

  ,@INT_BUCKET = TUA.COL009

  ,@INT_BUCKET = TUA.COL010

  ,@INT_BUCKET = TUA.COL011

  ,@INT_BUCKET = TUA.COL012

  ,@INT_BUCKET = TUA.COL013

  ,@INT_BUCKET = TUA.COL014

  ,@INT_BUCKET = TUA.COL015

  ,@INT_BUCKET = TUA.COL016

  ,@INT_BUCKET = TUA.COL017

  ,@INT_BUCKET = TUA.COL018

  ,@INT_BUCKET = TUA.COL019

  ,@INT_BUCKET = TUA.COL020

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA;

  INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');

  ;WITH BASE_AGGREGATE AS

  (

  SELECT

  SUM(TUA.COL001) AS SUM_COL001

  ,SUM(TUA.COL002) AS SUM_COL002

  ,SUM(TUA.COL003) AS SUM_COL003

  ,SUM(TUA.COL004) AS SUM_COL004

  ,SUM(TUA.COL005) AS SUM_COL005

  ,SUM(TUA.COL006) AS SUM_COL006

  ,SUM(TUA.COL007) AS SUM_COL007

  ,SUM(TUA.COL008) AS SUM_COL008

  ,SUM(TUA.COL009) AS SUM_COL009

  ,SUM(TUA.COL010) AS SUM_COL010

  ,SUM(TUA.COL011) AS SUM_COL011

  ,SUM(TUA.COL012) AS SUM_COL012

  ,SUM(TUA.COL013) AS SUM_COL013

  ,SUM(TUA.COL014) AS SUM_COL014

  ,SUM(TUA.COL015) AS SUM_COL015

  ,SUM(TUA.COL016) AS SUM_COL016

  ,SUM(TUA.COL017) AS SUM_COL017

  ,SUM(TUA.COL018) AS SUM_COL018

  ,SUM(TUA.COL019) AS SUM_COL019

  ,SUM(TUA.COL020) AS SUM_COL020

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  )

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = X.VAL

  FROM BASE_AGGREGATE BA

  CROSS APPLY

  (

  SELECT 'COL001', BA.SUM_COL001 UNION ALL

  SELECT 'COL002', BA.SUM_COL002 UNION ALL

  SELECT 'COL003', BA.SUM_COL003 UNION ALL

  SELECT 'COL004', BA.SUM_COL004 UNION ALL

  SELECT 'COL005', BA.SUM_COL005 UNION ALL

  SELECT 'COL006', BA.SUM_COL006 UNION ALL

  SELECT 'COL007', BA.SUM_COL007 UNION ALL

  SELECT 'COL008', BA.SUM_COL008 UNION ALL

  SELECT 'COL009', BA.SUM_COL009 UNION ALL

  SELECT 'COL010', BA.SUM_COL010 UNION ALL

  SELECT 'COL011', BA.SUM_COL011 UNION ALL

  SELECT 'COL012', BA.SUM_COL012 UNION ALL

  SELECT 'COL013', BA.SUM_COL013 UNION ALL

  SELECT 'COL014', BA.SUM_COL014 UNION ALL

  SELECT 'COL015', BA.SUM_COL015 UNION ALL

  SELECT 'COL016', BA.SUM_COL016 UNION ALL

  SELECT 'COL017', BA.SUM_COL017 UNION ALL

  SELECT 'COL018', BA.SUM_COL018 UNION ALL

  SELECT 'COL019', BA.SUM_COL019 UNION ALL

  SELECT 'COL020', BA.SUM_COL020

  ) AS X(COL,VAL)

  OPTION (MAXDOP 1);

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');

  ;WITH BASE_AGGREGATE AS

  (

  SELECT

  SUM(TUA.COL001) AS SUM_COL001

  ,SUM(TUA.COL002) AS SUM_COL002

  ,SUM(TUA.COL003) AS SUM_COL003

  ,SUM(TUA.COL004) AS SUM_COL004

  ,SUM(TUA.COL005) AS SUM_COL005

  ,SUM(TUA.COL006) AS SUM_COL006

  ,SUM(TUA.COL007) AS SUM_COL007

  ,SUM(TUA.COL008) AS SUM_COL008

  ,SUM(TUA.COL009) AS SUM_COL009

  ,SUM(TUA.COL010) AS SUM_COL010

  ,SUM(TUA.COL011) AS SUM_COL011

  ,SUM(TUA.COL012) AS SUM_COL012

  ,SUM(TUA.COL013) AS SUM_COL013

  ,SUM(TUA.COL014) AS SUM_COL014

  ,SUM(TUA.COL015) AS SUM_COL015

  ,SUM(TUA.COL016) AS SUM_COL016

  ,SUM(TUA.COL017) AS SUM_COL017

  ,SUM(TUA.COL018) AS SUM_COL018

  ,SUM(TUA.COL019) AS SUM_COL019

  ,SUM(TUA.COL020) AS SUM_COL020

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  )

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = X.VAL

  FROM BASE_AGGREGATE BA

  CROSS APPLY

  (

  SELECT 'COL001', BA.SUM_COL001 UNION ALL

  SELECT 'COL002', BA.SUM_COL002 UNION ALL

  SELECT 'COL003', BA.SUM_COL003 UNION ALL

  SELECT 'COL004', BA.SUM_COL004 UNION ALL

  SELECT 'COL005', BA.SUM_COL005 UNION ALL

  SELECT 'COL006', BA.SUM_COL006 UNION ALL

  SELECT 'COL007', BA.SUM_COL007 UNION ALL

  SELECT 'COL008', BA.SUM_COL008 UNION ALL

  SELECT 'COL009', BA.SUM_COL009 UNION ALL

  SELECT 'COL010', BA.SUM_COL010 UNION ALL

  SELECT 'COL011', BA.SUM_COL011 UNION ALL

  SELECT 'COL012', BA.SUM_COL012 UNION ALL

  SELECT 'COL013', BA.SUM_COL013 UNION ALL

  SELECT 'COL014', BA.SUM_COL014 UNION ALL

  SELECT 'COL015', BA.SUM_COL015 UNION ALL

  SELECT 'COL016', BA.SUM_COL016 UNION ALL

  SELECT 'COL017', BA.SUM_COL017 UNION ALL

  SELECT 'COL018', BA.SUM_COL018 UNION ALL

  SELECT 'COL019', BA.SUM_COL019 UNION ALL

  SELECT 'COL020', BA.SUM_COL020

  ) AS X(COL,VAL)

  OPTION (MAXDOP 0);

  INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = SUM(X.VAL)

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  CROSS APPLY

  (

  SELECT 'COL001', COL001 UNION ALL

  SELECT 'COL002', COL002 UNION ALL

  SELECT 'COL003', COL003 UNION ALL

  SELECT 'COL004', COL004 UNION ALL

  SELECT 'COL005', COL005 UNION ALL

  SELECT 'COL006', COL006 UNION ALL

  SELECT 'COL007', COL007 UNION ALL

  SELECT 'COL008', COL008 UNION ALL

  SELECT 'COL009', COL009 UNION ALL

  SELECT 'COL010', COL010 UNION ALL

  SELECT 'COL011', COL011 UNION ALL

  SELECT 'COL012', COL012 UNION ALL

  SELECT 'COL013', COL013 UNION ALL

  SELECT 'COL014', COL014 UNION ALL

  SELECT 'COL015', COL015 UNION ALL

  SELECT 'COL016', COL016 UNION ALL

  SELECT 'COL017', COL017 UNION ALL

  SELECT 'COL018', COL018 UNION ALL

  SELECT 'COL019', COL019 UNION ALL

  SELECT 'COL020', COL020

  ) AS X(COL,VAL)

  GROUP BY X.COL

  OPTION (MAXDOP 1);

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');

  SELECT

  @VCHAR_BUCKET = X.COL

  ,@INT_BUCKET = SUM(X.VAL)

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  CROSS APPLY

  (

  SELECT 'COL001', COL001 UNION ALL

  SELECT 'COL002', COL002 UNION ALL

  SELECT 'COL003', COL003 UNION ALL

  SELECT 'COL004', COL004 UNION ALL

  SELECT 'COL005', COL005 UNION ALL

  SELECT 'COL006', COL006 UNION ALL

  SELECT 'COL007', COL007 UNION ALL

  SELECT 'COL008', COL008 UNION ALL

  SELECT 'COL009', COL009 UNION ALL

  SELECT 'COL010', COL010 UNION ALL

  SELECT 'COL011', COL011 UNION ALL

  SELECT 'COL012', COL012 UNION ALL

  SELECT 'COL013', COL013 UNION ALL

  SELECT 'COL014', COL014 UNION ALL

  SELECT 'COL015', COL015 UNION ALL

  SELECT 'COL016', COL016 UNION ALL

  SELECT 'COL017', COL017 UNION ALL

  SELECT 'COL018', COL018 UNION ALL

  SELECT 'COL019', COL019 UNION ALL

  SELECT 'COL020', COL020

  ) AS X(COL,VAL)

  GROUP BY X.COL

  OPTION (MAXDOP 0);

  INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');

  INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');

  ;WITH BASE_DATA(COL,VAL) AS

  (

  SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL006', SUM(TUA.COL006) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL007', SUM(TUA.COL007) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL008', SUM(TUA.COL008) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL009', SUM(TUA.COL009) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL010', SUM(TUA.COL010) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL011', SUM(TUA.COL011) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL012', SUM(TUA.COL012) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL013', SUM(TUA.COL013) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL014', SUM(TUA.COL014) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL015', SUM(TUA.COL015) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL016', SUM(TUA.COL016) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL017', SUM(TUA.COL017) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL018', SUM(TUA.COL018) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL019', SUM(TUA.COL019) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL020', SUM(TUA.COL020) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  )

  SELECT

  @VCHAR_BUCKET = BD.COL

  ,@INT_BUCKET = BD.VAL

  FROM BASE_DATA BD

  OPTION (MAXDOP 0);

  INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');

  INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');

  ;WITH BASE_DATA(COL,VAL) AS

  (

  SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL006', SUM(TUA.COL006) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL007', SUM(TUA.COL007) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL008', SUM(TUA.COL008) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL009', SUM(TUA.COL009) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL010', SUM(TUA.COL010) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL011', SUM(TUA.COL011) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL012', SUM(TUA.COL012) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL013', SUM(TUA.COL013) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL014', SUM(TUA.COL014) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL015', SUM(TUA.COL015) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL016', SUM(TUA.COL016) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL017', SUM(TUA.COL017) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL018', SUM(TUA.COL018) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL019', SUM(TUA.COL019) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL

  SELECT 'COL020', SUM(TUA.COL020) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  )

  SELECT

  @VCHAR_BUCKET = BD.COL

  ,@INT_BUCKET = BD.VAL

  FROM BASE_DATA BD

  OPTION (MAXDOP 1);

  INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');

  SELECT

  T.T_TXT

  ,MAX(T.T_CPU) - MIN(T.T_CPU) AS CPU

  ,MAX(T.T_IDLE) - MIN(T.T_IDLE) AS IDLE

  ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

  FROM @timer T

  GROUP BY T.T_TXT

  ORDER BY DURATION;

  Results on my laptop

  T_TXT CPU IDLE DURATION

  --------------------- ---------- --------- -----------

  PRE AGG MD 0 1375000 125000 392169

  DRY RUN 531250 1593750 550086

  PRE AGG MD 1 750000 2125000 750075

  UNION ALL MD 0 5312500 312500 1447305

  UNION ALL MD 1 2531250 7593750 2600886

  POST AGG MD 0 15687500 437500 4112645

  DATA SET GENERATION 6656250 19843750 6783290

  POST AGG MD 1 12187500 24031250 9280401

• tripleAxe

  SSCertifiable

  Points: 5706

  More actions

  September 15, 2016 at 6:18 am

  #1901077

  Here is the UNION ALL query using a temp table so that we can have one pass at the input table as suggested by The Dixie Flatline in an earlier post.

  INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 2');

  CREATE TABLE ##RESULT (

  COL001 INT

  ,COL002 INT

  ,COL003 INT

  ,COL004 INT

  ,COL005 INT

  ,COL006 INT

  ,COL007 INT

  ,COL008 INT

  ,COL009 INT

  ,COL010 INT

  ,COL011 INT

  ,COL012 INT

  ,COL013 INT

  ,COL014 INT

  ,COL015 INT

  ,COL016 INT

  ,COL017 INT

  ,COL018 INT

  ,COL019 INT

  ,COL020 INT

  )

  INSERT INTO ##RESULT

  SELECT

  SUM(TUA.COL001)

  , SUM(TUA.COL002)

  , SUM(TUA.COL003)

  , SUM(TUA.COL004)

  , SUM(TUA.COL005)

  , SUM(TUA.COL006)

  , SUM(TUA.COL007)

  , SUM(TUA.COL008)

  , SUM(TUA.COL009)

  , SUM(TUA.COL010)

  , SUM(TUA.COL011)

  , SUM(TUA.COL012)

  , SUM(TUA.COL013)

  , SUM(TUA.COL014)

  , SUM(TUA.COL015)

  , SUM(TUA.COL016)

  , SUM(TUA.COL017)

  , SUM(TUA.COL018)

  , SUM(TUA.COL019)

  , SUM(TUA.COL020)

  FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA

  ;WITH BASE_DATA(COL,VAL) AS

  (

  SELECT 'COL001', COL001 FROM ##RESULT UNION ALL

  SELECT 'COL002', COL002 FROM ##RESULT UNION ALL

  SELECT 'COL003', COL003 FROM ##RESULT UNION ALL

  SELECT 'COL004', COL004 FROM ##RESULT UNION ALL

  SELECT 'COL005', COL005 FROM ##RESULT UNION ALL

  SELECT 'COL006', COL006 FROM ##RESULT UNION ALL

  SELECT 'COL007', COL007 FROM ##RESULT UNION ALL

  SELECT 'COL008', COL008 FROM ##RESULT UNION ALL

  SELECT 'COL009', COL009 FROM ##RESULT UNION ALL

  SELECT 'COL010', COL010 FROM ##RESULT UNION ALL

  SELECT 'COL011', COL011 FROM ##RESULT UNION ALL

  SELECT 'COL012', COL012 FROM ##RESULT UNION ALL

  SELECT 'COL013', COL013 FROM ##RESULT UNION ALL

  SELECT 'COL014', COL014 FROM ##RESULT UNION ALL

  SELECT 'COL015', COL015 FROM ##RESULT UNION ALL

  SELECT 'COL016', COL016 FROM ##RESULT UNION ALL

  SELECT 'COL017', COL017 FROM ##RESULT UNION ALL

  SELECT 'COL018', COL018 FROM ##RESULT UNION ALL

  SELECT 'COL019', COL019 FROM ##RESULT UNION ALL

  SELECT 'COL020', COL020 FROM ##RESULT

  )

  SELECT

  @VCHAR_BUCKET = BD.COL

  ,@INT_BUCKET = BD.VAL

  FROM BASE_DATA BD

  OPTION (MAXDOP 1);

  INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 2');

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  September 15, 2016 at 7:53 am

  #1901115

  Eirikur Eiriksson (9/15/2016)

  ---

  Here is a simple test harness which you can use

  Again, another example of multiple senior contributors moving the goal posts farther away to better explore the subject, even though the original question has already amply been answered.

  I LOVE this forum.

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  September 15, 2016 at 7:56 am

  #1901116

  tripleAxe (9/15/2016)

  ---

  Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with the CROSS APPLY query taking 15 seconds.

  You have volunteered to do what I was too lazy to do.

  Funny how the easier-on-the-eye UNION ALL turned out to be more efficient than the "higher-level" CROSS JOIN.

  This forum definitely ROCKS !

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  September 15, 2016 at 8:16 am

  #1901119

  j-1064772 (9/15/2016)

  ---

  tripleAxe (9/15/2016)

  ---

  Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with the CROSS APPLY query taking 15 seconds.

  You have volunteered to do what I was too lazy to do.

  Funny how the easier-on-the-eye UNION ALL turned out to be more efficient than the "higher-level" CROSS JOIN.

  This forum definitely ROCKS !

  Except that Eirikur's findings contradict that. Of course, Eirikur also used 20 columns in his testing, and I suspect that that is the reason for his different findings. It's clear that the more columns you are pivoting, the more times you need to scan the table using the UNION ALL method, and it is that cost that eventually causes the UNION ALL to lose to the CROSS APPLY.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 15, 2016 at 8:22 am

  #1901120

  j-1064772 (9/15/2016)

  ---

  Eirikur Eiriksson (9/15/2016)

  ---

  Here is a simple test harness which you can use

  Again, another example of multiple senior contributors moving the goal posts farther away to better explore the subject, even though the original question has already amply been answered.

  I LOVE this forum.

  +1M!

  I have loaded this one up into Evernote for deeper study and to put it into my brain for future use.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  September 15, 2016 at 9:11 am

  #1901147

  tripleAxe (9/15/2016)

  ---

  Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with the CROSS APPLY query taking 15 seconds.

  The queries used are below.

  The UNION ALL query

  SELECT 'AZ' [STATE], SUM(AZ) [VAL] FROM EXAMPLE

  UNION ALL

  SELECT 'NY' [STATE], SUM(NY) [VAL] FROM EXAMPLE

  UNION ALL

  SELECT 'PA' [STATE], SUM(PA) [VAL] FROM EXAMPLE

  The CROSS APPLY query

  SELECT

  x.State,

  SumValue = SUM(x.Value)

  FROM

  Example e

  CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)

  GROUP BY

  x.State;

  What did you use to generate the 12 million rows? Without that, no one can duplicate your tests.

  --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)
  

• tripleAxe

  SSCertifiable

  Points: 5706

  More actions

  September 15, 2016 at 9:23 am

  #1901153

  I used some of the code from Eirikur's test harness script.

• tripleAxe

  SSCertifiable

  Points: 5706

  More actions

  September 15, 2016 at 9:49 am

  #1901161

  Specifically, I changed the parameters in the data generation section to these below and then used this table to populate the original example table.

  -- Set the data sample's parameters

  DECLARE @SAMPLE_SIZE INT = 12000000;

  DECLARE @MAX_VALUE INT = 50;

  INSERT INTO Example(AZ, NY, PA) SELECT COL001,COL002,COL003 FROM TEEST.dbo.TBL_TEST_UNPIVOT_AGGREGATE