Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2011
11:20 AM

06-03-2011
11:20 AM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2011
02:43 PM

06-06-2011
02:43 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-10-2011
02:29 PM

06-10-2011
02:29 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2011
02:04 AM

06-13-2011
02:04 AM

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

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