在文献中，我已经读过在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)
为什么“OR操作”或OR条件不可思议？(Why is “OR operation” or OR condition non-sargable?)在文献中，我已经读过在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)
发布评论