Why joining two table variables significantly increases execution time in SQL Server 2012

Question

I am extracting data into two table variables @PremiumData and @LossData. Joining column is ControlNo.

If I run them separately, execution time to populate @PremiumData is 3 seconds, and execution time to populate @LossData is 0 seconds.

But when I join them together via a left join, the execution time increases to 01:07 ! minutes.

Query plan can be found here: https://www.brentozar.com/pastetheplan/?id=Syd564Tnf

DECLARE
    @EffectiveDateFrom DATETIME = '2012-04-01',
    @EffectiveDateTo DATETIME = '2018-04-30'

DECLARE @PremiumData TABLE (
                            ControlNo int,
                            PolicyNumber varchar(50),
                            EffectiveDate datetime,
                            ExpirationDate datetime,
                            ProducerName varchar(300), 
                            Premium money
                           )
DECLARE @LossData TABLE (
                        ControlNo int,
                        Losses money
                        )

INSERT INTO @PremiumData
    SELECT 
        INV.QuoteControlNum,  
        tblQuotes.PolicyNumber,
        tblQuotes.EffectiveDate,
        (SELECT TOP 1 Q.ExpirationDate
         FROM tblQuotes Q
         WHERE Q.ControlNo = tblQuotes.ControlNo
         ORDER BY Q.QuoteID DESC) as ExpirationDate,
        tblProducers.ProducerName,
        (SELECT ISNULL(SUM(tblFin_InvoiceDetails.AmtBilled), 0)
         FROM tblFin_InvoiceDetails
         WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
           AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum)) AS Premium
    FROM            
        tblFin_Invoices INV
    INNER JOIN      
        tblQuotes ON INV.QuoteID = tblQuotes.QuoteID
    INNER JOIN      
        tblProducerLocations ON INV.ProducerLocationGUID = tblProducerLocations.ProducerLocationGUID
    LEFT OUTER JOIN 
        tblProducers WITH (NOLOCK) ON tblProducerLocations.ProducerGUID = dbo.tblProducers.ProducerGUID 
    WHERE 
        (INV.Failed = 0)  
        AND (DATEDIFF(dd, tblQuotes.EffectiveDate, ISNULL(@EffectiveDateTo, tblQuotes.EffectiveDate)) >= 0)
        AND (DATEDIFF(dd, ISNULL(@EffectiveDateFrom, tblQuotes.EffectiveDate), tblQuotes.EffectiveDate) >= 0)
        AND dbo.tblQuotes.CompanyLocationGUID IN ('32828BB4-E1FA-489F-9764-75D8AF7A78F1',--"Plaza Insurance Company"    
                                                  '54A8FCCD-C7FE-4642-9C22-3A25207CDAEE' -- Watford         
                                                 )   

INSERT INTO @LossData
    SELECT 
        CPI.ControlNumber,
        SUM(CRP.ResPayAmount) as [Losses]
    FROM
        tblClaims_Claim C
    INNER JOIN 
        tblClaims_PolicyInformation CPI ON CPI.ClaimId = C.ClaimId
    INNER JOIN 
        tblClaims_ReservePayments CRP ON CRP.ClaimId = C.ClaimId
    GROUP BY
        CPI.ControlNumber--,

/* Joining @PremiumData and @LossData*/
SELECT  
    p.PolicyNumber,
    CONVERT(VARCHAR(11),p.EffectiveDate,101) as EffectiveDate,
    YEAR(EffectiveDate) as EffectiveYear,
    MONTH(EffectiveDate) as EffectiveMonth,
    SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (MONTH(EffectiveDate) * 4) - 3, 3) as MonthShort,
    'Q' + CONVERT(CHAR(1),DATEPART(QUARTER, p.EffectiveDate)) as Quarter,
    p.ProducerName,
    SUM(p.Premium) as Premium,
    ISNULL(ROUND(dbo.fn_EarnedPremiumCalcExtendedPolicies(SUM(Premium), EffectiveDate, ExpirationDate, 0, GETDATE()), 2), SUM(Premium)) AS EarnedPremium,   
    ISNULL(l.Losses,0) as Losses,
    ISNULL(SUM(l.Losses), 0) / NULLIF(ISNULL(ROUND(dbo.fn_EarnedPremiumCalcExtendedPolicies(SUM(Premium), EffectiveDate, ExpirationDate, 0, GETDATE()), 2), SUM(Premium)),0) as LossRatio
FROM 
    @PremiumData p
LEFT JOIN 
    @LossData l ON p.ControlNo = l.ControlNo 
GROUP BY  --p.ControlNo
    p.PolicyNumber,
    p.EffectiveDate,
    p.ExpirationDate,
    p.ProducerName,
    l.Losses

This is also the part of execution plan that concerns me:

Answer 1

Take a look at the estimated number of rows for your table variables in the execution plan you linked. The estimates are 1 row, but the actual rows are much higher.

The issue is the cardinality estimate is way off. I imagine the query is causing a spill into temp db because the initial memory grant is just too low. There are some helpful answers here

Try temp tables instead of table variables.

You could also try adding option(recompile) at the end of your insert statements (table variable or temp table) and this should improve your cardinality estimates.

Edit based on your edit: That sort is suffering from a bad cardinality estimate as well. The sort exists because you are grouping the query where you join the two table variables.