問題描述

想象一下下面的簡單查詢，我得到了過去 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())

這篇關于如何使 SQL 查詢中的重復自定義表達式可維護的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！