时间戳

首页 > 解决方案 > SQL replace cte by actual table to improve performance?

sql - SQL replace cte by actual table to improve performance?

问题描述

Table #temp has an index on (NrBL, LgnBL). But in SQL Server 2016, common table expressions cannot be indexed. Could it be faster to use another temporary table instead of the CTE, and index it on (NrBL, LgnBL) ?

WITH cte (NrBL, LgnBL, clientNr) AS 
(
    SELECT NrBL, LgnBL, clientNr
    FROM #temp
    WHERE Niveau=0
)
UPDATE a
SET a.ClientNr = cte.ClientNr
FROM #temp AS a
JOIN cte ON a.NrBL=cte.NrBL AND a.LgnBL = cte.LgnBL;

标签： sqlsql-server

解决方案

我认为带有窗口函数的可更新 CTE 是最好的方法：

WITH cte (NrBL, LgnBL, clientNr) AS (
      SELECT t.*,
             MAX(CASE WHEN Niveau = 0 THEN clientNr END) OVER (PARTITION BY NrBL, LgnBL) as new_clientNr
      FROM #temp t
     )
UPDATE cte
    SET ClientNr = new_ClientNr
    WHERE new_clientNR IS NOT NULL;

您可以在(NrBL, LgnBL, Niveau, clientNr).

推荐阅读