使用表变量时查询需要 10 分钟，而使用临时表时查询需要 2 秒

问：

有人写了一个使用表变量的旧查询，它在 2-3 分钟内运行。从过去 2 周开始，即使数据集大小大致相同，它也会永久运行。因此，我正在尝试优化脚本。当我用临时表替换表变量时，脚本的运行速度提高了 99.99%。究竟是什么原因？我同意表变量不适合大型数据集。但是，在这种情况下，记录计数以数千为单位。表变量和临时表都存储在 tempDB 中，那么为什么会有这种差异呢？在后端会发生什么，而表变量在 2-3 分钟内运行，现在需要花费大量时间？

    DECLARE @test_table1 TABLE(/*some columns*/ );

    INSERT INTO @test_table1
    SELECT /*some columns*/ FROM SomeTable1
        LEFT JOIN SomeTable2
        LEFT JOIN SomeTable3
        LEFT JOIN SomeTable4
        LEFT JOIN SomeTable5
    WHERE --some conditions with string and date functions

    UPDATE @test_table1 SET col1 = 'xyz' WHERE SUBSTRING(col2,1,4) IN ('some values')

    DECLARE @test_table2 TABLE (/*some columns*/);

    WITH [cte1] AS (
        SELECT /*some columns*/ FROM SomeTable1
            INNER JOIN @test_table1 
            LEFT JOIN SomeTable2)
    
    ,[cte2] AS(
        SELECT /*some columns*/ FROM SomeTable1
            INNER JOIN @test_table1 
            LEFT JOIN SomeTable2    
            LEFT JOIN SomeTable3
        UNION
        SELECT /*some columns*/ FROM SomeTable1
            INNER JOIN @test_table1 
            LEFT JOIN SomeTable2    
            LEFT JOIN SomeTable3)
    ,[cte3] AS(
        SELECT DISTINCT /*some columns*/ FROM [cte2]
        WHERE /*some simple conditions*/)

    INSERT INTO @test_table2 
    SELECT /*some columns with string concat using stuff and xml path*/ FROM [cte3]
    GROUP BY /*some columns*/