Or clause in an OUTER join killing performance

  • One of our developers sprung this on me and I have been trying to rethink it...because that OR clause brings things to its knees. I have the indexing and stats as tight as I know. That OR clause is the difference between 4 seconds and 4 minutes for output. Now I am trying to relogic it if possible to something less taxing. Have not seen a lot of OR clauses in JOINS before. Thoughts?

    left join dbo.A06_AccountSalutations a06

    on (((a01.FamilyId = 0 and a01.AccountNumber = a06.AccountNumber)

    or (a01.FamilyId > 0 and a01.FamilyId = a06.AccountNumber))

    and a06.SalutationType = 'FORMAL')

  • Have you considered something like this:

    ...

    left join dbo.A06_AccountSalutations a06

    on (a01.FamilyId = 0

    and a01.AccountNumber = a06.AccountNumber)

    and a06.SalutationType = 'FORMAL')

    union

    ...

    left join dbo.A06_AccountSalutations a06

    on (a01.FamilyId > 0

    and a01.FamilyId = a06.AccountNumber

    and a06.SalutationType = 'FORMAL')

  • Much much better. Many thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply