Optimisation of unpivot/insert code

  • 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;

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

  • 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.

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

  • 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

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

  • 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

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply