Difference between 2 queries

Database
Enthusiast

Difference between 2 queries

Hi all!

I would like to know if logically there is any difference between the 2 queries below. The first ones runs within 5 mins and the second one takes hours and hours, so I cannot compare the results.

The only difference is the group by clause in the DERV ( sub-select) . Please let me what you think.

select  A."PER_NM_YR" as c1,      DERV."FNC_RLE" as c2 ,     count(distinct E."INTERACTION_ID") as c3

from         ( sel* from   "DAY_D"  where "DAY_DT" = TIMESTAMP '2011-08-22 00:00:00' )  A

                                                ,   ( sel a.PRIM_PREM_PD_ACT,a.ROWID as a1 ,b.FNC_RLE,b.CNTCT

                                                                                 from "PRSN_D_CST" a   join ACTVTY b

                                                        on  a.ROWID = b.CNTCT  group by 1,2,3,4

                                                         ) derv -- DERV

                                                left outer join  "ORG_X" D

                                                On          D."ROWID" = DERV."PRIM_PREM_PD_ACT"

                                                , "CALL_SGMT" E

where  ( DERV.a1 = E."CNTCT"

                and       E."DT" = A."ROWID"

group   by 1,2

order    by 1, 2

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

select  A."PER_NM_YR" as c1,      DERV."FNC_RLE" as c2 ,     count(distinct E."INTERACTION_ID") as c3

from         ( sel* from   "DAY_D"  where "DAY_DT" = TIMESTAMP '2011-08-22 00:00:00' )  A

                                                ,   ( sel a.PRIM_PREM_PD_ACT,a.ROWID as a1 ,b.FNC_RLE,b.CNTCT

                                                                                 from "PRSN_D_CST" a   join ACTVTY b

                                                        on  a.ROWID = b.CNTCT

                                                         ) derv -- DERV

                                                left outer join  "ORG_X" D

                                                On          D."ROWID" = DERV."PRIM_PREM_PD_ACT"

                                     , "CALL_SGMT" E

where  ( DERV.a1 = E."CNTCT"

                and       E."DT" = A."ROWID"

group   by 1,2

order    by 1, 2

Thanks!!

2 REPLIES
Enthusiast

Re: Difference between 2 queries

Well, first you're missing a ")" in the first query. Before the last group by.

But to your original question, it appears that the two should return the same result set, although with vastly different performance (as you've seen). By having the group by in the derived table, you're asking Teradata to first eliminate duplicates in the derived table and then take that set of unique values and use it in the join. This will eliminate a lot of unnecessary join operations. Without that clause in your second query, you are taking every row in that derived table and attempting the join. This could lead to a lot of duplicate joins which are eliminated in the outer portion of the query by the GROUP BY and COUNT(DISTINCT...) operations. They *should* return the same results, but query 1 should be much faster.
Enthusiast

Re: Difference between 2 queries

Thanks Mnylin! I just needed a second pair of eyes.