[SOLVED] Performace of CASE statement in WHERE condition in SQL query

Performace of CASE statement in WHERE condition in SQL query

I have a query like this:

SELECT Column1, Column2, ...
FROM Table
WHERE (
    CASE
        WHEN Column1 = 'Value1'
        AND Column2 = 'Value2'
            THEN 'ok'
        ELSE 'nok'
    END = 'ok'
)

I know I can rewrite the condition like this:

SELECT Column1, Column2, ...
FROM Table
WHERE Column1 = 'Value1'
AND Column2 = 'Value2'

My question is, if there is any performance impact writing the query with CASE statement in WHERE condition (example #1) compared to query with simple conditions (example #2). Sometimes more complex conditions are more readable using the CASE statement (nested statements) than building the same conditions using AND+OR. I use Oracle and Microsoft SQL Server.

Solution

Yes. There is a major, major difference between using a CASE expression and boolean expressions in the WHERE clause.

In almost all databases, it comes down to "the optimizer understands boolean expressions". CASE expressions require that they be evaluated in the order that they are defined. Because of this, the optimizer will just use a table scan and run the expressions.

This can affect index usage and optimizer usage. It can have downstream effects in more complex queries on the choice of join or group by algorithm.

And another subtle point is that you could have expressions like this that are not indexed:

where substr(y, 1000, 1002) = 'xyz' and x = 1

One of these is obviously more expensive than the other. Many optimizers would know to filter on x = 1 before running a more expensive function.

The advice is to avoid CASE expressions in WHERE (and ON clauses). There are a handful of cases where they are needed, but they should be used very sparingly.