performance - 临时表变量的性能缓慢

Question

我一直假设对于中小型数据集，我会使用临时表变量，一旦数据集变大（相对 - 我知道这有点主观），我会转向 #temp 表。

我有一些代码可以非常快速地返回 159 行 - 0.00 毫秒。我看到 159 行并假设临时表变量会很好。但是，当我插入临时表变量时，代码会旋转 10 分钟。当我切换到#temp 表时，繁荣 - 回到 0.00 秒。

有人能告诉我为什么会发生这种情况，或者我可以做些什么来解决这个问题吗？

环境是SQL Server 2017 Enterprise，内存和磁盘空间充足。

DECLARE @Year INT = 2017
DECLARE @Month INT = NULL

DECLARE @TempTableVariable TABLE
(
    Test BIGINT

)

DROP TABLE IF EXISTS tempdb.dbo.#TempTableTest

CREATE TABLE #TempTableTest (Test BIGINT)


--INSERT INTO @TempTableVariable

INSERT INTO #TempTableTest

SELECT 
  idfHumanCase 
FROM dbo.tlbHumanCase
WHERE YEAR(COALESCE(datOnSetDate, datFinalDiagnosisDate, datTentativeDiagnosisDate, datEnteredDate)) = @Year
AND (MONTH(COALESCE(datOnSetDate, datFinalDiagnosisDate, datTentativeDiagnosisDate, datEnteredDate)) = @Month OR @Month IS NULL)
AND LegacyCaseID IN
(
SELECT LegacyCaseID
FROM dbo.tlbHumanCase 
WHERE YEAR(COALESCE(datOnSetDate, datFinalDiagnosisDate, datTentativeDiagnosisDate, datEnteredDate)) = @Year
AND (MONTH(COALESCE(datOnSetDate, datFinalDiagnosisDate, datTentativeDiagnosisDate, datEnteredDate)) = @Month OR @Month IS NULL)

GROUP BY LegacyCaseID HAVING COUNT(*) > 1
)

SELECT * FROM #TempTableTest

--SELECT * FROM @TempTableVariable

标签： performancetemp-tablessql-server-2017