Or clause in an OUTER join killing performance

• Studdy05

  Ten Centuries

  Points: 1375

  More actions

  August 1, 2012 at 2:40 pm

  #265065

  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')

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 1, 2012 at 3:20 pm

  #1520553

  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')

• Studdy05

  Ten Centuries

  Points: 1375

  More actions

  August 1, 2012 at 3:28 pm

  #1520561

  Much much better. Many thanks