August 1, 2012 at 2:40 pm
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')
August 1, 2012 at 3:20 pm
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')
August 1, 2012 at 3:28 pm
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