I got an email recently regarding one of my early blog posts from the olden days:
Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed".
I am not certain that optimization changes the priority of the expressions, but I do not think so. We can force evaluation so that it is done in a certain order by enclosing the first expression to evaluate in parentesis, since enclosed in parentesis expressions are evaluated first, like this:
((<filter 1>) OR <filter 2>) AND
((<filter 3>) OR <filter 4>)
In this case filter 1 is evaluated before than filter 2 and filter 3 is evaluated before than filter 4.
Thanks in advance
In that original post, I claimed that using efficient OR logic will not only make your code simpler and cleaner, but also more efficient because in theory SQL Server can "short-circuit
" on the first part of an OR if it is true, without the need to evaluate the second part.
However, in one of the comments, it was correctly pointed out that this is not true; you really have no control over how SQL Server will evaluate something -- it will generally do what it thinks is best regardless of the order in which you specify your conditions.
After getting that email I did some research to determine the truth behind this, and came up with a couple of good resources:
- This SQLTeam forum thread from last year explores the topic and is a good read.
- This blog post from Mark Cohen discusses the topic as well, but the best thing to read here is the very last comment from "sc0rp10n" which explains things pretty well. (Plus, with a "l33t" hacker name like that, you know he must know what he is talking about! These are assigned by some committee somewhere after a vigorous review process, right?)
- In this TechNet chat, Nigel Ellis, the development manager for the SQL Server Query Processor team, says about halfway into it that SQL Server does indeed short-circuit, but it is not specified if this is something users can control based on how they write their boolean expressions.
The final verdict? Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression
. With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.
Anyone have any thoughts, feedback, further info or links regarding this topic?