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 have a reason why: in the case of the SEPARATE executions, you have a VERY EFFICIENT PLAN that makes a hit (at least one row found) and it executes the "do something" code and then does the GOTO - thus COMPLETELY SKIPPING THE SECOND IF EXISTS CHECK. That second IF EXISTS has a HORRIBLY SLOW query plan that is being executed when you do the combined-check OR attempt in your first sample code. That query is NOT being "SHORT CIRCUITED" to not run in the EXISTS OR EXISTS scenario because the optimizer doesn't work that way. To my knowledge there are very few things that short-circuit in the optimizer/engine (CASE being one of them).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service