如何使 SQL 查询中的重复自定义表达式可维护

2021-09-10 00:00:00 sql tsql sql-server

想象一下下面的简单查询，我得到了过去 3 天内创建的所有用户的列表，逻辑或示例并不重要

Imagine the following simple query, where I get a list of all users that were created within the last 3 days, the logic or example is not important

SELECT
    ...
    , DATEDIFF(DAY, U.DateCreated, GETUTCDATE())
    ...
FROM
    dbo.AspNetUsers U
WHERE
    DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) < 3

我重复了一些代码 DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) <3，当我有更复杂的上述示例时，我不想维护两次或多次需要该逻辑.

I've repeated some code DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) < 3 which, when I have much more complicated examples of the above I don't want to maintain twice or however many times I need that logic.

考虑到性能，应该如何处理这个问题?

How should one go about dealing with this with performance in mind?

谢谢

推荐答案

如果您考虑到性能，那么您最好在需要时重复表达式.具体来说，不要试图将它们放在用户定义的函数中.众所周知，它们会使 SQL Server 中的查询变慢.

If you have performance in mind, then you'd better repeat expressions when needed. Specifically, don't try to put them in a user-defined functions. They are known to make queries slow in SQL Server.

话虽如此，SQL Server 中至少有两种方法可以在不影响性能的情况下使查询更具可读性:

Having said that, there are at least two methods in SQL Server to make queries more readable without affecting the performance:

  1. CTE
  2. 交叉申请

CTE 使用示例:

WITH
CTE
AS
(

    SELECT
        ...
        , DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) AS CalculatedColumn
        ...
    FROM
        dbo.AspNetUsers U

)
SELECT
    ...
    CalculatedColumn
    ...
FROM CTE
WHERE
    CalculatedColumn < 3
;

使用CROSS APPLY的例子:

而不是在以下查询中重复部分公式:

Instead of repeating parts of the formula in the following query:

SELECT
    ColA + ColB AS ColSum
    ,ColA - ColB AS ColDiff
    ,(ColA + ColB) * (ColA - ColB) AS Result
    ,(ColA + ColB) * (ColA - ColB) * 100.0 AS Percentage
FROM Table

你可以用CROSS APPLY这样写:

SELECT
    ColSum
    ,ColDiff
    ,Result
    ,Result * 100.0 AS Percentage
FROM
    Table
    CROSS APPLY
    (
        SELECT
            ColA + ColB AS ColSum
            ,ColA - ColB AS ColDiff
    ) AS A1
    CROSS APPLY
    (
        SELECT ColSum * ColDiff AS Result
    ) AS A2

<小时>

顺便说一下性能，

By the way, speaking about performance, 

WHERE DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) < 3

很糟糕，因为它不能在 DateCreated 上使用索引(因为您将列包装在一个函数中).

is terrible, because it can't use index on DateCreated (because you wrapped the column in a function).

你最好把它改写成这样

WHERE U.DateCreated > DATEADD(DAY, -3, GETUTCDATE())

相关文章