CODEWITHSUNDEEP
sqlsql-serverperformance
Zuzu JH
Zuzu JH

Reputation: 627

SQL 'case when' vs 'where' efficiency

Which is more efficient:

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

OR

Select SUM(Col1) From myTable where col2=2

Or are they the same speed?

Upvotes: 4

Views: 11211

Answers (2)

The Impaler
The Impaler

Reputation: 48780

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);

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

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The second is more efficient:

  • It would generally process fewer rows (assuming there are non-"2" values), because rows would be ignored before the aggregation function is called.
  • It allows the optimizer to take advantage of indexes.
  • It allows the optimizer to take advantage of table partitions.

Under some circumstances, they might appear to take the same amount of time, particularly on small tables.

Upvotes: 5

Related Questions