CASE expression simplification for performance improvement
Example with simplification:
Optimized Statement:
-------------------
SELECT
Q5.LADDER AS "MEASURE1",
Q5.$C1 AS "MEASURE2",
Q5.$C2 AS "CUSTOMERS",
Q5.$C3 AS "SPEND",
Q5.$C4 AS "TRANSACTIONS",
Q5.$C5 AS "UNITS"
FROM
(SELECT
Q4.LADDER,
Q4.$C1,
COUNT(DISTINCT Q4.ACCOUNT_NUMBER),
SUM(Q4.SALES),
COUNT(DISTINCT Q4.TRANSACTION_ID),
SUM(Q4.UNITS)
FROM
(SELECT
Q3.LADDER,
Q2.BRAND_NAME,
Q1.ACCOUNT_NUMBER,
Q1.SALES,
Q1.TRANSACTION_ID,
Q1.UNITS
FROM
DM.TILL_TRANSACTION_LINE AS Q1,
DM.ITEM AS Q2,
DM.LADDER AS Q3
WHERE
(Q1.COUNTRY_CODE <> 'IE ') AND
(Q1.CATEGORY_ID > 0) AND
(Q1.ITEM_CODE = Q2.ITEM_CODE) AND
(Q1.LADDER_ID = Q3.LADDER_ID) AND
(0 < Q1.UNITS) AND
('2013-08-01' <= Q1.TRANSACTION_DATE) AND
(Q1.TRANSACTION_DATE <= '2014-07-31') AND
Q1.CATEGORY_ID IN (280, 310)
) AS Q4
GROUP BY
Q4.$C1,
Q4.LADDER
) AS Q5
Example without simplification:
Optimized Statement:
-------------------
SELECT
Q11.$C0 AS "MEASURE1",
Q11.$C1 AS "MEASURE2",
Q11.$C2 AS "CUSTOMERS",
Q11.$C3 AS "SPEND",
Q11.$C4 AS "TRANSACTIONS",
Q11.$C5 AS "UNITS"
FROM
(SELECT
Q10.$C0,
Q10.$C1,
COUNT(DISTINCT Q10.ACCOUNT_NUMBER),
SUM(Q10.SALES),
COUNT(DISTINCT Q10.TRANSACTION_ID),
SUM(Q10.UNITS)
FROM
(SELECT
CASE
WHEN ('LADDER' = 'GENDER')
THEN Q1.GENDER
WHEN ('LADDER' = 'LIFESTAGE_SEGMENT_NAME')
THEN Q6.LIFESTAGE_SEGMENT_NAME
WHEN ('LADDER' = 'TYPE_OF_STORE_NAME')
THEN Q5.TYPE_OF_STORE_NAME
WHEN ('LADDER' = 'CATEGORY')
THEN Q7.CATEGORY
WHEN ('LADDER' = 'LADDER')
THEN Q8.LADDER
WHEN ('LADDER' = 'SUB_LADDER')
THEN Q9.SUB_LADDER
WHEN ('LADDER' = 'BRAND_NAME')
THEN Q4.BRAND_NAME
WHEN ('LADDER' = 'SUB_BRAND_NAME')
THEN Q4.SUB_BRAND_NAME
WHEN ('LADDER' = 'AGE_BAND')
etc |
