案例语句与编码 if 语句 [英] Case Statements versus coded if statements

问题描述

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

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 仅用于转换查询的最终输出，并且实际上可以将相同的功能替换为 if 或 select case 在 ASP 中，这可能意味着数据库查询/过程正在尝试做 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屋！