none
Aggregated CASE expressions versus the PIVOT operator… Is one better than the other?

    Question

  • First let me preface this (for the Celko’s sake) by saying that I agree that pivoting data is a display function and is best done at the application layer or in reporting software like Crystal Reports or SSRS.

    That said, we do, on occasion, have a need to pivot or transpose data at the database level.  

    Like most of us I first learned how to pivot data using aggregated case expression in SQL Server 2000. Once we had all of our production servers were updated to 2005, I thought, “cool now I can use the new PIVOT operator…” Once I looked at it and saw what it did and how it worked, I was somewhat disappointed. The syntax was far more complicated and I didn’t see any performance gain in query execution.

    Sure there is the dynamic pivot… but unless you are copying and pasting results from SSMS to Excel, it’s pretty much useless. I don’t know of a front end application that can work with a variable number of columns with variable names (granted… just because I don’t know of any doesn’t mean they don’t exist).

    AS a test I ran and compared execution plans for the following queries. (Both based on the SQL Server 2005 AdventureWorks database)

    SELECT 
    ProductName,
    SUM(CASE WHEN OrderYear = 2001 THEN OrderQty END) AS [2001],
    SUM(CASE WHEN OrderYear = 2002 THEN OrderQty END) AS [2002],
    SUM(CASE WHEN OrderYear = 2003 THEN OrderQty END) AS [2003],
    SUM(CASE WHEN OrderYear = 2004 THEN OrderQty END) AS [2004]
    FROM (
    	SELECT 
    	p.[Name] AS ProductName,
    	sod.OrderQty,
    	DatePart(yy,soh.OrderDate) AS OrderYear
    	FROM Sales.SalesOrderDetail sod
    	INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
    	INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID) AS ps
    GROUP BY ProductName
    ORDER BY ProductName;
    

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

    SELECT 
    ProductName,
    [2001],
    [2002],
    [2003],
    [2004]
    FROM (
    	SELECT 
    	p.[Name] AS ProductName,
    	sod.OrderQty,
    	DatePart(yy,soh.OrderDate) AS OrderYear
    	FROM Sales.SalesOrderDetail sod
    	INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
    	INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID) AS ps
    PIVOT (
    	SUM(OrderQty)
    	FOR OrderYear IN ([2001],[2002],[2003],[2004])) AS piv
    ORDER BY ProductName;
    

    The two executions plans were almost identical and both executed in approximately 330 ms.

    My questions are…

    Is there any real reason to use or even learn to use the PIVOT operator?

    Am I missing something that would give the PIVOT an edge over aggregated CASE expressions?


    Jason Long
    Tuesday, August 02, 2011 7:50 PM

Answers

  • PIVOT is just a shorter /simpler syntax, other than that there is no reason to even learn / use it. For multi-columns pivot you still need to use case aggregate, so PIVOT is only useful when you're pivoting by one column.

    However, if you want to write more compact code, you will need to learn PIVOT (and there is nothing hard there - the syntax is very simple once you wrote a couple using PIVOT). I remember when I started it seemed hard to me also, but once I answered about 20 or more questions based on PIVOT, I learned it and now can write with my eyes shut. It's not yet the case for me with MERGE command, I still need to open BOL every time I'm writing a sample. But it gets easier every time.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 02, 2011 8:00 PM
    Moderator
  • The SUM / CASE method is a more general method and can be applied to all situations in which you might use pivot.  In addition, there are many situations in which PIVOT is either difficult to apply or turns out to be more complicated than the SUM/CASE method.

    Another problem with PIVOT is that it is not consistent between RDBMS platforms.  You will find problems going between Oracle and SQL Server with the PIVOT command and much fewer problems with the SUM/CASE syntax.  Also, SUM/CASE has been working since the previous millenium whereas PIVOT operator is more recently new.  I suppose that you can make a weak case that the SUM/CASE method is more venerable.

    The strength of the PIVOT operator is that it provides more compact looking code than SUM/CASE for simpler problems.

    EDIT:

    I would in general recommend the SUM/CASE method over the PIVOT method.  The problem that I have experienced many times with the PIVOT operator is that it leads to a "dead end".  What I mean by that is that you think that you might have a situation that is simple enough to apply the PIVOT operator over the SUM/CASE method only to find some problem with the solution using PIVOT and you end up switching to the SUM / CASE method anyway.


    Tuesday, August 02, 2011 8:04 PM
    Moderator
  • >> First let me preface this (for the Celko’s sake) by saying that I agree that pivoting data is a display function and is best done at the application layer or in reporting software like Crystal Reports or SSRS. <<

    Rats! I had a  great rant. :)-

    >> Is there any real reason to use or even learn to use the PIVOT operator? <,

    To read bad T-SQL written by a newbie who is trying to get job security with proprietary garbage. 

    >> Am I missing something that would give the PIVOT an edge over aggregated CASE expressions? <<

    Not that I know. In fact CASE/SUM has more power. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi NModerator Thursday, December 06, 2012 11:30 PM
    • Marked as answer by Jason A Long Tuesday, December 11, 2012 7:27 PM
    Thursday, December 06, 2012 11:00 PM

All replies

  • PIVOT is just a shorter /simpler syntax, other than that there is no reason to even learn / use it. For multi-columns pivot you still need to use case aggregate, so PIVOT is only useful when you're pivoting by one column.

    However, if you want to write more compact code, you will need to learn PIVOT (and there is nothing hard there - the syntax is very simple once you wrote a couple using PIVOT). I remember when I started it seemed hard to me also, but once I answered about 20 or more questions based on PIVOT, I learned it and now can write with my eyes shut. It's not yet the case for me with MERGE command, I still need to open BOL every time I'm writing a sample. But it gets easier every time.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 02, 2011 8:00 PM
    Moderator
  • The SUM / CASE method is a more general method and can be applied to all situations in which you might use pivot.  In addition, there are many situations in which PIVOT is either difficult to apply or turns out to be more complicated than the SUM/CASE method.

    Another problem with PIVOT is that it is not consistent between RDBMS platforms.  You will find problems going between Oracle and SQL Server with the PIVOT command and much fewer problems with the SUM/CASE syntax.  Also, SUM/CASE has been working since the previous millenium whereas PIVOT operator is more recently new.  I suppose that you can make a weak case that the SUM/CASE method is more venerable.

    The strength of the PIVOT operator is that it provides more compact looking code than SUM/CASE for simpler problems.

    EDIT:

    I would in general recommend the SUM/CASE method over the PIVOT method.  The problem that I have experienced many times with the PIVOT operator is that it leads to a "dead end".  What I mean by that is that you think that you might have a situation that is simple enough to apply the PIVOT operator over the SUM/CASE method only to find some problem with the solution using PIVOT and you end up switching to the SUM / CASE method anyway.


    Tuesday, August 02, 2011 8:04 PM
    Moderator
  • ... For multi-columns pivot you still need to use case aggregate, so PIVOT is only useful when you're pivoting by one column. ...

    This is not completely true; however, in this case I am tea totaling.  For all practical purposes, this might as well be true.  Also, multi-column pivots seem to be slightly easier in Oracle than in SQL server, but that doesn't really apply here either.

    Tuesday, August 02, 2011 8:15 PM
    Moderator
  • Naomi,

    I tried to keep my test queries as simple and straight forward as possible for the sake of making a true "apples to apples" comparison.

    As far as only being able to PIVOT on single columns, take a look at this... PIVOT Multiple Columns

    As far as compactness... It may be more compact but (for me) it's must more difficult to read. 

    I'll try try forcing myself to write more queries using the PIVOT operator before I make up my mind

     

    Kent,

    I think we are thinking along the same lines but it looks like part of your post is missing so I'm not sure I got your full thought...

    Edit: I see where you completed with the edit. Great info. Thank you.


    Jason Long
    Tuesday, August 02, 2011 8:18 PM
  • Jason,

    I don't see anything in your blog that contradicts my statement. I guess I should have said 'if you want a performant code for multi-columns PIVOT you need to use CASE based pivot', but this is exactly what I meant - thought it was obvious.

    BTW, you may find my blog post an interesting reading

    Dynamic PIVOT on multiple columns


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 02, 2011 8:24 PM
    Moderator
  • The blog shows how to pivot multiple multiple columns using multiple pivot operators, in the same query, and no case statements. Looking at your blog post, you are using SUM/CASE. I figured that you'd be interested in seeing it done with PIVOT.


    Jason Long
    Tuesday, August 02, 2011 8:45 PM
  • Right, the blog uses multiple PIVOT statements. To me it's obvious without even trying that this query will result in worse performance comparing with the one direct query using CASE statements. That's why I dismiss this idea right away of two or more pivot operations in one query.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 02, 2011 8:50 PM
    Moderator
  • My test results contradict with the discussion above.

    I am finding the performance is differrent using two methods. We have a huge pivoting query return 8G data. The group by query return in 40 min and the pivoting return in 6 min. I attached my query below. Any idea? 

    group by  query

    select period_dt, vendorid, ISNULL(sum(case riskfactor when 1022 then value else NULL end), 0) as GEM2L_airlines
    , ISNULL(sum(case riskfactor when 1046 then value else NULL end), 0) as GEM2L_arg , ISNULL(sum(case riskfactor when
    1101 then value else NULL end), 0) as GEM2L_argc , ISNULL(sum(case riskfactor when 1047 then value else NULL end),
    0) as GEM2L_aus , ISNULL(sum(case riskfactor when 1102 then value else NULL end), 0) as GEM2L_ausc ,
    ISNULL(sum(case riskfactor when 1048 then value else NULL end), 0) as GEM2L_aut , ISNULL(sum(case riskfactor when
    1103 then value else NULL end), 0) as GEM2L_autc , ISNULL(sum(case riskfactor when 1023 then value else NULL end),
    0) as GEM2L_autocomp , ISNULL(sum(case riskfactor when 1034 then value else NULL end), 0) as GEM2L_banks ,
    ISNULL(sum(case riskfactor when 1049 then value else NULL end), 0) as GEM2L_bel , ISNULL(sum(case riskfactor when
    1104 then value else NULL end), 0) as GEM2L_belc , ISNULL(sum(case riskfactor when 1032 then value else NULL end),
    0) as GEM2L_biotech , ISNULL(sum(case riskfactor when 1051 then value else NULL end), 0) as GEM2L_bra ,
    ISNULL(sum(case riskfactor when 1106 then value else NULL end), 0) as GEM2L_brac , ISNULL(sum(case riskfactor when
    1052 then value else NULL end), 0) as GEM2L_can , ISNULL(sum(case riskfactor when 1107 then value else NULL end),
    0) as GEM2L_canc , ISNULL(sum(case riskfactor when 1019 then value else NULL end), 0) as GEM2L_capgoods ,
    ISNULL(sum(case riskfactor when 1053 then value else NULL end), 0) as GEM2L_che , ISNULL(sum(case riskfactor when
    1108 then value else NULL end), 0) as GEM2L_chec , ISNULL(sum(case riskfactor when 1014 then value else NULL end),
    0) as GEM2L_chemical , ISNULL(sum(case riskfactor when 1054 then value else NULL end), 0) as GEM2L_chl ,
    ISNULL(sum(case riskfactor when 1109 then value else NULL end), 0) as GEM2L_chlc , ISNULL(sum(case riskfactor when
    1055 then value else NULL end), 0) as GEM2L_chn , ISNULL(sum(case riskfactor when 1110 then value else NULL end),
    0) as GEM2L_chnc , ISNULL(sum(case riskfactor when 1056 then value else NULL end), 0) as GEM2L_chx ,
    ISNULL(sum(case riskfactor when 1057 then value else NULL end), 0) as GEM2L_col , ISNULL(sum(case riskfactor when
    1111 then value else NULL end), 0) as GEM2L_colc , ISNULL(sum(case riskfactor when 1020 then value else NULL end),
    0) as GEM2L_commsvcs , ISNULL(sum(case riskfactor when 1040 then value else NULL end), 0) as GEM2L_communic ,
    ISNULL(sum(case riskfactor when 1041 then value else NULL end), 0) as GEM2L_computer , ISNULL(sum(case riskfactor
    when 1024 then value else NULL end), 0) as GEM2L_consdur , ISNULL(sum(case riskfactor when 1015 then value else
    NULL end), 0) as GEM2L_constpp , ISNULL(sum(case riskfactor when 1025 then value else NULL end), 0) as
    GEM2L_consvcs , ISNULL(sum(case riskfactor when 1058 then value else NULL end), 0) as GEM2L_cze , ISNULL(sum(case
    riskfactor when 1112 then value else NULL end), 0) as GEM2L_czec , ISNULL(sum(case riskfactor when 1059 then value
    else NULL end), 0) as GEM2L_deu , ISNULL(sum(case riskfactor when 1113 then value else NULL end), 0) as GEM2L_deuc
    , ISNULL(sum(case riskfactor when 1035 then value else NULL end), 0) as GEM2L_divfinan , ISNULL(sum(case riskfactor
    when 1016 then value else NULL end), 0) as GEM2L_divmetal , ISNULL(sum(case riskfactor when 1060 then value else
    NULL end), 0) as GEM2L_dnk , ISNULL(sum(case riskfactor when 1114 then value else NULL end), 0) as GEM2L_dnkc ,
    ISNULL(sum(case riskfactor when 1061 then value else NULL end), 0) as GEM2L_egy , ISNULL(sum(case riskfactor when
    1115 then value else NULL end), 0) as GEM2L_egyc , ISNULL(sum(case riskfactor when 1116 then value else NULL end),
    0) as GEM2L_emuc , ISNULL(sum(case riskfactor when 1011 then value else NULL end), 0) as GEM2L_energy ,
    ISNULL(sum(case riskfactor when 1062 then value else NULL end), 0) as GEM2L_esp , ISNULL(sum(case riskfactor when
    1117 then value else NULL end), 0) as GEM2L_espc , ISNULL(sum(case riskfactor when 1063 then value else NULL end),
    0) as GEM2L_fin , ISNULL(sum(case riskfactor when 1118 then value else NULL end), 0) as GEM2L_finc ,
    ISNULL(sum(case riskfactor when 1029 then value else NULL end), 0) as GEM2L_foodprd , ISNULL(sum(case riskfactor
    when 1028 then value else NULL end), 0) as GEM2L_foodretl , ISNULL(sum(case riskfactor when 1064 then value else
    NULL end), 0) as GEM2L_fra , ISNULL(sum(case riskfactor when 1119 then value else NULL end), 0) as GEM2L_frac ,
    ISNULL(sum(case riskfactor when 1065 then value else NULL end), 0) as GEM2L_gbr , ISNULL(sum(case riskfactor when
    1120 then value else NULL end), 0) as GEM2L_gbrc , ISNULL(sum(case riskfactor when 1066 then value else NULL end),
    0) as GEM2L_grc , ISNULL(sum(case riskfactor when 1121 then value else NULL end), 0) as GEM2L_grcc ,
    ISNULL(sum(case riskfactor when 1008 then value else NULL end), 0) as GEM2L_growth , ISNULL(sum(case riskfactor
    when 1031 then value else NULL end), 0) as GEM2L_health , ISNULL(sum(case riskfactor when 1067 then value else NULL
    end), 0) as GEM2L_hkg , ISNULL(sum(case riskfactor when 1122 then value else NULL end), 0) as GEM2L_hkgc ,
    ISNULL(sum(case riskfactor when 1030 then value else NULL end), 0) as GEM2L_hshldprd , ISNULL(sum(case riskfactor
    when 1068 then value else NULL end), 0) as GEM2L_hun , ISNULL(sum(case riskfactor when 1123 then value else NULL
    end), 0) as GEM2L_hunc , ISNULL(sum(case riskfactor when 1069 then value else NULL end), 0) as GEM2L_idn ,
    ISNULL(sum(case riskfactor when 1124 then value else NULL end), 0) as GEM2L_idnc , ISNULL(sum(case riskfactor when
    1070 then value else NULL end), 0) as GEM2L_ind , ISNULL(sum(case riskfactor when 1125 then value else NULL end),
    0) as GEM2L_indc , ISNULL(sum(case riskfactor when 1036 then value else NULL end), 0) as GEM2L_insuran ,
    ISNULL(sum(case riskfactor when 1038 then value else NULL end), 0) as GEM2L_internet , ISNULL(sum(case riskfactor
    when 1071 then value else NULL end), 0) as GEM2L_irl , ISNULL(sum(case riskfactor when 1126 then value else NULL
    end), 0) as GEM2L_irlc , ISNULL(sum(case riskfactor when 1072 then value else NULL end), 0) as GEM2L_isr ,
    ISNULL(sum(case riskfactor when 1127 then value else NULL end), 0) as GEM2L_isrc , ISNULL(sum(case riskfactor when
    1073 then value else NULL end), 0) as GEM2L_ita , ISNULL(sum(case riskfactor when 1128 then value else NULL end),
    0) as GEM2L_itac , ISNULL(sum(case riskfactor when 1074 then value else NULL end), 0) as GEM2L_jor ,
    ISNULL(sum(case riskfactor when 1129 then value else NULL end), 0) as GEM2L_jorc , ISNULL(sum(case riskfactor when
    1075 then value else NULL end), 0) as GEM2L_jpn , ISNULL(sum(case riskfactor when 1130 then value else NULL end),
    0) as GEM2L_jpnc , ISNULL(sum(case riskfactor when 1076 then value else NULL end), 0) as GEM2L_kor ,
    ISNULL(sum(case riskfactor when 1131 then value else NULL end), 0) as GEM2L_korc , ISNULL(sum(case riskfactor when
    1010 then value else NULL end), 0) as GEM2L_leverage , ISNULL(sum(case riskfactor when 1009 then value else NULL
    end), 0) as GEM2L_liquid , ISNULL(sum(case riskfactor when 1078 then value else NULL end), 0) as GEM2L_mar ,
    ISNULL(sum(case riskfactor when 1133 then value else NULL end), 0) as GEM2L_marc , ISNULL(sum(case riskfactor when
    1026 then value else NULL end), 0) as GEM2L_media , ISNULL(sum(case riskfactor when 1079 then value else NULL end),
    0) as GEM2L_mex , ISNULL(sum(case riskfactor when 1134 then value else NULL end), 0) as GEM2L_mexc ,
    ISNULL(sum(case riskfactor when 1003 then value else NULL end), 0) as GEM2L_momentum , ISNULL(sum(case riskfactor
    when 1080 then value else NULL end), 0) as GEM2L_mys , ISNULL(sum(case riskfactor when 1135 then value else NULL
    end), 0) as GEM2L_mysc , ISNULL(sum(case riskfactor when 1081 then value else NULL end), 0) as GEM2L_nld ,
    ISNULL(sum(case riskfactor when 1136 then value else NULL end), 0) as GEM2L_nldc , ISNULL(sum(case riskfactor when
    1082 then value else NULL end), 0) as GEM2L_nor , ISNULL(sum(case riskfactor when 1137 then value else NULL end),
    0) as GEM2L_norc , ISNULL(sum(case riskfactor when 1083 then value else NULL end), 0) as GEM2L_nzl ,
    ISNULL(sum(case riskfactor when 1138 then value else NULL end), 0) as GEM2L_nzlc , ISNULL(sum(case riskfactor when
    1013 then value else NULL end), 0) as GEM2L_oilexpl , ISNULL(sum(case riskfactor when 1012 then value else NULL
    end), 0) as GEM2L_oilgas , ISNULL(sum(case riskfactor when 1085 then value else NULL end), 0) as GEM2L_pak ,
    ISNULL(sum(case riskfactor when 1140 then value else NULL end), 0) as GEM2L_pakc , ISNULL(sum(case riskfactor when
    1086 then value else NULL end), 0) as GEM2L_per , ISNULL(sum(case riskfactor when 1141 then value else NULL end),
    0) as GEM2L_perc , ISNULL(sum(case riskfactor when 1033 then value else NULL end), 0) as GEM2L_pharmac ,
    ISNULL(sum(case riskfactor when 1087 then value else NULL end), 0) as GEM2L_phl , ISNULL(sum(case riskfactor when
    1142 then value else NULL end), 0) as GEM2L_phlc , ISNULL(sum(case riskfactor when 1088 then value else NULL end),
    0) as GEM2L_pol , ISNULL(sum(case riskfactor when 1143 then value else NULL end), 0) as GEM2L_polc ,
    ISNULL(sum(case riskfactor when 1017 then value else NULL end), 0) as GEM2L_precmetl , ISNULL(sum(case riskfactor
    when 1089 then value else NULL end), 0) as GEM2L_prt , ISNULL(sum(case riskfactor when 1144 then value else NULL
    end), 0) as GEM2L_prtc , ISNULL(sum(case riskfactor when 1037 then value else NULL end), 0) as GEM2L_realest ,
    ISNULL(sum(case riskfactor when 1027 then value else NULL end), 0) as GEM2L_retail , ISNULL(sum(case riskfactor
    when 1091 then value else NULL end), 0) as GEM2L_rus , ISNULL(sum(case riskfactor when 1146 then value else NULL
    end), 0) as GEM2L_rusc , ISNULL(sum(case riskfactor when 1042 then value else NULL end), 0) as GEM2L_semicond ,
    ISNULL(sum(case riskfactor when 1093 then value else NULL end), 0) as GEM2L_sgp , ISNULL(sum(case riskfactor when
    1148 then value else NULL end), 0) as GEM2L_sgpc , ISNULL(sum(case riskfactor when 1006 then value else NULL end),
    0) as GEM2L_size , ISNULL(sum(case riskfactor when 1007 then value else NULL end), 0) as GEM2L_sizenonl ,
    ISNULL(sum(case riskfactor when 1039 then value else NULL end), 0) as GEM2L_software , ISNULL(sum(case riskfactor
    when 1001 then value else NULL end), 0) as GEM2L_srisk , ISNULL(sum(case riskfactor when 1018 then value else NULL
    end), 0) as GEM2L_steel , ISNULL(sum(case riskfactor when 1094 then value else NULL end), 0) as GEM2L_swe ,
    ISNULL(sum(case riskfactor when 1149 then value else NULL end), 0) as GEM2L_swec , ISNULL(sum(case riskfactor when
    1043 then value else NULL end), 0) as GEM2L_telecom , ISNULL(sum(case riskfactor when 1095 then value else NULL
    end), 0) as GEM2L_tha , ISNULL(sum(case riskfactor when 1150 then value else NULL end), 0) as GEM2L_thac ,
    ISNULL(sum(case riskfactor when 1021 then value else NULL end), 0) as GEM2L_transprt , ISNULL(sum(case riskfactor
    when 1096 then value else NULL end), 0) as GEM2L_tur , ISNULL(sum(case riskfactor when 1151 then value else NULL
    end), 0) as GEM2L_turc , ISNULL(sum(case riskfactor when 1097 then value else NULL end), 0) as GEM2L_twn ,
    ISNULL(sum(case riskfactor when 1152 then value else NULL end), 0) as GEM2L_twnc , ISNULL(sum(case riskfactor when
    1098 then value else NULL end), 0) as GEM2L_usa , ISNULL(sum(case riskfactor when 1153 then value else NULL end),
    0) as GEM2L_usac , ISNULL(sum(case riskfactor when 1044 then value else NULL end), 0) as GEM2L_utility ,
    ISNULL(sum(case riskfactor when 1005 then value else NULL end), 0) as GEM2L_value , ISNULL(sum(case riskfactor when
    1004 then value else NULL end), 0) as GEM2L_volatil , ISNULL(sum(case riskfactor when 1002 then value else NULL
    end), 0) as GEM2L_world , ISNULL(sum(case riskfactor when 1099 then value else NULL end), 0) as GEM2L_zaf ,
    ISNULL(sum(case riskfactor when 1154 then value else NULL end), 0) as GEM2L_zafc from cmmn_risk_exposures where
    period_dt >= '01/01/1997' and period_dt <= '01/01/2012' and riskfactor in ( 1022 , 1046 , 1101 , 1047 , 1102 , 1048
    , 1103 , 1023 , 1034 , 1049 , 1104 , 1032 , 1051 , 1106 , 1052 , 1107 , 1019 , 1053 , 1108 , 1014 , 1054 , 1109 ,
    1055 , 1110 , 1056 , 1057 , 1111 , 1020 , 1040 , 1041 , 1024 , 1015 , 1025 , 1058 , 1112 , 1059 , 1113 , 1035 ,
    1016 , 1060 , 1114 , 1061 , 1115 , 1116 , 1011 , 1062 , 1117 , 1063 , 1118 , 1029 , 1028 , 1064 , 1119 , 1065 ,
    1120 , 1066 , 1121 , 1008 , 1031 , 1067 , 1122 , 1030 , 1068 , 1123 , 1069 , 1124 , 1070 , 1125 , 1036 , 1038 ,
    1071 , 1126 , 1072 , 1127 , 1073 , 1128 , 1074 , 1129 , 1075 , 1130 , 1076 , 1131 , 1010 , 1009 , 1078 , 1133 ,
    1026 , 1079 , 1134 , 1003 , 1080 , 1135 , 1081 , 1136 , 1082 , 1137 , 1083 , 1138 , 1013 , 1012 , 1085 , 1140 ,
    1086 , 1141 , 1033 , 1087 , 1142 , 1088 , 1143 , 1017 , 1089 , 1144 , 1037 , 1027 , 1091 , 1146 , 1042 , 1093 ,
    1148 , 1006 , 1007 , 1039 , 1001 , 1018 , 1094 , 1149 , 1043 , 1095 , 1150 , 1021 , 1096 , 1151 , 1097 , 1152 ,
    1098 , 1153 , 1044 , 1005 , 1004 , 1002 , 1099 , 1154 ) 
    group by period_dt, vendorid order by period_dt, vendorid

    pivoting query

    WITH p AS (
    select period_dt, vendorid, riskfactor, value 
    from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '02/01/2012' 
    and riskfactor >= 1000 and riskfactor <= 1999
    )
    select period_dt, vendorid, [1022] AS [GEM2L_1022_airlines], [1046] AS [GEM2L_1046_arg], [1101] AS [GEM2L_1101_argc], [1047] AS [GEM2L_1047_aus], [1102] AS [GEM2L_1102_ausc], [1048] AS [GEM2L_1048_aut], [1103] AS [GEM2L_1103_autc], [1023] AS [GEM2L_1023_autocomp], [1034] AS [GEM2L_1034_banks], [1049] AS [GEM2L_1049_bel], [1104] AS [GEM2L_1104_belc], [1032] AS [GEM2L_1032_biotech], [1051] AS [GEM2L_1051_bra], [1106] AS [GEM2L_1106_brac], [1052] AS [GEM2L_1052_can], [1107] AS [GEM2L_1107_canc], [1019] AS [GEM2L_1019_capgoods], [1053] AS [GEM2L_1053_che], [1108] AS [GEM2L_1108_chec], [1014] AS [GEM2L_1014_chemical], [1054] AS [GEM2L_1054_chl], [1109] AS [GEM2L_1109_chlc], [1055] AS [GEM2L_1055_chn], [1110] AS [GEM2L_1110_chnc], [1056] AS [GEM2L_1056_chx], [1057] AS [GEM2L_1057_col], [1111] AS [GEM2L_1111_colc], [1020] AS [GEM2L_1020_commsvcs], [1040] AS [GEM2L_1040_communic], [1041] AS [GEM2L_1041_computer], [1024] AS [GEM2L_1024_consdur], [1015] AS [GEM2L_1015_constpp], [1025] AS [GEM2L_1025_consvcs], [1058] AS [GEM2L_1058_cze], [1112] AS [GEM2L_1112_czec], [1059] AS [GEM2L_1059_deu], [1113] AS [GEM2L_1113_deuc], [1035] AS [GEM2L_1035_divfinan], [1016] AS [GEM2L_1016_divmetal], [1060] AS [GEM2L_1060_dnk], [1114] AS [GEM2L_1114_dnkc], [1061] AS [GEM2L_1061_egy], [1115] AS [GEM2L_1115_egyc], [1116] AS [GEM2L_1116_emuc], [1011] AS [GEM2L_1011_energy], [1062] AS [GEM2L_1062_esp], [1117] AS [GEM2L_1117_espc], [1063] AS [GEM2L_1063_fin], [1118] AS [GEM2L_1118_finc], [1029] AS [GEM2L_1029_foodprd], [1028] AS [GEM2L_1028_foodretl], [1064] AS [GEM2L_1064_fra], [1119] AS [GEM2L_1119_frac], [1065] AS [GEM2L_1065_gbr], [1120] AS [GEM2L_1120_gbrc], [1066] AS [GEM2L_1066_grc], [1121] AS [GEM2L_1121_grcc], [1008] AS [GEM2L_1008_growth], [1031] AS [GEM2L_1031_health], [1067] AS [GEM2L_1067_hkg], [1122] AS [GEM2L_1122_hkgc], [1030] AS [GEM2L_1030_hshldprd], [1068] AS [GEM2L_1068_hun], [1123] AS [GEM2L_1123_hunc], [1069] AS [GEM2L_1069_idn], [1124] AS [GEM2L_1124_idnc], [1070] AS [GEM2L_1070_ind], [1125] AS [GEM2L_1125_indc], [1036] AS [GEM2L_1036_insuran], [1038] AS [GEM2L_1038_internet], [1071] AS [GEM2L_1071_irl], [1126] AS [GEM2L_1126_irlc], [1072] AS [GEM2L_1072_isr], [1127] AS [GEM2L_1127_isrc], [1073] AS [GEM2L_1073_ita], [1128] AS [GEM2L_1128_itac], [1074] AS [GEM2L_1074_jor], [1129] AS [GEM2L_1129_jorc], [1075] AS [GEM2L_1075_jpn], [1130] AS [GEM2L_1130_jpnc], [1076] AS [GEM2L_1076_kor], [1131] AS [GEM2L_1131_korc], [1010] AS [GEM2L_1010_leverage], [1009] AS [GEM2L_1009_liquid], [1078] AS [GEM2L_1078_mar], [1133] AS [GEM2L_1133_marc], [1026] AS [GEM2L_1026_media], [1079] AS [GEM2L_1079_mex], [1134] AS [GEM2L_1134_mexc], [1003] AS [GEM2L_1003_momentum], [1080] AS [GEM2L_1080_mys], [1135] AS [GEM2L_1135_mysc], [1081] AS [GEM2L_1081_nld], [1136] AS [GEM2L_1136_nldc], [1082] AS [GEM2L_1082_nor], [1137] AS [GEM2L_1137_norc], [1083] AS [GEM2L_1083_nzl], [1138] AS [GEM2L_1138_nzlc], [1013] AS [GEM2L_1013_oilexpl], [1012] AS [GEM2L_1012_oilgas], [1085] AS [GEM2L_1085_pak], [1140] AS [GEM2L_1140_pakc], [1086] AS [GEM2L_1086_per], [1141] AS [GEM2L_1141_perc], [1033] AS [GEM2L_1033_pharmac], [1087] AS [GEM2L_1087_phl], [1142] AS [GEM2L_1142_phlc], [1088] AS [GEM2L_1088_pol], [1143] AS [GEM2L_1143_polc], [1017] AS [GEM2L_1017_precmetl], [1089] AS [GEM2L_1089_prt], [1144] AS [GEM2L_1144_prtc], [1037] AS [GEM2L_1037_realest], [1027] AS [GEM2L_1027_retail], [1091] AS [GEM2L_1091_rus], [1146] AS [GEM2L_1146_rusc], [1042] AS [GEM2L_1042_semicond], [1093] AS [GEM2L_1093_sgp], [1148] AS [GEM2L_1148_sgpc], [1006] AS [GEM2L_1006_size], [1007] AS [GEM2L_1007_sizenonl], [1039] AS [GEM2L_1039_software], [1001] AS [GEM2L_1001_srisk], [1018] AS [GEM2L_1018_steel], [1094] AS [GEM2L_1094_swe], [1149] AS [GEM2L_1149_swec], [1043] AS [GEM2L_1043_telecom], [1095] AS [GEM2L_1095_tha], [1150] AS [GEM2L_1150_thac], [1021] AS [GEM2L_1021_transprt], [1096] AS [GEM2L_1096_tur], [1151] AS [GEM2L_1151_turc], [1097] AS [GEM2L_1097_twn], [1152] AS [GEM2L_1152_twnc], [1098] AS [GEM2L_1098_usa], [1153] AS [GEM2L_1153_usac], [1044] AS [GEM2L_1044_utility], [1005] AS [GEM2L_1005_value], [1004] AS [GEM2L_1004_volatil], [1002] AS [GEM2L_1002_world], [1099] AS [GEM2L_1099_zaf], [1154] AS [GEM2L_1154_zafc]
    from p
    PIVOT (
       max(value)
       FOR riskfactor IN(
           [1022], [1046], [1101], [1047], [1102], [1048], [1103], [1023], [1034], [1049], [1104], [1032], [1051], [1106], [1052], [1107], [1019], [1053], [1108], [1014], [1054], [1109], [1055], [1110], [1056], [1057], [1111], [1020], [1040], [1041], [1024], [1015], [1025], [1058], [1112], [1059], [1113], [1035], [1016], [1060], [1114], [1061], [1115], [1116], [1011], [1062], [1117], [1063], [1118], [1029], [1028], [1064], [1119], [1065], [1120], [1066], [1121], [1008], [1031], [1067], [1122], [1030], [1068], [1123], [1069], [1124], [1070], [1125], [1036], [1038], [1071], [1126], [1072], [1127], [1073], [1128], [1074], [1129], [1075], [1130], [1076], [1131], [1010], [1009], [1078], [1133], [1026], [1079], [1134], [1003], [1080], [1135], [1081], [1136], [1082], [1137], [1083], [1138], [1013], [1012], [1085], [1140], [1086], [1141], [1033], [1087], [1142], [1088], [1143], [1017], [1089], [1144], [1037], [1027], [1091], [1146], [1042], [1093], [1148], [1006], [1007], [1039], [1001], [1018], [1094], [1149], [1043], [1095], [1150], [1021], [1096], [1151], [1097], [1152], [1098], [1153], [1044], [1005], [1004], [1002], [1099], [1154]
       )
    ) AS pvt
    order by period_dt, vendorid


    fj

    Thursday, December 06, 2012 4:52 PM
  • I see some differences. First of all, your first query uses ISNULL for every column while the second doesn't. 

    For the first case, what will happen if you get rid of the IN clause (you're testing it anyway in any of the expression).

    Also, what will happen if you will not write ELSE clauses at all and use default (I don't think it will speed up, but just in case).

    So, try with these two changes and compare the performance again.

    Also, in your case you're using a PIVOT on a single column where I don't expect to be big difference in  2 methods. I was mostly suggesting a difference when we need to pivot multiple columns by either doubling PIVOT or using case operators.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, December 06, 2012 5:26 PM
    Moderator
  • >> First let me preface this (for the Celko’s sake) by saying that I agree that pivoting data is a display function and is best done at the application layer or in reporting software like Crystal Reports or SSRS. <<

    Rats! I had a  great rant. :)-

    >> Is there any real reason to use or even learn to use the PIVOT operator? <,

    To read bad T-SQL written by a newbie who is trying to get job security with proprietary garbage. 

    >> Am I missing something that would give the PIVOT an edge over aggregated CASE expressions? <<

    Not that I know. In fact CASE/SUM has more power. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi NModerator Thursday, December 06, 2012 11:30 PM
    • Marked as answer by Jason A Long Tuesday, December 11, 2012 7:27 PM
    Thursday, December 06, 2012 11:00 PM
  • Thanks Naomi. I have changed the query so two queries are compared apple to apple.  They are supposed to run at the same speed, but the pivoting is much faster in my case. We are using SQL 2008R2, don't know if that matters.

    Group by query using case stmt (takes 42.5min)

     

    select period_dt, vendorid, sum(case riskfactor when 1022 then value end) as GEM2L_airlines, sum(case riskfactor when 1046 then value end) as GEM2L_arg, sum(case riskfactor when 1101 then value end) as GEM2L_argc, sum(case riskfactor when 1047 then value end) as GEM2L_aus, sum(case riskfactor when 1102 then value end) as GEM2L_ausc, sum(case riskfactor when 1048 then value end) as GEM2L_aut, sum(case riskfactor when 1103 then value end) as GEM2L_autc, 
    sum(case riskfactor when 1023 then value end) as GEM2L_autocomp, sum(case riskfactor when 1034 then value end) as GEM2L_banks, sum(case riskfactor when 1049 then value end) as GEM2L_bel, sum(case riskfactor when 1104 then value end) as GEM2L_belc, sum(case riskfactor when 1032 then value end) as GEM2L_biotech, sum(case riskfactor when 1051 then value end) as GEM2L_bra, sum(case riskfactor when 1106 then value end) as GEM2L_brac, 
    sum(case riskfactor when 1052 then value end) as GEM2L_can, sum(case riskfactor when 1107 then value end) as GEM2L_canc, sum(case riskfactor when 1019 then value end) as GEM2L_capgoods, sum(case riskfactor when 1053 then value end) as GEM2L_che, sum(case riskfactor when 1108 then value end) as GEM2L_chec, sum(case riskfactor when 1014 then value end) as GEM2L_chemical, sum(case riskfactor when 1054 then value end) as GEM2L_chl, 
    sum(case riskfactor when 1109 then value end) as GEM2L_chlc, sum(case riskfactor when 1055 then value end) as GEM2L_chn, sum(case riskfactor when 1110 then value end) as GEM2L_chnc, sum(case riskfactor when 1056 then value end) as GEM2L_chx, sum(case riskfactor when 1057 then value end) as GEM2L_col, sum(case riskfactor when 1111 then value end) as GEM2L_colc, sum(case riskfactor when 1020 then value end) as GEM2L_commsvcs, 
    sum(case riskfactor when 1040 then value end) as GEM2L_communic, sum(case riskfactor when 1041 then value end) as GEM2L_computer, sum(case riskfactor when 1024 then value end) as GEM2L_consdur, sum(case riskfactor when 1015 then value end) as GEM2L_constpp, sum(case riskfactor when 1025 then value end) as GEM2L_consvcs, sum(case riskfactor when 1058 then value end) as GEM2L_cze, sum(case riskfactor when 1112 then value end) as GEM2L_czec, 
    sum(case riskfactor when 1059 then value end) as GEM2L_deu, sum(case riskfactor when 1113 then value end) as GEM2L_deuc, sum(case riskfactor when 1035 then value end) as GEM2L_divfinan, sum(case riskfactor when 1016 then value end) as GEM2L_divmetal, sum(case riskfactor when 1060 then value end) as GEM2L_dnk, sum(case riskfactor when 1114 then value end) as GEM2L_dnkc, sum(case riskfactor when 1061 then value end) as GEM2L_egy, 
    sum(case riskfactor when 1115 then value end) as GEM2L_egyc, sum(case riskfactor when 1116 then value end) as GEM2L_emuc, sum(case riskfactor when 1011 then value end) as GEM2L_energy, sum(case riskfactor when 1062 then value end) as GEM2L_esp, sum(case riskfactor when 1117 then value end) as GEM2L_espc, sum(case riskfactor when 1063 then value end) as GEM2L_fin, sum(case riskfactor when 1118 then value end) as GEM2L_finc, sum(case riskfactor when 1029 then value end) as GEM2L_foodprd, sum(case riskfactor when 1028 then value end) as GEM2L_foodretl, sum(case riskfactor when 1064 then value end) as GEM2L_fra, sum(case riskfactor when 1119 then value end) as GEM2L_frac, sum(case riskfactor when 1065 then value end) as GEM2L_gbr, sum(case riskfactor when 1120 then value end) as GEM2L_gbrc, sum(case riskfactor when 1066 then value end) as GEM2L_grc, sum(case riskfactor when 1121 then value end) as GEM2L_grcc, sum(case riskfactor when 1008 then value end) as GEM2L_growth, sum(case riskfactor when 1031 then value end) as GEM2L_health, sum(case riskfactor when 1067 then value end) as GEM2L_hkg, sum(case riskfactor when 1122 then value end) as GEM2L_hkgc, sum(case riskfactor when 1030 then value end) as GEM2L_hshldprd, sum(case riskfactor when 1068 then value end) as GEM2L_hun, sum(case riskfactor when 1123 then value end) as GEM2L_hunc, sum(case riskfactor when 1069 then value end) as GEM2L_idn, sum(case riskfactor when 1124 then value end) as GEM2L_idnc, sum(case riskfactor when 1070 then value end) as GEM2L_ind, sum(case riskfactor when 1125 then value end) as GEM2L_indc, sum(case riskfactor when 1036 then value end) as GEM2L_insuran, sum(case riskfactor when 1038 then value end) as GEM2L_internet, sum(case riskfactor when 1071 then value end) as GEM2L_irl, sum(case riskfactor when 1126 then value end) as GEM2L_irlc, sum(case riskfactor when 1072 then value end) as GEM2L_isr, sum(case riskfactor when 1127 then value end) as GEM2L_isrc, sum(case riskfactor when 1073 then value end) as GEM2L_ita, sum(case riskfactor when 1128 then value end) as GEM2L_itac, sum(case riskfactor when 1074 then value end) as GEM2L_jor, sum(case riskfactor when 1129 then value end) as GEM2L_jorc, sum(case riskfactor when 1075 then value end) as GEM2L_jpn, sum(case riskfactor when 1130 then value end) as GEM2L_jpnc, sum(case riskfactor when 1076 then value end) as GEM2L_kor, sum(case riskfactor when 1131 then value end) as GEM2L_korc, sum(case riskfactor when 1010 then value end) as GEM2L_leverage, sum(case riskfactor when 1009 then value end) as GEM2L_liquid, sum(case riskfactor when 1078 then value end) as GEM2L_mar, sum(case riskfactor when 1133 then value end) as GEM2L_marc, sum(case riskfactor when 1026 then value end) as GEM2L_media, sum(case riskfactor when 1079 then value end) as GEM2L_mex, sum(case riskfactor when 1134 then value end) as GEM2L_mexc, sum(case riskfactor when 1003 then value end) as GEM2L_momentum, sum(case riskfactor when 1080 then value end) as GEM2L_mys, sum(case riskfactor when 1135 then value end) as GEM2L_mysc, sum(case riskfactor when 1081 then value end) as GEM2L_nld, sum(case riskfactor when 1136 then value end) as GEM2L_nldc, sum(case riskfactor when 1082 then value end) as GEM2L_nor, sum(case riskfactor when 1137 then value end) as GEM2L_norc, sum(case riskfactor when 1083 then value end) as GEM2L_nzl, sum(case riskfactor when 1138 then value end) as GEM2L_nzlc, sum(case riskfactor when 1013 then value end) as GEM2L_oilexpl, sum(case riskfactor when 1012 then value end) as GEM2L_oilgas, sum(case riskfactor when 1085 then value end) as GEM2L_pak, sum(case riskfactor when 1140 then value end) as GEM2L_pakc, sum(case riskfactor when 1086 then value end) as GEM2L_per, sum(case riskfactor when 1141 then value end) as GEM2L_perc, sum(case riskfactor when 1033 then value end) as GEM2L_pharmac, sum(case riskfactor when 1087 then value end) as GEM2L_phl, sum(case riskfactor when 1142 then value end) as GEM2L_phlc, sum(case riskfactor when 1088 then value end) as GEM2L_pol, sum(case riskfactor when 1143 then value end) as GEM2L_polc, sum(case riskfactor when 1017 then value end) as GEM2L_precmetl, sum(case riskfactor when 1089 then value end) as GEM2L_prt, sum(case riskfactor when 1144 then value end) as GEM2L_prtc, sum(case riskfactor when 1037 then value end) as GEM2L_realest, sum(case riskfactor when 1027 then value end) as GEM2L_retail, sum(case riskfactor when 1091 then value end) as GEM2L_rus, sum(case riskfactor when 1146 then value end) as GEM2L_rusc, sum(case riskfactor when 1042 then value end) as GEM2L_semicond, sum(case riskfactor when 1093 then value end) as GEM2L_sgp, sum(case riskfactor when 1148 then value end) as GEM2L_sgpc, sum(case riskfactor when 1006 then value end) as GEM2L_size, sum(case riskfactor when 1007 then value end) as GEM2L_sizenonl, sum(case riskfactor when 1039 then value end) as GEM2L_software, sum(case riskfactor when 1001 then value end) as GEM2L_srisk, sum(case riskfactor when 1018 then value end) as GEM2L_steel, sum(case riskfactor when 1094 then value end) as GEM2L_swe, sum(case riskfactor when 1149 then value end) as GEM2L_swec, sum(case riskfactor when 1043 then value end) as GEM2L_telecom, sum(case riskfactor when 1095 then value end) as GEM2L_tha, sum(case riskfactor when 1150 then value end) as GEM2L_thac, sum(case riskfactor when 1021 then value end) as GEM2L_transprt, sum(case riskfactor when 1096 then value end) as GEM2L_tur, sum(case riskfactor when 1151 then value end) as GEM2L_turc, sum(case riskfactor when 1097 then value end) as GEM2L_twn, sum(case riskfactor when 1152 then value end) as GEM2L_twnc, sum(case riskfactor when 1098 then value end) as GEM2L_usa, sum(case riskfactor when 1153 then value end) as GEM2L_usac, 
    sum(case riskfactor when 1044 then value end) as GEM2L_utility, sum(case riskfactor when 1005 then value end) as GEM2L_value, sum(case riskfactor when 1004 then value end) as GEM2L_volatil, 
    sum(case riskfactor when 1002 then value end) as GEM2L_world, sum(case riskfactor when 1099 then value end) as GEM2L_zaf, sum(case riskfactor when 1154 then value end) as GEM2L_zafc from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '01/01/2012' and riskfactor in ( 1022, 1046, 1101, 1047, 1102, 1048, 1103, 1023, 1034, 1049, 1104, 1032, 1051, 1106, 1052, 1107, 1019, 1053, 1108, 1014, 1054, 1109, 1055, 1110, 1056, 1057, 1111, 1020, 1040, 1041, 1024, 1015, 1025, 1058, 1112, 1059, 1113, 1035, 1016, 1060, 1114, 1061, 1115, 1116, 1011, 1062, 1117, 1063, 1118, 1029, 1028, 1064, 1119, 1065, 1120, 1066, 1121, 1008, 1031, 1067, 1122, 1030, 1068, 1123, 1069, 1124, 1070, 1125, 1036, 1038, 1071, 1126, 1072, 1127, 1073, 1128, 1074, 1129, 1075, 1130, 1076, 1131, 1010, 1009, 1078, 1133, 1026, 1079, 1134, 1003, 1080, 1135, 1081, 1136, 1082, 1137, 1083, 1138, 1013, 1012, 1085, 1140, 1086, 1141, 1033, 1087, 1142, 1088, 1143, 1017, 1089, 1144, 1037, 1027, 1091, 1146, 1042, 1093, 1148, 1006, 1007, 1039, 1001, 1018, 1094, 1149, 1043, 1095, 1150, 1021, 1096, 1151, 1097, 1152, 1098, 1153, 1044, 1005, 1004, 1002, 1099, 1154 ) 
    group by period_dt, vendorid order by period_dt, vendorid

    The piviting query takes 4 min.  I have checked the result is the same

    WITH p AS (
    select period_dt, vendorid, riskfactor, value 
    from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '01/01/2012' 
    and riskfactor >= 1000 and riskfactor <= 1999
    )
    select period_dt, vendorid, [1022] AS [GEM2L_1022_airlines], [1046] AS [GEM2L_1046_arg], [1101] AS [GEM2L_1101_argc], [1047] AS [GEM2L_1047_aus], [1102] AS [GEM2L_1102_ausc], [1048] AS [GEM2L_1048_aut], [1103] AS [GEM2L_1103_autc], [1023] AS [GEM2L_1023_autocomp], [1034] AS [GEM2L_1034_banks], [1049] AS [GEM2L_1049_bel], [1104] AS [GEM2L_1104_belc], [1032] AS [GEM2L_1032_biotech], [1051] AS [GEM2L_1051_bra], [1106] AS [GEM2L_1106_brac], [1052] AS [GEM2L_1052_can], [1107] AS [GEM2L_1107_canc], [1019] AS [GEM2L_1019_capgoods], [1053] AS [GEM2L_1053_che], [1108] AS [GEM2L_1108_chec], 
    [1014] AS [GEM2L_1014_chemical], [1054] AS [GEM2L_1054_chl], [1109] AS [GEM2L_1109_chlc], [1055] AS [GEM2L_1055_chn], [1110] AS [GEM2L_1110_chnc], [1056] AS [GEM2L_1056_chx], [1057] AS [GEM2L_1057_col], [1111] AS [GEM2L_1111_colc], [1020] AS [GEM2L_1020_commsvcs], [1040] AS [GEM2L_1040_communic], [1041] AS [GEM2L_1041_computer], [1024] AS [GEM2L_1024_consdur], [1015] AS [GEM2L_1015_constpp], [1025] AS [GEM2L_1025_consvcs], [1058] AS [GEM2L_1058_cze], [1112] AS [GEM2L_1112_czec], [1059] AS [GEM2L_1059_deu], [1113] AS [GEM2L_1113_deuc], [1035] AS [GEM2L_1035_divfinan], 
    [1016] AS [GEM2L_1016_divmetal], [1060] AS [GEM2L_1060_dnk], [1114] AS [GEM2L_1114_dnkc], [1061] AS [GEM2L_1061_egy], [1115] AS [GEM2L_1115_egyc], [1116] AS [GEM2L_1116_emuc], [1011] AS [GEM2L_1011_energy], [1062] AS [GEM2L_1062_esp], [1117] AS [GEM2L_1117_espc], [1063] AS [GEM2L_1063_fin], [1118] AS [GEM2L_1118_finc], [1029] AS [GEM2L_1029_foodprd], [1028] AS [GEM2L_1028_foodretl], [1064] AS [GEM2L_1064_fra], [1119] AS [GEM2L_1119_frac], [1065] AS [GEM2L_1065_gbr], [1120] AS [GEM2L_1120_gbrc], [1066] AS [GEM2L_1066_grc], [1121] AS [GEM2L_1121_grcc], [1008] AS [GEM2L_1008_growth], 
    [1031] AS [GEM2L_1031_health], [1067] AS [GEM2L_1067_hkg], [1122] AS [GEM2L_1122_hkgc], [1030] AS [GEM2L_1030_hshldprd], [1068] AS [GEM2L_1068_hun], [1123] AS [GEM2L_1123_hunc], [1069] AS [GEM2L_1069_idn], [1124] AS [GEM2L_1124_idnc], [1070] AS [GEM2L_1070_ind], [1125] AS [GEM2L_1125_indc], [1036] AS [GEM2L_1036_insuran], [1038] AS [GEM2L_1038_internet], [1071] AS [GEM2L_1071_irl], [1126] AS [GEM2L_1126_irlc], [1072] AS [GEM2L_1072_isr], [1127] AS [GEM2L_1127_isrc], [1073] AS [GEM2L_1073_ita], [1128] AS [GEM2L_1128_itac], [1074] AS [GEM2L_1074_jor], [1129] AS [GEM2L_1129_jorc], 
    [1075] AS [GEM2L_1075_jpn], [1130] AS [GEM2L_1130_jpnc], [1076] AS [GEM2L_1076_kor], [1131] AS [GEM2L_1131_korc], [1010] AS [GEM2L_1010_leverage], [1009] AS [GEM2L_1009_liquid], [1078] AS [GEM2L_1078_mar], [1133] AS [GEM2L_1133_marc], [1026] AS [GEM2L_1026_media], [1079] AS [GEM2L_1079_mex], [1134] AS [GEM2L_1134_mexc], [1003] AS [GEM2L_1003_momentum], [1080] AS [GEM2L_1080_mys], [1135] AS [GEM2L_1135_mysc], [1081] AS [GEM2L_1081_nld], [1136] AS [GEM2L_1136_nldc], [1082] AS [GEM2L_1082_nor], [1137] AS [GEM2L_1137_norc], [1083] AS [GEM2L_1083_nzl], [1138] AS [GEM2L_1138_nzlc], 
    [1013] AS [GEM2L_1013_oilexpl], [1012] AS [GEM2L_1012_oilgas], [1085] AS [GEM2L_1085_pak], [1140] AS [GEM2L_1140_pakc], [1086] AS [GEM2L_1086_per], [1141] AS [GEM2L_1141_perc], [1033] AS [GEM2L_1033_pharmac], [1087] AS [GEM2L_1087_phl], [1142] AS [GEM2L_1142_phlc], [1088] AS [GEM2L_1088_pol], [1143] AS [GEM2L_1143_polc], [1017] AS [GEM2L_1017_precmetl], [1089] AS [GEM2L_1089_prt], [1144] AS [GEM2L_1144_prtc], [1037] AS [GEM2L_1037_realest], [1027] AS [GEM2L_1027_retail], [1091] AS [GEM2L_1091_rus], [1146] AS [GEM2L_1146_rusc], [1042] AS [GEM2L_1042_semicond], [1093] AS [GEM2L_1093_sgp], 
    [1148] AS [GEM2L_1148_sgpc], [1006] AS [GEM2L_1006_size], [1007] AS [GEM2L_1007_sizenonl], [1039] AS [GEM2L_1039_software], 
    [1001] AS [GEM2L_1001_srisk], [1018] AS [GEM2L_1018_steel], [1094] AS [GEM2L_1094_swe], [1149] AS [GEM2L_1149_swec], [1043] AS [GEM2L_1043_telecom], 
    [1095] AS [GEM2L_1095_tha], [1150] AS [GEM2L_1150_thac], [1021] AS [GEM2L_1021_transprt], [1096] AS [GEM2L_1096_tur], [1151] AS [GEM2L_1151_turc], 
    [1097] AS [GEM2L_1097_twn], [1152] AS [GEM2L_1152_twnc], [1098] AS [GEM2L_1098_usa], [1153] AS [GEM2L_1153_usac], [1044] AS [GEM2L_1044_utility], 
    [1005] AS [GEM2L_1005_value], [1004] AS [GEM2L_1004_volatil], [1002] AS [GEM2L_1002_world], [1099] AS [GEM2L_1099_zaf], [1154] AS [GEM2L_1154_zafc]
    from p
    PIVOT (
     max(value)
     FOR riskfactor IN(
       [1022], [1046], [1101], [1047], [1102], [1048], [1103], [1023], [1034], [1049], [1104], [1032], [1051], [1106], [1052], [1107], [1019], [1053], [1108], [1014], [1054], [1109], [1055], [1110], [1056], [1057], [1111], [1020], [1040], [1041], [1024], [1015], [1025], [1058], [1112], [1059], [1113], [1035], [1016], [1060], [1114], [1061], [1115], [1116], [1011], [1062], [1117], [1063], [1118], [1029], [1028], [1064], [1119], [1065], [1120], [1066], [1121], [1008], [1031], [1067], [1122], [1030], [1068], [1123], [1069], [1124], [1070], [1125], 
    [1036], [1038], [1071], [1126], [1072], [1127], [1073], [1128], [1074], [1129], [1075], [1130], [1076], [1131], [1010], [1009], [1078], [1133], [1026], [1079], [1134], [1003], [1080], [1135], [1081], [1136], [1082], [1137], [1083], [1138], [1013], [1012], [1085], [1140], [1086], [1141], [1033], [1087], [1142], [1088], [1143], [1017], [1089], [1144], [1037], [1027], [1091], [1146], [1042], [1093], [1148], [1006], [1007], [1039], [1001], [1018], [1094], [1149], [1043], [1095], [1150], [1021], [1096], [1151], [1097], [1152], [1098], [1153], [1044], [1005], [1004], [1002], [1099], [1154]
     )
    ) AS pvt
    order by period_dt, vendorid


    fj

    Friday, December 07, 2012 4:27 PM
  • Still, there are differences. First of all, in the first query you use SUM, but in the second MAX()

    Secondly, I suggest to use the same CTE in both queries and use SUM case  from (select * from cte) X

    or PIVOT (SUM(value)..


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, December 07, 2012 4:37 PM
    Moderator
  • Naomi, thanks for your fast response. I'll make changes and report back. BTW, what is CTE stands for, this may be a common SQL term that I don't know.

    fj

    Friday, December 07, 2012 6:18 PM
  • CTE - common table expression. Two good blogs on this topic I know of (well, one being is mine so I am biased :)):

    CTE and hierarchical queries

    CTE: Coolest T-SQL Enhancement


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, December 07, 2012 6:21 PM
    Moderator
  • I did another round of testing. There are difference that is not that obvious between these two queries.

    "Group by" query, finished in 45min 30 sec. 

    "Pivoting" query, finished in 4min 13sec. 

    I have checked the execution plan of both.  Group by query has one more step "compute Scalar" although it uses 0% of total time. The big difference is "order by period_dt, vendorid". The "group by" query uses 62% of total time, but the pivoting query uses 22% of total time. There might be  some index tuning I can do to avoid going back to original table to retrieve data, but I would expect that impact should apply to both queries the same way. 

    Here are the queries

    "group by" query

    select period_dt, vendorid, sum(case riskfactor when 1022 then value end) as GEM2L_airlines, sum(case riskfactor when 1046 then value end) as GEM2L_arg, sum(case riskfactor when 1101 then value end) as GEM2L_argc, sum(case riskfactor when 1047 then value end) as GEM2L_aus, sum(case riskfactor when 1102 then value end) as GEM2L_ausc, sum(case riskfactor when 1048 then value end) as GEM2L_aut, sum(case riskfactor when 1103 then value end) as GEM2L_autc, 

    sum(case riskfactor when 1023 then value end) as GEM2L_autocomp, sum(case riskfactor when 1034 then value end) as GEM2L_banks, sum(case riskfactor when 1049 then value end) as GEM2L_bel, sum(case riskfactor when 1104 then value end) as GEM2L_belc, sum(case riskfactor when 1032 then value end) as GEM2L_biotech, sum(case riskfactor when 1051 then value end) as GEM2L_bra, sum(case riskfactor when 1106 then value end) as GEM2L_brac, 
    sum(case riskfactor when 1052 then value end) as GEM2L_can, sum(case riskfactor when 1107 then value end) as GEM2L_canc, sum(case riskfactor when 1019 then value end) as GEM2L_capgoods, sum(case riskfactor when 1053 then value end) as GEM2L_che, sum(case riskfactor when 1108 then value end) as GEM2L_chec, sum(case riskfactor when 1014 then value end) as GEM2L_chemical, sum(case riskfactor when 1054 then value end) as GEM2L_chl, 
    sum(case riskfactor when 1109 then value end) as GEM2L_chlc, sum(case riskfactor when 1055 then value end) as GEM2L_chn, sum(case riskfactor when 1110 then value end) as GEM2L_chnc, sum(case riskfactor when 1056 then value end) as GEM2L_chx, sum(case riskfactor when 1057 then value end) as GEM2L_col, sum(case riskfactor when 1111 then value end) as GEM2L_colc, sum(case riskfactor when 1020 then value end) as GEM2L_commsvcs, 
    sum(case riskfactor when 1040 then value end) as GEM2L_communic, sum(case riskfactor when 1041 then value end) as GEM2L_computer, sum(case riskfactor when 1024 then value end) as GEM2L_consdur, sum(case riskfactor when 1015 then value end) as GEM2L_constpp, sum(case riskfactor when 1025 then value end) as GEM2L_consvcs, sum(case riskfactor when 1058 then value end) as GEM2L_cze, sum(case riskfactor when 1112 then value end) as GEM2L_czec, 
    sum(case riskfactor when 1059 then value end) as GEM2L_deu, sum(case riskfactor when 1113 then value end) as GEM2L_deuc, sum(case riskfactor when 1035 then value end) as GEM2L_divfinan, sum(case riskfactor when 1016 then value end) as GEM2L_divmetal, sum(case riskfactor when 1060 then value end) as GEM2L_dnk, sum(case riskfactor when 1114 then value end) as GEM2L_dnkc, sum(case riskfactor when 1061 then value end) as GEM2L_egy, 
    sum(case riskfactor when 1115 then value end) as GEM2L_egyc, sum(case riskfactor when 1116 then value end) as GEM2L_emuc, sum(case riskfactor when 1011 then value end) as GEM2L_energy, sum(case riskfactor when 1062 then value end) as GEM2L_esp, sum(case riskfactor when 1117 then value end) as GEM2L_espc, sum(case riskfactor when 1063 then value end) as GEM2L_fin, sum(case riskfactor when 1118 then value end) as GEM2L_finc, sum(case riskfactor when 1029 then value end) as GEM2L_foodprd, 
    sum(case riskfactor when 1028 then value end) as GEM2L_foodretl, sum(case riskfactor when 1064 then value end) as GEM2L_fra, sum(case riskfactor when 1119 then value end) as GEM2L_frac, sum(case riskfactor when 1065 then value end) as GEM2L_gbr, sum(case riskfactor when 1120 then value end) as GEM2L_gbrc, sum(case riskfactor when 1066 then value end) as GEM2L_grc, sum(case riskfactor when 1121 then value end) as GEM2L_grcc, sum(case riskfactor when 1008 then value end) as GEM2L_growth, 
    sum(case riskfactor when 1031 then value end) as GEM2L_health, sum(case riskfactor when 1067 then value end) as GEM2L_hkg, sum(case riskfactor when 1122 then value end) as GEM2L_hkgc, sum(case riskfactor when 1030 then value end) as GEM2L_hshldprd, sum(case riskfactor when 1068 then value end) as GEM2L_hun, sum(case riskfactor when 1123 then value end) as GEM2L_hunc, sum(case riskfactor when 1069 then value end) as GEM2L_idn, sum(case riskfactor when 1124 then value end) as GEM2L_idnc, 
    sum(case riskfactor when 1070 then value end) as GEM2L_ind, sum(case riskfactor when 1125 then value end) as GEM2L_indc, sum(case riskfactor when 1036 then value end) as GEM2L_insuran, sum(case riskfactor when 1038 then value end) as GEM2L_internet, sum(case riskfactor when 1071 then value end) as GEM2L_irl, sum(case riskfactor when 1126 then value end) as GEM2L_irlc, sum(case riskfactor when 1072 then value end) as GEM2L_isr, sum(case riskfactor when 1127 then value end) as GEM2L_isrc, 
    sum(case riskfactor when 1073 then value end) as GEM2L_ita, sum(case riskfactor when 1128 then value end) as GEM2L_itac, sum(case riskfactor when 1074 then value end) as GEM2L_jor, sum(case riskfactor when 1129 then value end) as GEM2L_jorc, sum(case riskfactor when 1075 then value end) as GEM2L_jpn, sum(case riskfactor when 1130 then value end) as GEM2L_jpnc, sum(case riskfactor when 1076 then value end) as GEM2L_kor, sum(case riskfactor when 1131 then value end) as GEM2L_korc, 
    sum(case riskfactor when 1010 then value end) as GEM2L_leverage, sum(case riskfactor when 1009 then value end) as GEM2L_liquid, sum(case riskfactor when 1078 then value end) as GEM2L_mar, sum(case riskfactor when 1133 then value end) as GEM2L_marc, sum(case riskfactor when 1026 then value end) as GEM2L_media, sum(case riskfactor when 1079 then value end) as GEM2L_mex, sum(case riskfactor when 1134 then value end) as GEM2L_mexc, sum(case riskfactor when 1003 then value end) as GEM2L_momentum, 
    sum(case riskfactor when 1080 then value end) as GEM2L_mys, sum(case riskfactor when 1135 then value end) as GEM2L_mysc, sum(case riskfactor when 1081 then value end) as GEM2L_nld, sum(case riskfactor when 1136 then value end) as GEM2L_nldc, sum(case riskfactor when 1082 then value end) as GEM2L_nor, sum(case riskfactor when 1137 then value end) as GEM2L_norc, sum(case riskfactor when 1083 then value end) as GEM2L_nzl, sum(case riskfactor when 1138 then value end) as GEM2L_nzlc, 
    sum(case riskfactor when 1013 then value end) as GEM2L_oilexpl, sum(case riskfactor when 1012 then value end) as GEM2L_oilgas, sum(case riskfactor when 1085 then value end) as GEM2L_pak, sum(case riskfactor when 1140 then value end) as GEM2L_pakc, sum(case riskfactor when 1086 then value end) as GEM2L_per, sum(case riskfactor when 1141 then value end) as GEM2L_perc, sum(case riskfactor when 1033 then value end) as GEM2L_pharmac, sum(case riskfactor when 1087 then value end) as GEM2L_phl, 
    sum(case riskfactor when 1142 then value end) as GEM2L_phlc, sum(case riskfactor when 1088 then value end) as GEM2L_pol, sum(case riskfactor when 1143 then value end) as GEM2L_polc, sum(case riskfactor when 1017 then value end) as GEM2L_precmetl, sum(case riskfactor when 1089 then value end) as GEM2L_prt, sum(case riskfactor when 1144 then value end) as GEM2L_prtc, sum(case riskfactor when 1037 then value end) as GEM2L_realest, sum(case riskfactor when 1027 then value end) as GEM2L_retail, 
    sum(case riskfactor when 1091 then value end) as GEM2L_rus, sum(case riskfactor when 1146 then value end) as GEM2L_rusc, sum(case riskfactor when 1042 then value end) as GEM2L_semicond, sum(case riskfactor when 1093 then value end) as GEM2L_sgp, sum(case riskfactor when 1148 then value end) as GEM2L_sgpc, sum(case riskfactor when 1006 then value end) as GEM2L_size, sum(case riskfactor when 1007 then value end) as GEM2L_sizenonl, sum(case riskfactor when 1039 then value end) as GEM2L_software, 
    sum(case riskfactor when 1001 then value end) as GEM2L_srisk, sum(case riskfactor when 1018 then value end) as GEM2L_steel, sum(case riskfactor when 1094 then value end) as GEM2L_swe, sum(case riskfactor when 1149 then value end) as GEM2L_swec, sum(case riskfactor when 1043 then value end) as GEM2L_telecom, sum(case riskfactor when 1095 then value end) as GEM2L_tha, sum(case riskfactor when 1150 then value end) as GEM2L_thac, sum(case riskfactor when 1021 then value end) as GEM2L_transprt, 
    sum(case riskfactor when 1096 then value end) as GEM2L_tur, sum(case riskfactor when 1151 then value end) as GEM2L_turc, sum(case riskfactor when 1097 then value end) as GEM2L_twn, sum(case riskfactor when 1152 then value end) as GEM2L_twnc, sum(case riskfactor when 1098 then value end) as GEM2L_usa, sum(case riskfactor when 1153 then value end) as GEM2L_usac, 
    sum(case riskfactor when 1044 then value end) as GEM2L_utility, sum(case riskfactor when 1005 then value end) as GEM2L_value, sum(case riskfactor when 1004 then value end) as GEM2L_volatil, 
    sum(case riskfactor when 1002 then value end) as GEM2L_world, sum(case riskfactor when 1099 then value end) as GEM2L_zaf, sum(case riskfactor when 1154 then value end) as GEM2L_zafc 
    from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '01/01/2012' 
    and riskfactor >= 1000 and riskfactor <= 1999
    group by period_dt, vendorid 
    order by period_dt, vendorid

    pivoting query

    WITH p AS (
    select period_dt, vendorid, riskfactor, value 
    from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '01/01/2012' 
    and riskfactor >= 1000 and riskfactor <= 1999
    )
    select period_dt, vendorid, [1022] AS [GEM2L_1022_airlines], [1046] AS [GEM2L_1046_arg], [1101] AS [GEM2L_1101_argc], [1047] AS [GEM2L_1047_aus], [1102] AS [GEM2L_1102_ausc], [1048] AS [GEM2L_1048_aut], [1103] AS [GEM2L_1103_autc], [1023] AS [GEM2L_1023_autocomp], [1034] AS [GEM2L_1034_banks], [1049] AS [GEM2L_1049_bel], [1104] AS [GEM2L_1104_belc], [1032] AS [GEM2L_1032_biotech], [1051] AS [GEM2L_1051_bra], [1106] AS [GEM2L_1106_brac], [1052] AS [GEM2L_1052_can], [1107] AS [GEM2L_1107_canc], [1019] AS [GEM2L_1019_capgoods], [1053] AS [GEM2L_1053_che], [1108] AS [GEM2L_1108_chec], 
    [1014] AS [GEM2L_1014_chemical], [1054] AS [GEM2L_1054_chl], [1109] AS [GEM2L_1109_chlc], [1055] AS [GEM2L_1055_chn], [1110] AS [GEM2L_1110_chnc], [1056] AS [GEM2L_1056_chx], [1057] AS [GEM2L_1057_col], [1111] AS [GEM2L_1111_colc], [1020] AS [GEM2L_1020_commsvcs], [1040] AS [GEM2L_1040_communic], [1041] AS [GEM2L_1041_computer], [1024] AS [GEM2L_1024_consdur], [1015] AS [GEM2L_1015_constpp], [1025] AS [GEM2L_1025_consvcs], [1058] AS [GEM2L_1058_cze], [1112] AS [GEM2L_1112_czec], [1059] AS [GEM2L_1059_deu], [1113] AS [GEM2L_1113_deuc], [1035] AS [GEM2L_1035_divfinan], 
    [1016] AS [GEM2L_1016_divmetal], [1060] AS [GEM2L_1060_dnk], [1114] AS [GEM2L_1114_dnkc], [1061] AS [GEM2L_1061_egy], [1115] AS [GEM2L_1115_egyc], [1116] AS [GEM2L_1116_emuc], [1011] AS [GEM2L_1011_energy], [1062] AS [GEM2L_1062_esp], [1117] AS [GEM2L_1117_espc], [1063] AS [GEM2L_1063_fin], [1118] AS [GEM2L_1118_finc], [1029] AS [GEM2L_1029_foodprd], [1028] AS [GEM2L_1028_foodretl], [1064] AS [GEM2L_1064_fra], [1119] AS [GEM2L_1119_frac], [1065] AS [GEM2L_1065_gbr], [1120] AS [GEM2L_1120_gbrc], [1066] AS [GEM2L_1066_grc], [1121] AS [GEM2L_1121_grcc], [1008] AS [GEM2L_1008_growth], 
    [1031] AS [GEM2L_1031_health], [1067] AS [GEM2L_1067_hkg], [1122] AS [GEM2L_1122_hkgc], [1030] AS [GEM2L_1030_hshldprd], [1068] AS [GEM2L_1068_hun], [1123] AS [GEM2L_1123_hunc], [1069] AS [GEM2L_1069_idn], [1124] AS [GEM2L_1124_idnc], [1070] AS [GEM2L_1070_ind], [1125] AS [GEM2L_1125_indc], [1036] AS [GEM2L_1036_insuran], [1038] AS [GEM2L_1038_internet], [1071] AS [GEM2L_1071_irl], [1126] AS [GEM2L_1126_irlc], [1072] AS [GEM2L_1072_isr], [1127] AS [GEM2L_1127_isrc], [1073] AS [GEM2L_1073_ita], [1128] AS [GEM2L_1128_itac], [1074] AS [GEM2L_1074_jor], [1129] AS [GEM2L_1129_jorc], 
    [1075] AS [GEM2L_1075_jpn], [1130] AS [GEM2L_1130_jpnc], [1076] AS [GEM2L_1076_kor], [1131] AS [GEM2L_1131_korc], [1010] AS [GEM2L_1010_leverage], [1009] AS [GEM2L_1009_liquid], [1078] AS [GEM2L_1078_mar], [1133] AS [GEM2L_1133_marc], [1026] AS [GEM2L_1026_media], [1079] AS [GEM2L_1079_mex], [1134] AS [GEM2L_1134_mexc], [1003] AS [GEM2L_1003_momentum], [1080] AS [GEM2L_1080_mys], [1135] AS [GEM2L_1135_mysc], [1081] AS [GEM2L_1081_nld], [1136] AS [GEM2L_1136_nldc], [1082] AS [GEM2L_1082_nor], [1137] AS [GEM2L_1137_norc], [1083] AS [GEM2L_1083_nzl], [1138] AS [GEM2L_1138_nzlc], 
    [1013] AS [GEM2L_1013_oilexpl], [1012] AS [GEM2L_1012_oilgas], [1085] AS [GEM2L_1085_pak], [1140] AS [GEM2L_1140_pakc], [1086] AS [GEM2L_1086_per], [1141] AS [GEM2L_1141_perc], [1033] AS [GEM2L_1033_pharmac], [1087] AS [GEM2L_1087_phl], [1142] AS [GEM2L_1142_phlc], [1088] AS [GEM2L_1088_pol], [1143] AS [GEM2L_1143_polc], [1017] AS [GEM2L_1017_precmetl], [1089] AS [GEM2L_1089_prt], [1144] AS [GEM2L_1144_prtc], [1037] AS [GEM2L_1037_realest], [1027] AS [GEM2L_1027_retail], [1091] AS [GEM2L_1091_rus], [1146] AS [GEM2L_1146_rusc], [1042] AS [GEM2L_1042_semicond], [1093] AS [GEM2L_1093_sgp], 
    [1148] AS [GEM2L_1148_sgpc], [1006] AS [GEM2L_1006_size], [1007] AS [GEM2L_1007_sizenonl], [1039] AS [GEM2L_1039_software], 
    [1001] AS [GEM2L_1001_srisk], [1018] AS [GEM2L_1018_steel], [1094] AS [GEM2L_1094_swe], [1149] AS [GEM2L_1149_swec], [1043] AS [GEM2L_1043_telecom], 
    [1095] AS [GEM2L_1095_tha], [1150] AS [GEM2L_1150_thac], [1021] AS [GEM2L_1021_transprt], [1096] AS [GEM2L_1096_tur], [1151] AS [GEM2L_1151_turc], 
    [1097] AS [GEM2L_1097_twn], [1152] AS [GEM2L_1152_twnc], [1098] AS [GEM2L_1098_usa], [1153] AS [GEM2L_1153_usac], [1044] AS [GEM2L_1044_utility], 
    [1005] AS [GEM2L_1005_value], [1004] AS [GEM2L_1004_volatil], [1002] AS [GEM2L_1002_world], [1099] AS [GEM2L_1099_zaf], [1154] AS [GEM2L_1154_zafc]
    from p
    PIVOT (
     sum(value)
     FOR riskfactor IN(
       [1022], [1046], [1101], [1047], [1102], [1048], [1103], [1023], [1034], [1049], [1104], [1032], [1051], [1106], [1052], [1107], [1019], [1053], [1108], [1014], [1054], [1109], [1055], [1110], [1056], [1057], [1111], [1020], [1040], [1041], [1024], [1015], [1025], [1058], [1112], [1059], [1113], [1035], [1016], [1060], [1114], [1061], [1115], [1116], [1011], [1062], [1117], [1063], [1118], [1029], [1028], [1064], [1119], [1065], [1120], [1066], [1121], [1008], [1031], [1067], [1122], [1030], [1068], [1123], [1069], [1124], [1070], [1125], 
    [1036], [1038], [1071], [1126], [1072], [1127], [1073], [1128], [1074], [1129], [1075], [1130], [1076], [1131], [1010], [1009], [1078], [1133], [1026], [1079], [1134], [1003], [1080], [1135], [1081], [1136], [1082], [1137], [1083], [1138], [1013], [1012], [1085], [1140], [1086], [1141], [1033], [1087], [1142], [1088], [1143], [1017], [1089], [1144], [1037], [1027], [1091], [1146], [1042], [1093], [1148], [1006], [1007], [1039], [1001], [1018], [1094], [1149], [1043], [1095], [1150], [1021], [1096], [1151], [1097], [1152], [1098], [1153], [1044], [1005], [1004], [1002], [1099], [1154]
     )
    ) AS pvt
    order by period_dt, vendorid


    fj


    • Edited by f.j_ Monday, December 10, 2012 9:57 PM
    Monday, December 10, 2012 9:52 PM
  • Just to make sure the queries are exactly the same, try

    WITH p AS (
    select period_dt, vendorid, riskfactor, value 
    from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '01/01/2012' 
    and riskfactor >= 1000 and riskfactor <= 1999
    )

    select SUM(...)

    from p 

    GROUP BY ...

    ORDER BY ...

    If you can post the XML version of the query plan, it may also help. 

    Also, are your stats and indexes are up to date and are you running against the same table? Also, what happens when you first run PIVOT query and then SUM query?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 10, 2012 10:31 PM
    Moderator
  • OK, got it. That's what you meant by same CTE. Thank you.

    I changed the "group-by" query as below. The execution plan have almost two thousand lines, I am not sure if it's appropriate to post.  But here is my takeaway, the big difference should be caused by the first step of execution plan, which is the index scan to retrieve all data needed. I would think both queries will search on predicates and retrieve a smaller data set to work with. The pivoting query does it, but I don't know why the "group-by" query doesn't do it. And it caused the subsequent steps run much slower.

    The index used in step 1 is a non-clustered index, with key of period and riskfactor. vendorid and value are "included".  BTW, the pivoting query runs first and both queries are running against same DB and table. We have a maintanance plan to rebuild index, I checked the fragment is only 0.01.

    -- group by query code --
    WITH p AS (
    select period_dt, vendorid, riskfactor, value 
    from cmmn_risk_exposures 
    where period_dt >= '01/01/1997' and period_dt <= '01/01/2012' 
    and riskfactor >= 1000 and riskfactor <= 1999
    )
    select period_dt, vendorid, sum(case riskfactor when 1022 then value end) as GEM2L_airlines, sum(case riskfactor when 1046 then value end) as GEM2L_arg, sum(case riskfactor when 1101 then value end) as GEM2L_argc, sum(case riskfactor when 1047 then value end) as GEM2L_aus, sum(case riskfactor when 1102 then value end) as GEM2L_ausc, sum(case riskfactor when 1048 then value end) as GEM2L_aut, sum(case riskfactor when 1103 then value end) as GEM2L_autc, 
    sum(case riskfactor when 1023 then value end) as GEM2L_autocomp, sum(case riskfactor when 1034 then value end) as GEM2L_banks, sum(case riskfactor when 1049 then value end) as GEM2L_bel, sum(case riskfactor when 1104 then value end) as GEM2L_belc, sum(case riskfactor when 1032 then value end) as GEM2L_biotech, sum(case riskfactor when 1051 then value end) as GEM2L_bra, sum(case riskfactor when 1106 then value end) as GEM2L_brac, 
    sum(case riskfactor when 1052 then value end) as GEM2L_can, sum(case riskfactor when 1107 then value end) as GEM2L_canc, sum(case riskfactor when 1019 then value end) as GEM2L_capgoods, sum(case riskfactor when 1053 then value end) as GEM2L_che, sum(case riskfactor when 1108 then value end) as GEM2L_chec, sum(case riskfactor when 1014 then value end) as GEM2L_chemical, sum(case riskfactor when 1054 then value end) as GEM2L_chl, 
    sum(case riskfactor when 1109 then value end) as GEM2L_chlc, sum(case riskfactor when 1055 then value end) as GEM2L_chn, sum(case riskfactor when 1110 then value end) as GEM2L_chnc, sum(case riskfactor when 1056 then value end) as GEM2L_chx, sum(case riskfactor when 1057 then value end) as GEM2L_col, sum(case riskfactor when 1111 then value end) as GEM2L_colc, sum(case riskfactor when 1020 then value end) as GEM2L_commsvcs, 
    sum(case riskfactor when 1040 then value end) as GEM2L_communic, sum(case riskfactor when 1041 then value end) as GEM2L_computer, sum(case riskfactor when 1024 then value end) as GEM2L_consdur, sum(case riskfactor when 1015 then value end) as GEM2L_constpp, sum(case riskfactor when 1025 then value end) as GEM2L_consvcs, sum(case riskfactor when 1058 then value end) as GEM2L_cze, sum(case riskfactor when 1112 then value end) as GEM2L_czec, 
    sum(case riskfactor when 1059 then value end) as GEM2L_deu, sum(case riskfactor when 1113 then value end) as GEM2L_deuc, sum(case riskfactor when 1035 then value end) as GEM2L_divfinan, sum(case riskfactor when 1016 then value end) as GEM2L_divmetal, sum(case riskfactor when 1060 then value end) as GEM2L_dnk, sum(case riskfactor when 1114 then value end) as GEM2L_dnkc, sum(case riskfactor when 1061 then value end) as GEM2L_egy, 
    sum(case riskfactor when 1115 then value end) as GEM2L_egyc, sum(case riskfactor when 1116 then value end) as GEM2L_emuc, sum(case riskfactor when 1011 then value end) as GEM2L_energy, sum(case riskfactor when 1062 then value end) as GEM2L_esp, sum(case riskfactor when 1117 then value end) as GEM2L_espc, sum(case riskfactor when 1063 then value end) as GEM2L_fin, sum(case riskfactor when 1118 then value end) as GEM2L_finc, sum(case riskfactor when 1029 then value end) as GEM2L_foodprd, 
    sum(case riskfactor when 1028 then value end) as GEM2L_foodretl, sum(case riskfactor when 1064 then value end) as GEM2L_fra, sum(case riskfactor when 1119 then value end) as GEM2L_frac, sum(case riskfactor when 1065 then value end) as GEM2L_gbr, sum(case riskfactor when 1120 then value end) as GEM2L_gbrc, sum(case riskfactor when 1066 then value end) as GEM2L_grc, sum(case riskfactor when 1121 then value end) as GEM2L_grcc, sum(case riskfactor when 1008 then value end) as GEM2L_growth, 
    sum(case riskfactor when 1031 then value end) as GEM2L_health, sum(case riskfactor when 1067 then value end) as GEM2L_hkg, sum(case riskfactor when 1122 then value end) as GEM2L_hkgc, sum(case riskfactor when 1030 then value end) as GEM2L_hshldprd, sum(case riskfactor when 1068 then value end) as GEM2L_hun, sum(case riskfactor when 1123 then value end) as GEM2L_hunc, sum(case riskfactor when 1069 then value end) as GEM2L_idn, sum(case riskfactor when 1124 then value end) as GEM2L_idnc, 
    sum(case riskfactor when 1070 then value end) as GEM2L_ind, sum(case riskfactor when 1125 then value end) as GEM2L_indc, sum(case riskfactor when 1036 then value end) as GEM2L_insuran, sum(case riskfactor when 1038 then value end) as GEM2L_internet, sum(case riskfactor when 1071 then value end) as GEM2L_irl, sum(case riskfactor when 1126 then value end) as GEM2L_irlc, sum(case riskfactor when 1072 then value end) as GEM2L_isr, sum(case riskfactor when 1127 then value end) as GEM2L_isrc, 
    sum(case riskfactor when 1073 then value end) as GEM2L_ita, sum(case riskfactor when 1128 then value end) as GEM2L_itac, sum(case riskfactor when 1074 then value end) as GEM2L_jor, sum(case riskfactor when 1129 then value end) as GEM2L_jorc, sum(case riskfactor when 1075 then value end) as GEM2L_jpn, sum(case riskfactor when 1130 then value end) as GEM2L_jpnc, sum(case riskfactor when 1076 then value end) as GEM2L_kor, sum(case riskfactor when 1131 then value end) as GEM2L_korc, 
    sum(case riskfactor when 1010 then value end) as GEM2L_leverage, sum(case riskfactor when 1009 then value end) as GEM2L_liquid, sum(case riskfactor when 1078 then value end) as GEM2L_mar, sum(case riskfactor when 1133 then value end) as GEM2L_marc, sum(case riskfactor when 1026 then value end) as GEM2L_media, sum(case riskfactor when 1079 then value end) as GEM2L_mex, sum(case riskfactor when 1134 then value end) as GEM2L_mexc, sum(case riskfactor when 1003 then value end) as GEM2L_momentum, 
    sum(case riskfactor when 1080 then value end) as GEM2L_mys, sum(case riskfactor when 1135 then value end) as GEM2L_mysc, sum(case riskfactor when 1081 then value end) as GEM2L_nld, sum(case riskfactor when 1136 then value end) as GEM2L_nldc, sum(case riskfactor when 1082 then value end) as GEM2L_nor, sum(case riskfactor when 1137 then value end) as GEM2L_norc, sum(case riskfactor when 1083 then value end) as GEM2L_nzl, sum(case riskfactor when 1138 then value end) as GEM2L_nzlc, 
    sum(case riskfactor when 1013 then value end) as GEM2L_oilexpl, sum(case riskfactor when 1012 then value end) as GEM2L_oilgas, sum(case riskfactor when 1085 then value end) as GEM2L_pak, sum(case riskfactor when 1140 then value end) as GEM2L_pakc, sum(case riskfactor when 1086 then value end) as GEM2L_per, sum(case riskfactor when 1141 then value end) as GEM2L_perc, sum(case riskfactor when 1033 then value end) as GEM2L_pharmac, sum(case riskfactor when 1087 then value end) as GEM2L_phl, 
    sum(case riskfactor when 1142 then value end) as GEM2L_phlc, sum(case riskfactor when 1088 then value end) as GEM2L_pol, sum(case riskfactor when 1143 then value end) as GEM2L_polc, sum(case riskfactor when 1017 then value end) as GEM2L_precmetl, sum(case riskfactor when 1089 then value end) as GEM2L_prt, sum(case riskfactor when 1144 then value end) as GEM2L_prtc, sum(case riskfactor when 1037 then value end) as GEM2L_realest, sum(case riskfactor when 1027 then value end) as GEM2L_retail, 
    sum(case riskfactor when 1091 then value end) as GEM2L_rus, sum(case riskfactor when 1146 then value end) as GEM2L_rusc, sum(case riskfactor when 1042 then value end) as GEM2L_semicond, sum(case riskfactor when 1093 then value end) as GEM2L_sgp, sum(case riskfactor when 1148 then value end) as GEM2L_sgpc, sum(case riskfactor when 1006 then value end) as GEM2L_size, sum(case riskfactor when 1007 then value end) as GEM2L_sizenonl, sum(case riskfactor when 1039 then value end) as GEM2L_software, 
    sum(case riskfactor when 1001 then value end) as GEM2L_srisk, sum(case riskfactor when 1018 then value end) as GEM2L_steel, sum(case riskfactor when 1094 then value end) as GEM2L_swe, sum(case riskfactor when 1149 then value end) as GEM2L_swec, sum(case riskfactor when 1043 then value end) as GEM2L_telecom, sum(case riskfactor when 1095 then value end) as GEM2L_tha, sum(case riskfactor when 1150 then value end) as GEM2L_thac, sum(case riskfactor when 1021 then value end) as GEM2L_transprt, 
    sum(case riskfactor when 1096 then value end) as GEM2L_tur, sum(case riskfactor when 1151 then value end) as GEM2L_turc, sum(case riskfactor when 1097 then value end) as GEM2L_twn, sum(case riskfactor when 1152 then value end) as GEM2L_twnc, sum(case riskfactor when 1098 then value end) as GEM2L_usa, sum(case riskfactor when 1153 then value end) as GEM2L_usac, 
    sum(case riskfactor when 1044 then value end) as GEM2L_utility, sum(case riskfactor when 1005 then value end) as GEM2L_value, sum(case riskfactor when 1004 then value end) as GEM2L_volatil, 
    sum(case riskfactor when 1002 then value end) as GEM2L_world, sum(case riskfactor when 1099 then value end) as GEM2L_zaf, sum(case riskfactor when 1154 then value end) as GEM2L_zafc 
    from p
      group by period_dt, vendorid 
    order by period_dt, vendorid


    fj


    • Edited by f.j_ Tuesday, December 11, 2012 4:43 PM
    Tuesday, December 11, 2012 4:33 PM
  • What is your SQL Server version? I guess the amount of the fields makes the difference. Your case is not very typical scenario, but since you found that PIVOT works much faster for your case, I think you should stay with PIVOT. It will be nice if someone from MS SQL Server team or a person who is good with knowing internal working of optimizer can comment in this thread.

    One extra suggestion I can give (assuming that the CTE does not return too many rows) is instead of CTE try to use temporary table and see, what will be performance in this case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 11, 2012 5:04 PM
    Moderator