SQL Server谓词懒惰? [英] Sql Server predicates lazy?

问题描述

我有一个查询:

SELECT 
    someFields 
FROM 
    someTable 
WHERE 
    cheapLookup=1 
    AND (CAST(someField as FLOAT)/otherField)<0.9

那么，在cheapLookup是0的情况下，将执行CAST和除法吗?如果没有，在这种情况下如何避免计算?

So, will the CAST and division be performed in the case that cheapLookup is 0? If not, how can I avoid the calculation in this case?

推荐答案

这取决于查询计划，该计划由每个可以产生正确结果的替代计划的估计成本决定.

It depends on the query plan, which is determined by the estimated cost of each considered alternative plan that would produce correct results.

如果谓词"cheapLookup = 1"可以使用索引，并且具有足够的选择性，则SQL Server可能会选择在该索引上进行搜索并将第二个谓词应用为残差(即，仅对具有以下含义的行求值)与搜索操作匹配).

If the predicate 'cheapLookup = 1' can use an index, and it is sufficiently selective, SQL Server would likely choose to seek on that index and apply the second predicate as a residual (that is, only evaluating it on rows that are matched by the seeking operation).

另一方面，如果cheapLookup不是索引中的前导键，或者它不是非常有选择性，则SQL Server可能会选择进行扫描，将这两个谓词应用于遇到的每一行.

On the other hand, if cheapLookup is not the leading key in an index, or if it is not very selective, SQL Server might choose to scan, applying both predicates to every row encountered.

除非在整个表达式上碰巧有索引的计算列，否则不会为搜索操作选择第二个谓词，并且使用该索引原来是执行整个查询的最便宜的方法.如果存在合适的索引，则SQL Server将寻求第二个谓词结果< 0.9"，然后应用"cheapLookup = 1"作为残差.索引的计算列也可能以cheapLookup作为其第二个键，这将导致纯查找而没有残差.

The second predicate will not be chosen for a seeking operation, unless there happens to be an indexed computed column on the whole expression, and using that index turns out to be the cheapest way to execute the whole query. If a suitable index exists, SQL Server would seek on 'second predicate result < 0.9', and apply 'cheapLookup=1' as a residual. There is also the possibility that the indexed computed column has cheapLookup as its second key, which would result in a pure seek, with no residual.

关于第二个谓词的另一件事是，如果没有计算列(无论是否为索引)，SQL Server将不得不猜测表达式的选择性.使用计算列，服务器可能能够在expression-result列上创建统计信息，这将对优化器有所帮助.请注意，'CAST(someField as FLOAT)/otherField'上的计算列必须先保留，然后才能对其建立索引或创建统计信息，因为该列包含不精确的数据类型.

The other thing about the second predicate is that without a computed column (whether or not indexed), SQL Server will have to guess at the selectivity of the expression. With the computed column, the server might be able to create statistics on the expression-result column, which will help the optimizer. Note that a computed column on 'CAST(someField as FLOAT)/otherField' would have to be persisted before it could be indexed or have statistics created on it, because it contains an imprecise data type.

总而言之，表达式的复杂性不如使用优化程序考虑的每种可用访问方法的整个计划的估计成本那样重要.

In summary, it's not the complexity of the expression that counts so much as the estimated cost of the whole plan that uses each of the available access methods considered by the optimizer.

这篇关于SQL Server谓词懒惰?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！