Home Forums SQL Server 2014 Development - SQL Server 2014 More problems with SQL 2014's new Cardinality Estimator RE: More problems with SQL 2014's new Cardinality Estimator

  • June 29, 2017 at 8:43 am

    #1948756

    Both of those execution plans indicate that SQL Server baulked at the task of generating a decent plan from such a complex query (Reason For Early Termination Of Statement Optimization = Time Out) so I'd vote for a reconfiguration of the query, something more likely to result in a stable plan - which very often equates to a more efficient plan. Something like this, perhaps:

    SELECT
     d.*,
     [AgeBandPricingScheme] = old10.[AgeBandPricingScheme],
     [CostCentre] = Old30.[CostCentre],
     [VATRate] = Old90.[VATRate],
     [FinancialReturnSubcategory] = Old110.[FinancialReturnSubcategory],
     [Frequency] = Old120.[Frequency],
     [FundingType] = Old130.[FundingType],
     [Organisation] = Old150.[Organisation],
     [ServiceGroup] = Old220.[ServiceGroup],
     [Subjective] = Old250.[Subjective],
     [Unit] = Old260.[Unit]
    INTO #AugmentedDeleted
    FROM #Deleted d
    Left Join dbo.TRef_AgeBandPricingScheme Old10 With (NoLock) On (Old10.[AgeBandPricingSchemeID] = D.[AgeBandPricingSchemeID])
    Left Join dbo.TRef_CostCentre Old30 With (NoLock) On (Old30.[CostCentreID] = D.[CostCentreID])
    Left Join dbo.TRefSys_VATRate Old90 With (NoLock) On (Old90.[VATRateID] = D.[DefaultVATRateID])
    Left Join dbo.TRefSys_FinancialReturnSubcategory Old110 With (NoLock) On (Old110.[FinancialReturnSubcategoryID] = D.[FinancialReturnSubcategoryID])
    Left Join dbo.TRefSys_Frequency Old120 With (NoLock) On (Old120.[FrequencyID] = D.[FrequencyID])
    Left Join dbo.TRef_FundingType Old130 With (NoLock) On (Old130.[FundingTypeID] = D.[FundingTypeID])
    Left Join dbo.T_Organisation Old150 With (NoLock) On (Old150.[OrganisationID] = D.[OrganisationID])
    Left Join dbo.TRef_ServiceGroup Old220 With (NoLock) On (Old220.[ServiceGroupID] = D.[ServiceGroupID])
    Left Join dbo.TRef_Subjective Old250 With (NoLock) On (Old250.[SubjectiveIDForIncomeValidation] = D.[SubjectiveID])
    Left Join dbo.TRef_Unit Old260 With (NoLock) On (Old260.[UnitID] = D.[UnitID])

     

    SELECT
     i.*,
     [AgeBandPricingScheme] = New10.[AgeBandPricingScheme],
     [CostCentre] = New30.[CostCentre],
     [VATRate] = New90.[VATRate],
     [FinancialReturnSubcategory] = New110.[FinancialReturnSubcategory],
     [Frequency] = New120.[Frequency],
     [FundingType] = New130.[FundingType],
     [Organisation] = New150.[Organisation],
     [ServiceGroup] = New220.[ServiceGroup],
     [Subjective] = New250.[Subjective],
     [Unit] = New260.[Unit]
    INTO #AugmentedInserted
    FROM #Inserted i
    Left Join dbo.TRef_AgeBandPricingScheme New10 With (NoLock) On (New10.[AgeBandPricingSchemeID] = i.[AgeBandPricingSchemeID])
    Left Join dbo.TRef_CostCentre New30 With (NoLock) On (New30.[CostCentreID] = i.[CostCentreID])
    Left Join dbo.TRefSys_VATRate New90 With (NoLock) On (New90.[VATRateID] = i.[DefaultVATRateID])
    Left Join dbo.TRefSys_FinancialReturnSubcategory New110 With (NoLock) On (New110.[FinancialReturnSubcategoryID] = i.[FinancialReturnSubcategoryID])
    Left Join dbo.TRefSys_Frequency New120 With (NoLock) On (New120.[FrequencyID] = i.[FrequencyID])
    Left Join dbo.TRef_FundingType New130 With (NoLock) On (New130.[FundingTypeID] = i.[FundingTypeID])
    Left Join dbo.T_Organisation New150 With (NoLock) On (New150.[OrganisationID] = i.[OrganisationID])
    Left Join dbo.TRef_ServiceGroup New220 With (NoLock) On (New220.[ServiceGroupID] = i.[ServiceGroupID])
    Left Join dbo.TRef_Subjective New250 With (NoLock) On (New250.[SubjectiveIDForIncomeValidation] = i.[SubjectiveID])
    Left Join dbo.TRef_Unit New260 With (NoLock) On (New260.[UnitID] = i.[UnitID])

    SELECT
    ...
    FROM #AugmentedInserted i
    FULL OUTER JOIN #AugmentedDeleted i On (i.ServiceLevelID = i.ServiceLevelID)
    JOIN sys.columns SC On (SC.Object_ID = @ObjectID)
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden