Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023