julian.fletcher (1/9/2013)
The client's database has a nightly maintenance job to reindex.Really, I was just wondering if there was a well known reason why
If A Or B{Something}
might be massively slow while
If A{Something}
Else If B
{Something}
is lightning fast. Does SQL try to do something 'clever' with "If A Or B" that it can't do with "If A Else If B"? But if it's "just one of those (performance) things", so be it and thanks for your time!
I depends on how the optimizer decides to implement the OR predicate.
In fact it's not "If A or B" as you say here, but it's "If EXISTS(SomeQuery) OR EXISTS(SomeQuery)" and it makes a whole lot of difference.
As Kevin said, the optimizer has no concept of short-circuiting and has to build a plan that evaluates all the expressions, regardless of whether they might (marginally) benefit from short-circuiting.
Take this simple query as an example:
IF EXISTS (
SELECT 1
FROM sys.objects
WHERE name = 'spt_values'
)
OR EXISTS (
SELECT 1
FROM spt_monitor
WHERE connections > 0
)
BEGIN
PRINT 1
END
The execution plan it produces on my laptop (2008R2SP2) is the following:
As you can see, the "OR" is implemented using a concatenation operator. The COND WITH QUERY can exit (and somehow short-circuit) as soon as the first result comes in from the (concatenation + nested loop), but in your case the optimizer might have implemented the "OR" with a blocking operator.
It would be great if you could post the execution plan and confirm it.
-- Gianluca Sartori