SQL Query performance (break-up one query into smaller ones or not)

Question

I have a SQL query (run on a Teradata platform) which features 1) an inner join 2) where clause 3) group by

It takes about 40 minutes to run and I want to make it faster. (I don't have privileges to create an INDEX for this table). The code is below. Should I create an intermediate table W using just the WHERE clause and another intermediate table to filter out the rows of W that intersect with table X and then finally do an inner join ?

    create table ABC as
    (select 
        b.acct_nb,
        max(b.lst_pmt) as pmt       
     from
     Y as b inner join X as a on 
     a.acct_nb = b.acct_nb
     where b.amount > 0
     group by b.acct_nb
     );

Proposed code:

    create table W as
    select acct_nb,amount
    from Y
    where amount > 0;

    create table W2 as
    select a.acct_nb,b.amount
    from X as a inner join W as b
    on a.acct_nb = b.acct_nb;


    create table ABC as
    select a.acct_nb,max(b.lst_pmt) as pmt
    from W2 as a inner join Y as b
    on a.acct_nb = b.acct_nb
    group by b.acct_nb;

quit;

Answer 1

The proposed code will probably not improve performance.

Whithout Explain or QueryLog info it's hard to advice, but you might try to aggregate before the join:

select b.*
from 
 (
   select 
      acct_nb,
      max(lst_pmt) as pmt       
   from Y
   where amount > 0
   group by b.acct_nb
 ) as b 
inner join X as a 
on a.acct_nb = b.acct_nb