SQL Server Performance

left join and is null sub query is too slow, plz help

Discussion in 'Contribute Your Performance and Clustering Tips' started by i.netdeveloper, Mar 15, 2013.

  1. i am trying got data from table a and table b
    in a sub query. I have to do a left outer join and in where clause I have to check if the column1 is null.
    but the query is taking 14 seconds, which should take less than a sec.

    Any advice?

    select * from tablea a
    inner join tablec c on a.col1 = c.col1
    left outer join tableb b on a.col1 = b.col1 and a.col2 = b.col2
    where c.col2 = something and b.col3 is null;
  2. FrankKalis Moderator

    Welcome to the forum!
    Your reference b.col3 in the WHERE clause is turning the LEFT JOIN effectively into an INNER JOIN. It might be better to move this from WHERE into the LEFT JOIN condition.
    As for the performance: Do you have appropriate indexes in place? What is the execution plan look like?
  3. Thanks for the reply.
    I tired it, but I don't want to do an inner join as I need records.
    We have added an index too, and instead of left outer join, used Exception join.
    Which bring down the query run time from 13 to 6 sec, but still needs to be improved.

Share This Page