Tuning SQL query : subquery with aggregate function on the same table

Question

The following query takes approximately 30 seconds to give results. table1 contains ~20m lines table2 contains ~10000 lines

I'm trying to find a way to improve performances. Any ideas ?

declare @PreviousMonthDate datetime 
select @PreviousMonthDate = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101') as [PreviousMonthDate])

     select  
 distinct(t1.code), t1.ent, t3.lib, t3.typ from table1 t1, table2 t3
     where (select min(t2.dat) from table1 t2 where   t2.code=t1.code) >@PreviousMonthDate
and t1.ent in ('XXX')
and t1.code=t3.cod
and t1.dat>@PreviousMonthDate

Thanks

Answer 1

This is your query, more sensibly written:

 select t1.code, t1.ent, t2.lib, t2.typ
 from table1 t1 join
      table2 t2
      on t1.code = t2.cod
 where not exists (select 1
                   from table1 tt1
                   where tt1.code = t1.code and
                         tt1.dat <= @PreviousMonthDate 
                  ) and
       t1.ent = 'XXX' and 
       t1.dat > @PreviousMonthDate;

For this query, you want the following indexes:

• table1(ent, dat, code) -- for the where
• table1(code, dat) -- for the subquery
• table2(cod, lib, typ) -- for the join

Notes:

• Table aliases should make sense. t3 for table2 is cognitively dissonant, even though I know these are made up names.
• not exists (especially with the right indexes) should be faster than the aggregation subquery.
• The indexes will satisfy the where clause, reducing the data needed for filtering.
• select distinct is a statement. distinct is not a function, so the parentheses do nothing.
• Never use comma in the FROM clause. Always use proper, explicit, standard JOIN syntax.