在文献中,我已经读过在WHERE子句中使用OR条件或运算符使得语句non-sargable 。 我不确定为什么或如何这是真的。 任何帮助,将不胜感激。
In literature, I have read that using OR condition or operator in a WHERE clause makes a statement non-sargable. I am not sure why or how this could be true. Any help would be appreciated.
最满意答案
答案由权威优化书籍SQL Server查询性能调优作者 Grant Fritchey提供。 所以这里:
“OR语句现在比以前更加优化。但是如果你考虑一下,如果我有一个索引并且我想匹配等于A或Z的值,引擎必须进行多次比较,而不是简单的一个。可搜索的条件都导致直接点查找或范围查找.So = A将遍历一棵树并从该值的索引中检索一行或一组行。但如果它是A或R ,它不能检索范围,它必须做其他类型的工作。有时你会看到这些完成两个寻找与JOIN操作。这是伟大的。但有时你会看到额外的过滤器操作员或扫描。所以,这不是可以抨击的。“ (再次,归功于作者)
The answer was provided by the author of the authoritative optimization book SQL Server Query Performance Tuning, Grant Fritchey. So here it goes:
"OR statements are much more optimized now than they used to be. But if you think about it, if I have an index and I want to match values that are equal to A or Z, the engine has to do multiple comparisons, not simply one. The sargeable conditions all result in a straight forward point lookup, or range lookup. So = A will walk a tree and retrieve the one row, or the set of rows, from the index for that value. But if it’s A or R, it can’t retrieve a range, it has to do other types of work. Sometimes you’ll see these done as two seeks with a JOIN operation. And that’s great. But other times you’ll see additional filter operators or scans. So, it’s not sargeable." (again, credit goes to the author)
更多推荐
发布评论