Case Statements versus coded if statements

Question

What is more efficient - handling with case statements in sql or handling the same data using if statements in code. I'm asking because my colleague has a huge query that has many case statements. I advised her to take stress off of the DB by coding the case statements. I've found that it is more efficient...but why?

Answer 1

There's a more fundamental question that's not being asked here: What are these CASE statements actually doing?

Forget performance for a minute. If CASE is only being used to transform the final output of a query, and it's actually possible to replace the same functionality with an if or select case in ASP, then it probably means that the database query/procedure is trying to do things that the UI should be responsible for, such as formatting. The separation-of-concerns issue is more serious than any possible performance issue.

If you have a query like this:

SELECT InvoiceID, InvoiceDate,
    CASE WHEN PaidStatus = 0 THEN 'Unpaid' ELSE 'Paid' END
FROM ...

This is just silly, because the UI, or whatever layer does the data-to-domain mapping, should know how to convert a status in the database to its corresponding description. It makes no sense to be including this logic in the query itself.

On the other hand, if the CASE construct is an essential part of the query, like:

SELECT
    SUM(CASE WHEN PaidStatus = 0 THEN Amount ELSE 0 END) AS TotalUnpaid,
    SUM(CASE WHEN PaidStatus = 1 THEN Amount ELSE 0 END) AS TotalPaid
FROM ...

Don't even try to move this kind logic to the UI, because the database is much better at it. And the CASE is semantically a part of the query ("compute total paid and unpaid amounts for x"), it's not taking over any UI function.

Worry first about where the logic actually belongs based on what it's intending to accomplish. Performance concerns should only enter into the discussion if you are actually noticing significant performance problems.