Query Optimization

[baltman]

Why does the first WHERE clause take so much longer than the sum of the parts? Anything I can do within the WHERE clause to improve the processing time?

I can't change the DB. There are also 5 inner joins and 3 outer joins with a few addition filters embedded... e.g. LEFT OUTER JOIN B ON (A.ID = B.LoanID AND B.Flag = 1)

Thank you,
Brian

~20 - 25 seconds
WHERE (((A.Status IN ('NEW', 'PRESETTLED', 'SETTLED') AND ISNULL(B.Status,'') NOT in ('SOURCED')) OR A.Status IN ('SOURCED') and ISNULL(B.Status,'') not in ('CLOSED'))  AND (C.ID in ('2456'))) OR D.PoolID in ('904')

Vs

~ 5 seconds
WHERE (((A.Status IN ('NEW', 'PRESETTLED', 'SETTLED') AND ISNULL(B.Status,'') NOT in ('SOURCED')) OR A.Status IN ('SOURCED') and ISNULL(B.Status,'') not in ('CLOSED'))  AND (C.ID in ('2456')))

Vs
~ 5 seconds
WHERE D.PoolID in ('904')
[code]

 

[rac2]

WHERE (
        (
          (
            A.Status IN ('NEW', 'PRESETTLED', 'SETTLED')
            AND
            ISNULL(B.Status,'') NOT in ('SOURCED')
          )
          OR 
          A.Status IN ('SOURCED')
          and
          ISNULL(B.Status,'') not in ('CLOSED')
        )  
        AND 
        (
            C.ID in ('2456')
        )
      ) 

  OR D.PoolID in ('904')

It looks like this will get everything in PoolID '904' plus the C.ID '2456' which have various combinations of A.Status and B.Status .


It looks like the conditions on A.Status = 'Sourced' and B.Status of Not Closed might benefit from some parentheses. This to make the structure parallel to the first set of conditions.

WHERE (
        (
          (
            A.Status IN ('NEW', 'PRESETTLED', 'SETTLED')
            AND
            ISNULL(B.Status,'') NOT in ('SOURCED')
          )
          OR 
[COLOR=green]          ([/color]
            A.Status IN ('SOURCED')
            and
            ISNULL(B.Status,'') not in ('CLOSED')
[COLOR=green]          )[/color]
        )
        AND 
        (
             C.ID in ('2456')
        )
      ) 

  OR D.PoolID in ('904')

Whether that makes a difference or not I couldnt say, but it clarifies the condition for me.

Since the two sets of conditions seem to be disconnected, possibly a UNION query instead of an OR condtion would give you 5+5=10 seconds performance.