主页 / dba / 问题 / 241038
Accepted
Kirk Saunders
Asked: 2019-06-21 06:54:26 +0800 CST

重用嵌套决策逻辑 - CTE 与复制代码

  • 772

问题

我有一个查询，它输出一个column，通过一系列CASE语句创建。这同样column被用作同一语句中CASE第二个逻辑的一部分。columnSELECT

如果我要构建一个CTE应用了内部逻辑的 a，那么当我以后必须将其用作决策时，我可以参考内部逻辑。总体额外开销是多少？

据我了解，并没有增加任何真正的开销。（下面是我使用的一些研究和一个简单的测试用例）。是否有文章谈论这一点或情况并非如此？

研究和简单的测试用例

我发现有几篇文章没有指出这个特定问题，而是将我发送到没有增加运营开销的方向。

我针对我们现有的一个数据库写了一个小查询来测试这个理论。结果和执行时间相同，以及 Thestatistics和Query Execution Plan.

Code，Execution Plan对于Statistics非CTE版本：

SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
CASE
    WHEN PFH.FulFilHdrCreateDateTime IS NULL
        THEN PC.CaseCreateDateTime
    ELSE
        PFH.FulFilHdrCreateDateTime
    END AS [ImportantDate],
DATEDIFF(Day, CASE WHEN PFH.FulFilHdrCreateDateTime IS NULL THEN PC.CaseCreateDateTime ELSE PFH.FulFilHdrCreateDateTime END, GETDATE())
FROM PATIENTCASES PC
    LEFT OUTER JOIN PATFULFILLMENTHEADER PFH
        ON PFH.CompanyID = PC.CompanyID
        AND PFH.ClientID = PC.ClientID
        AND PFH.ProgramID = PC.ProgramID
        AND PFH.PatientID = PC.PatientID
        AND PFH.CaseID = PC.CaseID
        AND PFH.FulFilHdrID = (SELECT TOP(1) temp.FulFilHdrID
                                FROM PATFULFILLMENTHEADER temp
                                WHERE temp.CompanyID = PC.CompanyID
                                AND temp.ClientID = PC.ClientID
                                AND temp.ProgramID = PC.ProgramID
                                AND temp.PatientID = PC.PatientID
                                AND temp.CaseID = PC.CaseID
                                ORDER BY temp.FulFilHdrID
                            )
WHERE PC.CompanyID = 'RxCRoads'

非 CTE 执行计划

+---------------------------------------------------------+----------+
| Query Profile Statistics                                |          |
|   Number of INSERT, DELETE and UPDATE statements        | 0        |
|   Rows affected by INSERT, DELETE, or UPDATE statements | 0        |
|   Number of SELECT statements                           | 2        |
|   Rows returned by SELECT statements                    | 2880384  |
|   Number of transactions                                | 0        |
| Network Statistics                                      |          |
|   Number of server roundtrips                           | 3        |
|   TDS packets sent from client                          | 3        |
|   TDS packets received from server                      | 38523    |
|   Bytes sent from client                                | 2128     |
|   Bytes received from server                            | 157781300|
| Time Statistics                                         |          |
|   Client processing time                                | 10158    |
|   Total execution time                                  | 10158    |
|   Wait time on server replies                           | 0        |
+---------------------------------------------------------+----------+

Code，对于Execution PlanCTEStatistics版本：

WITH CTE (CompanyID, ClientID, ProgramID, PatientID, CaseID, ImportantDate)
AS
(
    SELECT PC.CompanyID,
    PC.ClientID,
    PC.ProgramID,
    PC.PatientID,
    PC.CaseID,
    CASE
        WHEN PFH.FulFilHdrCreateDateTime IS NULL
            THEN PC.CaseCreateDateTime
        ELSE
            PFH.FulFilHdrCreateDateTime
        END AS [ImportantDate]
    FROM PATIENTCASES PC
        LEFT OUTER JOIN PATFULFILLMENTHEADER PFH
            ON PFH.CompanyID = PC.CompanyID
            AND PFH.ClientID = PC.ClientID
            AND PFH.ProgramID = PC.ProgramID
            AND PFH.PatientID = PC.PatientID
            AND PFH.CaseID = PC.CaseID
            AND PFH.FulFilHdrID = (SELECT TOP(1) temp.FulFilHdrID
                                    FROM PATFULFILLMENTHEADER temp
                                    WHERE temp.CompanyID = PC.CompanyID
                                    AND temp.ClientID = PC.ClientID
                                    AND temp.ProgramID = PC.ProgramID
                                    AND temp.PatientID = PC.PatientID
                                    AND temp.CaseID = PC.CaseID
                                    ORDER BY temp.FulFilHdrID
                                )
)
SELECT CompanyID, 
ClientID, 
ProgramID, 
PatientID, 
CaseID, 
ImportantDate,
DATEDIFF(Day, [ImportantDate], GETDATE())
FROM CTE
WHERE CompanyID = 'RxCRoads'

CTE执行计划

+---------------------------------------------------------+-----------+
| Query Profile Statistics                                |           |
|   Number of INSERT, DELETE and UPDATE statements        |         0 |
|   Rows affected by INSERT, DELETE, or UPDATE statements |         0 |
|   Number of SELECT statements                           |         2 |
|   Rows returned by SELECT statements                    |   2880383 |
|   Number of transactions                                |         0 |
| Network Statistics                                      |           |
|   Number of server roundtrips                           |         3 |
|   TDS packets sent from client                          |         3 |
|   TDS packets received from server                      |     38523 |
|   Bytes sent from client                                |      2348 |
|   Bytes received from server                            | 157781800 |
| Time Statistics                                         |           |
|   Client processing time                                |      9985 |
|   Total execution time                                  |      9985 |
|   Wait time on server replies                           |         0 |
+---------------------------------------------------------+-----------+
sql-server performance

1 个回答

  1. Best Answer

    看来你在这里做了一些扎实的分析，所以我可能无法补充太多。

    当人们开始讨论 CTE 时，我经常想到的一件事是 Erik Darling 的这篇文章：

    CTE、内联视图以及它们的作用

    总而言之，CTE 是一个很好的基础，您可以从中引用和过滤选择列表中的项目，否则您将无法（想想窗口函数），但每次引用 CTE 时，它们都会被执行。你必须打一个更大的基础集的次数越少，你做的读取越少越好。如果您发现自己引用 CTE 不止一次或两次，则应考虑使用临时表或持久表，并使用适当的索引。

    因此，在您的具体情况下，由于您没有加入CTE，或以其他方式将其与其他数据集相关联，因此 CTE 不太可能会给您带来任何问题。

    请注意，如果有人出现并开始处理查询，将 CTE 加入其他表，或返回到自身，或添加另一个级别的 CTE“嵌套”（为最终选择处理更多数字） - 那么您将开始进入“运营开销”领域。

    • 5

相关问题