IF is better than OR

Here's the accompanying video – same material, different format.

Here's a common mistake: using OR instead of IF. Let's say you want to get sales by a time interval, either current week or current month. This will not perform well:

IF @Interval = 'wk'

SELECT SUM(o.Sale) AS Sales

FROM Orders o

WHERE o.BizID = @BizID

AND (

— Interval: Week

( @Interval = 'wk'

AND DATEPART(wk, o.OrderDate) = @wk

AND DATEPART(yyyy, o.OrderDate) = @yyyy

)

— Interval: Month

OR ( @Interval = 'mm'

AND DATEPART(mm, o.OrderDate) = @mm

AND DATEPART(yyyy, o.OrderDate) = @yyyy

)

GROUP BY O.BizID

This will perform much better:

— Interval: Week

IF @Interval = 'wk'

SELECT SUM(o.Sale) AS Sales

FROM Orders o

WHERE o.BizID = @BizID

AND DATEPART(wk, o.OrderDate) = @wk

AND DATEPART(yyyy, o.OrderDate) = @yyyy

GROUP BY O.BizID

ELSE

— Interval: Month

IF @Interval = 'mm'

SELECT SUM(o.Sale) AS Sales

FROM Orders o

WHERE o.BizID = @BizID

AND DATEPART(mm, o.OrderDate) = @mm

AND DATEPART(yyyy, o.OrderDate) = @yyyy

GROUP BY O.BizID

OR always widens the scope of the query; the optimizer has to walk through each and every OR statement to evaluate if it's true. When you use IF, the optimizer immediately figures out what's true, and only has to deal with the one query, OR-free.

OR is not the same as IF. Avoid OR when you can, and your queries will thank you.

-Jen McCown, http://www.MidnightDBA.com