There is an old query written by someone which uses table variables and it was running in 2-3 minutes. From last 2 weeks, it runs eternally even though the dataset size is approximately same. Hence, I am trying to optimize the script. The script runs 99.99% faster when I replace table variable with a temporary table. What is the exact reason for this? I agree that the table variable does not suit for large datasets. However in this case, the record count is in thousands. Both table variable and temporary table stores the data in tempDB, then why this difference? What could have happened in the backend where the table variable which was running in 2-3 minutes before, takes an insane amount of time now?

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*/