SQL subquery and join performance issue

Question

       `select * from DETAIL a
        where a.BUILD > GETDATE() - 90 s
        and (a.IN + a.Rt) NOT IN (SELECT Sample_IN + Rt FROM  SUMMARY)                  
        and (a.Rt + a.Err) IN 
       (SELECT Rt + Err 
        FROM SUMMARY 
        where (sample_in + rt + err) NOT IN 
       (SELECT in + rt + err FROM DETAIL)) 
        group by a.rt, a.plant, a.in, a.build`

This query is showing performance issues , it runs faster in sql2000 server , but exhibits poor performance in sql2008R2. The tables in both the ennvironment have same properties(column datatypes and indexes) . I guess there is some propablity in the "+" operator for select clause. can anyone help me ?

Answer 1

Indexes do not work when you concatenate fields. You can create columns in your table that will already combine those fields, and create an index on those fields. This will improve your performances.

Also, note that this query will run faster and use your current indexes (forgive me for typos, you did not include tables definition) :

select * 
  from DETAIL a
 where a.BUILD > DateAdd( Day, -90, GetDate() )
   and not exists ( select null 
                      from SUMMARY 
                     where SUMMARY.Sample_IN = a.IN and SUMMARY.Rt  = a.Rt )
   and exists ( select null
                 from SUMMARY 
                where not exists ( select null
                                     from DETAIL 
                                    where DETAIL.in = SUMMARY.Sample_IN 
                                      and DETAIL.Rt = SUMMARY.Rt 
                                      and DETAIL.Err = SUMMARY.Err)
                  and a.Rt = SUMMARY.Rt
                  and a.Err = SUMMARY.Err ) 
group by a.rt, a.plant, a.in, a.build