Link to home
Start Free TrialLog in
Avatar of davidcahan
davidcahanFlag for United States of America

asked on

Performance Difference Between OR Operator and Union Operator

I've always found in practice that using a union operator when trying to build a result set off one table but filtering over multiple different columns performs much better than using an OR operator.  I'm suddenly feeling the need to back up my theory

EX

Select ID
From Foo
Where Phone = 1
Union
Select ID
From Foo
Where Name = 1

VS

Select ID
From Foo
Where Name = 1 or Phone=1

The opposite is true if it's the same column

EX
Select ID
from Foo
where Phone = 1 or Phone = 2

Does my understanding match reality.  Like I said, I've dealt with tables over a million rows and always found a union operator to perform much better than an OR when filtering over multiple different columns
ASKER CERTIFIED SOLUTION
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davidcahan

ASKER

I had a table that looked something like this

PhoneHome
PhoneWork
PhoneCell
PhoneAlt

Whenever I did an OR between them on SQL Server 2000 with over a million rows it might take 8 seconds to come back with an answer.  When I switched to union it took about 2 seconds.  Those numbers are a little anecdotal in the sense that I can't remember the exact different but the difference was definitely significant.  So then I just became a big fan of UNION.  For change management efficiency, I just decided that on any table that I knew would have millions of rows eventually I would just always use a union when I built SP's to access it.  Perhaps that was an incorrect plan or one that only applied to SQL 2000.

Does the addition of a like with wildcards change the union vs. OR conversation?
oh, the columns were varchar.  not sure if that would make a difference but thought I would mention it.