重用嵌套決策邏輯 - CTE 與複製程式碼

問題

我有一個查詢，它輸出一個column，通過一系列CASE語句創建。這同樣column被用作同一語句中CASE第二個邏輯的一部分。column``SELECT

如果我要建構一個CTE應用了內部邏輯的 a，那麼當我以後必須將其用作決策時，我可以參考內部邏輯。總體額外成本是多少？

**據我了解，**並沒有增加任何真正的成本。（下面是我使用的一些研究和一個簡單的測試案例）。是否有文章談論這一點或情況並非如此？

---

研究和簡單的測試案例

我發現有幾篇文章沒有指出這個特定問題，而是向我發送了沒有增加運營成本的方向。

• https://www.scarydba.com/2016/07/18/common-table-expression-just-a-name/
• https://www.sqlshack.com/why-is-my-cte-so-slow/

我針對我們現有的一個數據庫寫了一個小查詢來測試這個理論。結果和執行時間相同，以及 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'

+---------------------------------------------------------+----------+
| 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'

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

看來你在這裡做了一些紮實的分析，所以我可能無法補充太多。

當人們開始討論 CTE 時，我經常想到的一件事是 Erik Darling 的這篇文章：

CTE、內聯視圖以及它們的作用

總而言之，CTE 是一個很好的基礎，您可以從中引用和過濾選擇列表中的項目，否則您將無法（想想視窗函式），但每次引用 CTE 時，它們都會被執行。你必須打一個更大的基礎集的次數越少，你做的讀取越少越好。如果您發現自己不止一次或兩次引用 CTE，則應考慮使用臨時表或持久表，並使用適當的索引。

因此，在您的具體情況下，由於您沒有加入CTE，或以其他方式將其與其他數據集相關聯，因此 CTE 不太可能會給您帶來任何問題。

請注意，如果有人出現並開始處理查詢，將 CTE 加入其他表，或返回自身，或添加另一個級別的 CTE“嵌套”（為最終選擇處理更多數字） - 那麼您將開始進入“運營成本”領域。

引用自：https://dba.stackexchange.com/questions/241038