Need help optimizing

Database
Enthusiast

Need help optimizing

Hi!

I need help to tune this query . I have omiited somw "when' statements and put some dashes(--) to simplfy.
I am also not sure how the 2 'CASE WHEN ' statements work.
SELECT DISTINCT 'SB' AS c1 , D1.c1 AS c2 , D1.c3 AS c3 ,
D1.c2 AS c4
FROM (
SELECT COUNT ( T1."SR_NUM" ) AS c1 ,
CASE WHEN
CASE WHEN T1."AGE" <= 2 THEN '0-2'
---------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '0-2' THEN 0
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
--------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '3-4' THEN 3
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '5-6' THEN 5
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
-----------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '7-10' THEN 7
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
-------------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '11-20' THEN 11
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
---------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '21-50' THEN 21
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '>50' THEN 50
END AS c2 ,
CASE WHEN T1."AGE" <= 2 THEN '0-2'
WHEN T1."AGE" BETWEEN 3 AND 4 THEN '3-4'
------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END AS c3
FROM "POSTN_HIST" T4 , (
SELECT SF.OWN_POS AS OWN_POS , SD.SR_NUM AS SR_NUM ,
SD.STUS , SUM ( D.x_tiaa_business_day ) AS AGE ,
SF.OPN_DT , SF.CONTACT
FROM D_SD , DX SDX , F SF , DAY_D D
WHERE SD.ROW = SDX.ROW
AND SF.SR = SD.ROW
AND SDX.X_CLS_DT IS NULL
AND SF.OWN_POS <> 0
AND SDX.X_SR_CAT_TYPE_CD = 'SB'
AND SD.STUS <> 'Cnc'
AND D.ROW BETWEEN SF.OPN_DT AND 20110602
GROUP BY 1 , 2 , 3 , 5 , 6 ) T1
WHERE ( T4."ROW" = T1."OWN_POS"
AND T4."LVL_POSTN_ID" = '1-2YPMF1'
AND T1."OPN_DT" BETWEEN T4."EFF_DT" AND T4."END_DT" )
GROUP BY
CASE WHEN
CASE WHEN T1."AGE" <= 2 THEN '0-2'
WHEN T1."AGE" BETWEEN 3 AND 4 THEN '3-4'
-------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '0-2' THEN 0
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
--------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '3-4' THEN 3
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
-----------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '5-6' THEN 5
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
---------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '7-10' THEN 7
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
-------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '11-20' THEN 11
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
------------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '21-50' THEN 21
WHEN CASE
WHEN T1."AGE" <= 2 THEN '0-2'
------------------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END = '>50' THEN 50
END ,
CASE WHEN T1."AGE" <= 2 THEN '0-2'
---------------------------
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END ) D1
ORDER BY 4

Thanks!
3 REPLIES
Junior Contributor

Re: Need help optimizing

Who wrote that query?
This is one of the most nonsens CASEs if ever seen :-)

It's just two simple age group calculations:
CASE
WHEN T1."AGE" <= 2 THEN 0
...
WHEN T1."AGE" BETWEEN 21 AND 50 THEN 21
ELSE 50
END as c2
,
CASE
WHEN T1."AGE" <= 2 THEN '0-2'
...
WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'
ELSE '>50'
END as c3

and in Teradata you can use an alias in any place, so the GROUP BY is reduced to:
GROUP BY c2,c3

Dieter
Enthusiast

Re: Need help optimizing

I did change this but I do not see any improvement in the performance of the query. The IOcount , CPUTIME and spool usage have all increased a little bit by the chnages. IS there any way to tune it?
Junior Contributor

Re: Need help optimizing

Hard to tell without additional info about DDL/stats/explain/DBQL.

But my guess would be the inner query to calculate the "age", this is probably a product join due to the BETWEEN creating a large intermediate result set before doing the SUM.

There should be another column in your calendar table based on the calculation of:
SUM (x_tiaa_business_day) OVER (ORDER BY x_tiaa_business_day ROWS UNBOUNDED PRECEDING)
Now you need two joins to the calendar table, but both are equi-joins on OPN_DT and 20110602 and the AGE is a simple difference.

In TD13 the join on 20110602 could be rewritten as a Scalar Subquery:
(SELECT xxx FROM calendar WHERE calendardate = 20110602)

Dieter