SQL子查询性能需要提高

Karan 发表于 Dev

卡兰

我在Update语句中有此子查询。它显示了执行计划内的热点。请提出建议，是否可以进行一些代码改进以提高性能。

--Schema of temp table.
CREATE TABLE #tmpInvestorJob (
    LogID INT,
    [Status] INT,
    JobType VARCHAR(20)
)

CREATE CLUSTERED INDEX IX_tmpInv_Status ON #tmpInvestorJob ([Status]);

----#TempTimeline has less than 10 records but has lot of columns.
----#tmpInvestorJob has lots of data inside and is main reason for the slowness.

UPDATE g
SET Completed = ISNULL(a.Completed, 0)
FROM #TempTimeline g
JOIN (
    SELECT LogID,
           COUNT(1) 'Completed'
    FROM #tmpInvestorJob
    WHERE [Status] = 3
    GROUP BY LogID
) a ON a.LogID = g.LogID

请提出建议，如果我们可以改进上面的Update语句。

克劳森

您可以在#tmpInvestorJob表中的LogID上添加索引。

这将提高性能，因为它仅计算表之间匹配的行。它应该快得多，因为#TempTimeline中只有几行。

UPDATE g 
SET 
  Completed =  COALESCE(a.Completed, 0)
FROM #TempTimeline g
-- using outer apply in case no matches are found to set completed to 0
OUTER APPLY 
(
  SELECT COUNT(*) Completed
  FROM #tmpInvestorJob
  WHERE
    [Status] = 3              
    and LogID = g.LogID
) a

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。