[SOLVED] Optimizing pivot/unpivot & transform queries SQL

I need advise how to optimize my query / my pivot/unpivot actions as it's taking a lot of time for this procedure to run.

Basically I have data with a lot of values in columns. Value data is split by p&l lines (sales, units etc.), moreover split by type baseline & promo. My row data contains a column data type - 1 & 2, which for example shows 1 as plan and 2 as actual. So i.e. on value sales, I have row with data type 1 which shows plan baseline sales & plan promo sales, data type 2 shows actual baseline & actual sales. And so on with other values.

All data is separated by chain_id (customer) & int_id (promo request).

By testing the procedure I discovered that pivot/unpivot takes 90% of query time, making it run for all customers/promo requests more than 5 minutes, which is something I want to bring down to minimum possible required time. At full scope I have 120 value columns (final columns incl. all p&L lines & variances) for each rowset & around 15k row sets

I prepared sample table & included just a few value columns for easier understanding

GO
BEGIN TRANSACTION
DROP TABLE IF EXISTS teststuffsample
CREATE TABLE teststuffsample (chain_id int,int_id int,data_type int,brand_id int,bndl_id int,product_id int,p_month int,bs_units float,bs_fix float,bs_gts float,bs_distr float,bs_chain float,units float,fix float,gts float,distr float,chain float,dsc_abs float,srv float)
INSERT INTO teststuffsample (chain_id,int_id,data_type,brand_id,bndl_id,product_id,p_month,bs_units,bs_fix,bs_gts,bs_distr,bs_chain,units,fix,gts,distr,chain,dsc_abs,srv)
VALUES
('1492','1044','1','133','0','216','10','484.610355961642','144413.886076569','158952.196755419','7152.84885399383','0','1566','466668','513648','23114.16','0','0','135000'),

('1492','1044','1','161','0','217','10','367.465235962632','345784.787040837','345784.787040837','15560.3154168377','0','1071','1007811','1007811','45351.495','0','0','135000'),

('1492','1044','1','174','0','223','10','32.5912503278301','18446.6476855518','18446.6476855518','830.099145849833','0','115','65090','65090','2929.05','0','0','11662.4439085442'),

('1492','1044','1','174','0','224','10','124.621894496795','73651.5396476058','81502.7190009039','3667.62235504068','0','241.5','142726.5','157941','7107.345','0','0','25572.8959980463'),

('1492','1044','1','174','0','225','10','18.0749312628939','10230.4110947979','10230.4110947979','460.368499265908','0','69','39054','39054','1757.43','0','0','6997.46634512653'),

('1492','1044','1','174','0','226','10','23.7828042932815','14055.6373373294','15553.9540078061','699.927930351275','0','69','40779','45126','2030.67','0','0','7306.54171372753'),

('1492','1044','1','174','0','227','10','5.78170671318688','6423.47615835062','7371.67605931327','331.725422669097','0','17.25','19164.75','21993.75','989.71875','0','0','3433.82734515706'),

('1492','1044','1','174','0','228','10','11.8454479001878','13160.2926171086','15102.9460727394','679.632573273275','0','28.75','31941.25','36656.25','1649.53125','0','0','5723.04557526176'),

('1492','1044','1','174','0','229','10','27.0753094861434','30080.6688391053','34521.0195948328','1553.44588176748','0','63.25','70270.75','80643.75','3628.96875','0','0','12590.7002655759'),

('1492','1044','1','174','0','230','10','98.1613226090106','55559.3085967','55559.3085967','2500.1688868515','0','230','130180','130180','5858.1','0','0','23324.8878170884'),

('1492','1044','1','174','0','231','10','55.6341451542095','32879.7797861378','36384.730930853','1637.31289188839','0','115','67965','75210','3384.45','0','0','12177.5695228792'),

('1492','1044','1','174','0','232','10','158.058357207257','173706.134570775','184454.102860869','8300.4346287391','0','345','379155','402615','18117.675','0','0','67934.7660184987'),

('1492','1044','1','174','0','1116','10','17.9385737496933','19714.4925509129','20934.3155658921','942.044200465144','0','46','50554','53682','2415.69','0','0','9057.9688024665'),

('1492','1044','1','174','0','1250','10','78.9370914693898','149822.599608902','159137.17640229','7161.17293810304','0','172.5','327405','347760','15649.2','0','0','58662.5049604689'),

('1492','1044','1','174','0','1297','10','23.8689851946638','45303.3338994719','48119.8741524422','2165.3943368599','0','46','87308','92736','4173.12','0','0','15643.334656125'),

('1492','1044','1','174','0','1547','10','9.8942555467826','5451.73480627721','5689.1969394','256.013862273','0','23','12673','13225','595.125','0','0','2270.67370798864'),

('1492','1044','1','174','0','1548','10','0','0','0','0','0','11.5','7659','7659','344.655','0','0','1372.29463658842'),

('1492','1044','1','174','0','1549','10','8.61391256572434','4746.26582371411','4952.9997252915','222.884987638117','0','28.75','15841.25','16531.25','743.90625','0','0','2838.3421349858'),

('1492','1044','1','174','0','1550','10','10.825592819086','7209.84481751128','7209.84481751128','324.443016788007','0','28.75','19147.5','19147.5','861.6375','0','0','3430.73659147105'),

('1492','1044','1','182','0','1784','10','0','0','0','0','0','600','115800','139800','6291','0','0','40000'),

('1492','1044','1','183','0','65','10','165.577073674536','35433.4937663507','38082.7269451433','1713.72271253145','0','580','124120','133400','6003','0','0','35052.2781250853'),

('1492','1044','1','183','0','69','10','162.957340654596','23954.7290762256','25584.3024827716','1151.29361172472','0','846.8','124479.6','132947.6','5982.642','0','0','35153.8314542328'),

('1492','1044','1','183','0','1200','10','127.028030709007','38616.5213355381','40140.8577040462','1806.33859668208','0','580','176320','183280','8247.6','0','0','49793.8904206819'),

('1492','1044','2','133','0','216','10','484.610355961642','144413.886076569','158952.196755419','7152.84885399383','0','2993.96','892200.08','982018.88','44190.8496','0','0','135000'),

('1492','1044','2','161','0','217','10','367.465235962632','345784.787040837','345784.787040837','15560.3154168377','0','1322.09','1244086.69','1244086.69','55983.90105','0','0','135000'),

('1492','1044','2','174','0','223','10','32.5912503278301','18446.6476855518','18446.6476855518','830.099145849833','0','342.7','193968.2','193968.2','8728.569','0','0','14728.2427831696'),

('1492','1044','2','174','0','224','10','124.621894496795','73651.5396476058','81502.7190009039','3667.62235504068','0','478.4','282734.4','312873.6','14079.312','0','0','21468.3689715829'),

('1492','1044','2','174','0','225','10','18.0749312628939','10230.4110947979','10230.4110947979','460.368499265908','0','93.15','52722.9','52722.9','2372.5305','0','0','4003.31431354609'),

('1492','1044','2','174','0','226','10','23.7828042932815','14055.6373373294','15553.9540078061','699.927930351275','0','157.55','93112.05','103037.7','4636.6965','0','0','7070.11189689147'),

('1492','1044','2','174','0','227','10','5.78170671318688','6423.47615835062','7371.67605931327','331.725422669097','0','72.45','80491.95','92373.75','4156.81875','0','0','6111.8522607868'),

('1492','1044','2','174','0','228','10','11.8454479001878','13160.2926171086','15102.9460727394','679.632573273275','0','118.45','131597.95','151023.75','6796.06875','0','0','9992.39337874667'),

('1492','1044','2','174','0','229','10','27.0753094861434','30080.6688391053','34521.0195948328','1553.44588176748','0','372.6','413958.6','475065','21377.925','0','0','31432.383055475'),

('1492','1044','2','174','0','230','10','98.1613226090106','55559.3085967','55559.3085967','2500.1688868515','0','524.4','296810.4','296810.4','13356.468','0','0','22537.1768762595'),

('1492','1044','2','174','0','231','10','55.6341451542095','32879.7797861378','36384.730930853','1637.31289188839','0','401.35','237197.85','262482.9','11811.7305','0','0','18010.7230074097'),

('1492','1044','2','174','0','232','10','158.058357207257','173706.134570775','184454.102860869','8300.4346287391','0','307.05','337447.95','358327.35','16124.73075','0','0','25622.8357755697'),

('1492','1044','2','174','0','1116','10','17.9385737496933','19714.4925509129','20934.3155658921','942.044200465144','0','71.3','78358.7','83207.1','3744.3195','0','0','5949.87197784766'),

('1492','1044','2','174','0','1250','10','78.9370914693898','149822.599608902','159137.17640229','7161.17293810304','0','428.95','814147.1','864763.2','38914.344','0','0','61819.1855676004'),

('1492','1044','2','174','0','1297','10','23.8689851946638','45303.3338994719','48119.8741524422','2165.3943368599','0','175.95','333953.1','354715.2','15962.184','0','0','25357.4675384527'),

('1492','1044','2','174','0','1547','10','9.8942555467826','5451.73480627721','5689.1969394','256.013862273','0','135.7','74770.7','78027.5','3511.2375','0','0','5677.43074724381'),

('1492','1044','2','174','0','1548','10','0','0','0','0','0','59.8','39826.8','39826.8','1792.206','0','0','3024.09765970266'),

('1492','1044','2','174','0','1549','10','8.61391256572434','4746.26582371411','4952.9997252915','222.884987638117','0','55.2','30415.2','31740','1428.3','0','0','2309.46335481104'),

('1492','1044','2','174','0','1550','10','10.825592819086','7209.84481751128','7209.84481751128','324.443016788007','0','96.6','64335.6','64335.6','2895.102','0','0','4885.0808349043'),

('1492','1044','2','182','0','1784','10','0','0','0','0','0','1929.6','372412.8','449596.8','20231.856','0','0','40000'),

('1492','1044','2','183','0','65','10','165.577073674536','35433.4937663507','38082.7269451433','1713.72271253145','0','1303.84','279021.76','299883.2','13494.744','0','0','38891.528278371'),

('1492','1044','2','183','0','69','10','162.957340654596','23954.7290762256','25584.3024827716','1151.29361172472','0','1295.72','190470.84','203428.04','9154.2618','0','0','26548.8328224475'),

('1492','1044','2','183','0','1200','10','127.028030709007','38616.5213355381','40140.8577040462','1806.33859668208','0','1287.6','391430.4','406881.6','18309.672','0','0','54559.6388991815'),

('1492','1054','1','161','0','218','6','6498.55801052056','2300489.53572428','2430460.69593469','109370.731317061','0','16065','5687010','6008310','270373.95','0','0','1282166.26422904'),

('1492','1054','1','163','0','1288','6','377.013858782708','76910.8271916724','100662.700294983','4529.82151327424','0','2562','522648','684054','30782.43','0','0','117833.735770956'),

('1492','1054','2','161','0','218','6','6498.55801052056','2300489.53572428','2430460.69593469','109370.731317061','0','20458.48','7242301.92','7651471.52','344316.2184','0','0','1206215.17107884'),

('1492','1054','2','163','0','1288','6','377.013858782708','76910.8271916724','100662.700294983','4529.82151327424','0','5703.5','1163514','1522834.5','68527.5525','0','0','193784.828921164'),

('1492','1055','1','174','0','232','6','680.335153258822','655843.087741504','763336.041956398','34350.1218880379','0','2242.5','2161770','2516085','113223.825','0','0','555000'),

('1492','1055','2','174','0','232','6','680.335153258822','655843.087741504','763336.041956398','34350.1218880379','0','1780.2','1716112.8','1997384.4','89882.298','0','0','555000'),

('1492','1057','1','163','0','1288','7','568.329475035592','115939.212907261','151743.969834503','6828.47864255264','0','8540','1742160','2280180','102608.1','0','0','700000'),

('1492','1057','2','163','0','1288','7','568.329475035592','115939.212907261','151743.969834503','6828.47864255264','0','9810.02','2001244.08','2619275.34','117867.3903','0','0','700000'),

('1492','1058','1','133','0','216','7','10714.8714639816','2925159.90966698','3439473.73993809','154776.318297214','0','22040','6016920','7074840','318367.8','0','380000','1023022.25374418'),

('1492','1058','1','174','0','232','7','875.139077089731','843634.070314501','981906.044494678','44185.7720022605','0','2300','2217200','2580600','116127','0','200000','376977.746255823'),

('1492','1058','2','133','0','216','7','10714.8714639816','2925159.90966698','3439473.73993809','154776.318297214','0','23614.12','6446654.76','7580132.52','341105.9634','0','339283.3','1068378.42545737'),

('1492','1058','2','174','0','232','7','875.139077089731','843634.070314501','981906.044494678','44185.7720022605','0','2075.75','2001023','2328991.5','104804.6175','0','150416.7','331621.574542635')

COMMIT TRANSACTION
GO

My procedure needs to calculate variances between Promo and baseline for plan & actual data with results stored in each separate column.

On small data sets with

Here's how my procedure looks like. You can create table in your db & launch this procedure, it should work fine:

drop table if exists #product
drop table if exists #value_types
drop table if exists #tmp
drop table if exists #tmp2
drop table if exists #tmp3

--for the sake of testing, I have stored procedure for this table
select *
into #product
from teststuffsample

--help table to cross apply & match with product table
create table #value_types
(
    ord int
    ,value_type nvarchar(13)
    ,base_type nvarchar(13)
    ,fc_ac nvarchar(13)
)
insert into #value_types (ord, value_type, base_type, fc_ac)
values  (1, 'units_nonprol','bs_units','units_nonprol'),
        (2, 'units','bs_units','units'),
        (3, 'coverage','coverage','coverage'),
        (4, 'fix','bs_fix','fix'),
        (5, 'gts','bs_gts','gts'),
        (6, 'distr','bs_distr','distr'),
        (7, 'chain','bs_chain','chain'),
        (8, 'dsc','bs_ttl_dsc','ttl_dsc'),
        (9, 'srv','bs_srv','srv'),
        (10, 'vat','bs_vat','ttl_vat'),
        (11, 'nts','bs_nts','nts'),
        (12, 'cogs','bs_cogs','cogs'),
        (13, 'gp','bs_gp','gp'),
        (14, 'bme','bs_ttl_bme','ttl_bme'),
        (15, 'bc','bs_bc','bc');

--here I convert initial multiple value columns to single column with value type & single value column
with i
as
(
    select t.chain_id
            ,t.int_id
            ,t.p_month
            ,t.data_type
            ,t.bndl_id
            ,t.brand_id
            ,t.product_id

            ,t.bs_units
            ,t.units


            ,t.bs_fix
            ,t.fix

            ,t.bs_gts
            ,t.gts

            ,t.bs_distr
            ,t.distr

            ,t.bs_chain
            ,t.chain

            ,cast(0 as float) as bs_srv
            ,t.srv

    from #product t
)
    select chain_id
            ,int_id
            ,p_month
            ,data_type
            ,bndl_id
            ,brand_id
            ,product_id
            ,value_type
            ,value
    into #tmp2
    from i
    unpivot
    (
    value
    for value_type in (bs_units,units,bs_fix,fix,bs_gts,gts,bs_distr,distr,bs_chain,chain,bs_srv,srv)
    ) piv;

--I create base table for each row combination & multiple it by all needed p&l lines from help table
select distinct t.chain_id
                ,t.int_id
                ,t.p_month
                ,t.bndl_id
                ,t.brand_id
                ,t.product_id
                ,cast(n.ord as nvarchar(2)) + '_' + n.value_type as val_type_show
                ,n.value_type
into #tmp
from #product t
cross apply #value_types n;

--Here i perform selective joins to add value columns to seggregate Baseline/Promo values to perform variance calculation for each p&l line
select t.*
        ,base_fc.value as 'Baseline FC'
        ,fc.value as 'Promo FC'
        ,fc.value - base_fc.value 'Promo FC vs Baseline FC'
        ,isnull(base_ac.value, base_fc.value) as 'Baseline AC/FC'
        ,isnull(ac.value, fc.value) as 'Promo AC/FC'
        ,isnull(ac.value, fc.value) - isnull(base_ac.value, base_fc.value) as 'Promo AC/FC vs Baseline AC/FC'
        ,isnull(base_ac.value, base_fc.value) - base_fc.value 'Baseline AC/FC vs Baseline FC'
        ,isnull(ac.value, fc.value) - fc.value 'Promo AC/FC vs Promo FC'
into #tmp3
from #tmp t
left join (select t.int_id
                ,t.p_month
                ,t.bndl_id
                ,t.brand_id
                ,t.product_id
                ,n.value_type
                ,t.value
            from #tmp2 t
            join #value_types n on n.base_type = t.value_type
            where n.value_type is not null and t.data_type = 1) base_fc on base_fc.int_id = t.int_id and
                                                                            base_fc.p_month = t.p_month and
                                                                            base_fc.bndl_id = t.bndl_id and
                                                                            base_fc.brand_id = t.brand_id and
                                                                            base_fc.product_id = t.product_id and
                                                                            base_fc.value_type = t.value_type
left join (select t.int_id
                ,t.p_month
                ,t.bndl_id
                ,t.brand_id
                ,t.product_id
                ,n.value_type
                ,t.value
            from #tmp2 t
            join #value_types n on n.fc_ac = t.value_type
            where n.value_type is not null and t.data_type = 1) fc on fc.int_id = t.int_id and
                                                                            fc.p_month = t.p_month and
                                                                            fc.bndl_id = t.bndl_id and
                                                                            fc.brand_id = t.brand_id and
                                                                            fc.product_id = t.product_id and
                                                                            fc.value_type = t.value_type
left join (select t.int_id
                ,t.p_month
                ,t.bndl_id
                ,t.brand_id
                ,t.product_id
                ,n.value_type
                ,t.value
            from #tmp2 t
            join #value_types n on n.base_type = t.value_type
            where n.value_type is not null and t.data_type = 2) base_ac on base_ac.int_id = t.int_id and
                                                                            base_ac.p_month = t.p_month and
                                                                            base_ac.bndl_id = t.bndl_id and
                                                                            base_ac.brand_id = t.brand_id and
                                                                            base_ac.product_id = t.product_id and
                                                                            base_ac.value_type = t.value_type
left join (select t.int_id
                ,t.p_month
                ,t.bndl_id
                ,t.brand_id
                ,t.product_id
                ,n.value_type
                ,t.value
            from #tmp2 t
            join #value_types n on n.fc_ac = t.value_type
            where n.value_type is not null and t.data_type = 2) ac on ac.int_id = t.int_id and
                                                                            ac.p_month = t.p_month and
                                                                            ac.bndl_id = t.bndl_id and
                                                                            ac.brand_id = t.brand_id and
                                                                            ac.product_id = t.product_id and
                                                                            ac.value_type = t.value_type;

--after calculating variances I unpivot variances columns to my value_type column & value column. So in my value type column I have all P&L lines + calculated variances
--after unpivoting I convert whole value_types back to each separate value column to achive set of 1 row - all values
select *
from (select chain_id
                    ,int_id
                    ,p_month
                    ,bndl_id
                    ,brand_id
                    ,product_id
                    ,col + ' ' + value_type as col
                    ,value
            from #tmp3
                unpivot
                (
                    value
                    for col in ([Baseline FC], [Promo FC], [Promo FC vs Baseline FC], [Baseline AC/FC], [Promo AC/FC], [Promo AC/FC vs Baseline AC/FC], [Baseline AC/FC vs Baseline FC], [Promo AC/FC vs Promo FC])
                ) pv
            ) t
pivot
    (
    max(value)
    for col in ([Baseline AC/FC units],[Baseline AC/FC fix],[Baseline AC/FC gts],[Baseline AC/FC distr],[Baseline AC/FC chain],[Baseline AC/FC dsc],[Baseline AC/FC srv],[Baseline AC/FC vs Baseline FC units],[Baseline AC/FC vs Baseline FC fix],[Baseline AC/FC vs Baseline FC gts],[Baseline AC/FC vs Baseline FC distr],[Baseline AC/FC vs Baseline FC chain],[Baseline AC/FC vs Baseline FC dsc],[Baseline AC/FC vs Baseline FC srv],[Baseline FC units],[Baseline FC fix],[Baseline FC gts],[Baseline FC distr],[Baseline FC chain],[Baseline FC dsc],[Baseline FC srv],[Promo AC/FC units],[Promo AC/FC fix],[Promo AC/FC gts],[Promo AC/FC distr],[Promo AC/FC chain],[Promo AC/FC dsc],[Promo AC/FC srv],[Promo AC/FC vs Baseline AC/FC units],[Promo AC/FC vs Baseline AC/FC fix],[Promo AC/FC vs Baseline AC/FC gts],[Promo AC/FC vs Baseline AC/FC distr],[Promo AC/FC vs Baseline AC/FC chain],[Promo AC/FC vs Baseline AC/FC dsc],[Promo AC/FC vs Baseline AC/FC srv],[Promo AC/FC vs Promo FC units],[Promo AC/FC vs Promo FC fix],[Promo AC/FC vs Promo FC gts],[Promo AC/FC vs Promo FC distr],[Promo AC/FC vs Promo FC chain],[Promo AC/FC vs Promo FC dsc],[Promo AC/FC vs Promo FC srv],[Promo FC units],[Promo FC fix],[Promo FC gts],[Promo FC distr],[Promo FC chain],[Promo FC dsc],[Promo FC srv],[Promo FC vs Baseline FC units],[Promo FC vs Baseline FC fix],[Promo FC vs Baseline FC gts],[Promo FC vs Baseline FC distr],[Promo FC vs Baseline FC chain],[Promo FC vs Baseline FC dsc],[Promo FC vs Baseline FC srv]
    )
) pvt

The preview of procedure result, as 1 rowset with all values stored in columns going to the right:

I understand my example & case telling might not be the easiest to understand from the go, but I really appreciate your time & advise on this case. Please ask any questions if something is not clear

Further edits:

deleted duplicate rows from sample data, messed up while preparing sample for this topic
adjusted cross apply to reference initial table rather than #tmp2 with much more data
2nd change allowed me to bring down procedure downtime before pivot/unpivot actions at the end to 4 seconds. tested on 14k input rows in product data & 120 value columns. The pivot/unpivot part takes the procedure time to 1:40 minutes, that's what I currently look for to decrease
changed nvarchar(max) to actual text length, got significant time decrease to 19 sec for whole dataset