SQL“case when"与“where"效率 [英] SQL 'case when' vs 'where' efficiency

问题描述

哪个更有效:

Select SUM(case when col2=2 then col1 Else 0 End) From myTable 

或

Select SUM(Col1) From myTable where col2=2

还是它们的速度相同?

推荐答案

绝对第二个应该更快.这是因为访问" 的概念.访问是指查询需要检索以产生结果的数据量.它对数据库引擎优化器决定包含在执行计划中的操作符"有很大影响.

Definitively the second one should be faster. This is because of the concept of "Access". Access refers to the amount of data that the query needs to retrieve in order to produce the result. It has a big impact on the "operator" the database engine optimizer decides to include in the execution plan.

安全的一些例外，第一个查询需要访问所有表行，然后计算结果，包括与案例无关的行.

Safe some exceptions, the first query needs to access all the table rows and then compute the result, including rows that don't have anything to do with the case.

第二个查询只引用计算结果所需的特定行.因此，它具有更快的潜力.为了实现它，索引的存在是至关重要的.例如:

The second query only refers to the specific rows needed to compute the result. Therefore, it has the potentiality of being faster. In order for it to be materialized, the presence of indexes is crucial. For example:

create index ix1 on myTable (col2);

在这种情况下，它只会访问匹配过滤谓词col2 = 2的行子集.

In this case it will only access the subset of rows that match the filtering predicate col2 = 2.

这篇关于SQL“case when"与“where"效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！