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

In my experience our database servers are much MUCH larger than our application servers and are usually sitting below 30% idle. Have the database manage the data, then have the client iterate through the resultSet. It is better practice to have the database only return the data you need (if you can determine this up front).

Answer 2

CASE statements are preferred because:

• SQL: They are ANSI standard, making it portable to other databases without need for alteration
• they support "short circuiting"

Answer 3

You should query (filter and sort) the data in the database, and leave presentation to the presentation tier. This is for two key reasons:

• Databases are made to filter and sort data
• You want to pull the least amount of data over the wire from the DB as necessary

Answer 4

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.