Archives of the TeradataForum

Message Posted: Thu, 19 Feb 2009 @ 21:44:25 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Too much time spent on aggregate function

From:		Dieter Noeth

Simard Rudel wrote:

I try to have more performance with this kind of SQL model. We spend too much time to get the 'last group of service', the 'last state' and the 'first service' information.

If this i done to return additional columns for the 'last' row, then RANK/ROW_NUMBER is probably the fastest version, e.g.:

     SELECT * FROM lineitem
     WHERE (l_partkey, l_shipdate, l_quantity) IN
       (SELECT l_partkey, l_shipdate, MAX(l_quantity)
        FROM lineitem l1
        GROUP BY 1,2);

is rewritten as:

     SELECT * FROM lineitem
     QUALIFY
        RANK() OVER
            (PARTITION BY l_partkey, l_shipdate
             ORDER BY l_quantity DESC) = 1;

     > >     ---------- Last group of service    ----------
     > >
     > >     LEFT JOIN ((SELECT A.NO,A.NO_ACTIV,A.NO_ASK,
     > >                        MAX(R.NO_REGR_MES) NO_LAST_GROUP_SERV
     > > <========== aggregate operation
     > >                 FROM   ........
     > >                 GROUP BY 1,2,3) AS TNO_REGR
     > > <========== derive table
     > >
     > >                INNER JOIN DB_NAME.TREGR_MES ON
     > >                    .........
     > >                    AND TNO_REGR.NO_LAST_GROUP_SERV =
     > > TREGR_MES.NO_REGR_MES)
     > >
     > >     ON .....

     qualify
        rank() over
         (partition by A.NO,A.NO_ACTIV,A.NO_ASK
          order by R.NO_REGR_MES desc) = 1

If NO_REGR_MES is not unique you might have to use ROW_NUMBER, but RANK might have better performance.

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference