Problem with "If Exists (Select ...) Or Exists (Select ...)"
-
julian.fletcher (1/9/2013)
The client's database has a nightly maintenance job to reindex.May not be frequent enough. May not update the stats needed.
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!
No, other than the fact that B doesn't have to be run in the second case if A is true.
No this is not 'one of those performance things and so be it'. There will be a reason here, it's not obvious from just seeing an abstraction of the code. I asked for the plans and the wait types to try and ID why and tell you why. It's not a plain case of 'anyone who uses OR is an idiot'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks, that makes perfect sense, but I forgot to mention that neither condition A or B were met when the code was run, which means that both A and B would have been evaluated.
Hence my confusion.
-
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
-
julian.fletcher (1/9/2013)
Thanks, that makes perfect sense, but I forgot to mention that neither condition A or B were met when the code was run, which means that both A and B would have been evaluated.Hence my confusion.
Now we DEFINITELY need the query plans (and I would like statistics IO ON output too) from each scenario. No other way to know what is happening. But it surely seems logical that a different plan is being created between the two query sets.
Hmm, another possible explanation (although unlikely assuming this is a repeatable scenario) is that the first query was simply blocked for a extended period and when you crafted the second and ran it the blocking lock wasn't in play.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I think that's it! I should have realised that there would be one execution plan for the "If Exists () Or Exists ()" statement rather than two.
If I have the chance, I'll try to get execution plans but, given it's on a client's live database (and, so far, it's not been reproducible here, even with a restore of their database), this is unlikely.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply