案例陈述与编码的if陈述 [英] Case Statements versus coded if statements

问题描述

更有效的方法-在sql中使用case语句处理，或在代码中使用if语句处理相同的数据.我问是因为我的同事有一个很大的查询，其中包含许多案例陈述.我建议她通过编写case语句来减轻数据库的压力.我发现它更有效...但是为什么呢?

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?

推荐答案

这里还没有问到一个更基本的问题:这些CASE语句实际上在做什么?

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

忘记表演一分钟.如果CASE仅用于转换查询的最终输出，并且实际上可以用ASP中的if或select case替换相同的功能，则可能意味着数据库查询/过程正在尝试做UI应该负责的事情，例如格式化.关注点分离问题比任何可能的性能问题都更为严重.

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.

如果您有这样的查询:

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

这很愚蠢，因为UI或进行数据到域映射的任何层都应该知道如何将数据库中的状态转换为其相应的描述.在查询本身中包含此逻辑没有任何意义.

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.

另一方面，如果CASE构造是查询的必要部分，例如:

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 ...

甚至不要尝试将这种逻辑转移到UI，因为数据库在很多上表现更好.并且CASE从语义上讲是查询的一部分(计算 x 的已付和未付总额")，它不接管任何UI函数.

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.

这篇关于案例陈述与编码的if陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！