In the following post, J.D. brings up that I have a poorly performing query.
I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition)
Let's take a look at it here:
INSERT INTO [dbo].[tbl_Planning_Operational_Data_Exploded] ( [ScenarioID] ,[CompanyID] ,[OperationalAccountID] ,[CurrencyID] ,[CustomerID] ,[ItemID] ,[CalendarDate] ,[Amt] ,[PlanningOperationalDataActualTransactionAttributeValueExplodedID] ) SELECT ats.[ScenarioID] ,pode.[CompanyID] ,pode.[OperationalAccountID] ,pode.[CurrencyID] ,pode.[CustomerID] ,pode.[ItemID] ,pode.[CalendarDate] ,SUM(pode.[Amt]) AS Amt ,'00000000-0000-0000-0000-000000000000' AS [PlanningOperationalDataActualTransactionAttributeValueExplodedID] FROM #ActualThroughScenarios ats WITH (NOLOCK) --Mini 100 records INNER JOIN [dbo].[tbl_Core_Scenarios] cs WITH (NOLOCK) ON cs.ScenarioID = ats.ScenarioID --Mini 100 records AND cs.ScenarioTypeID IN ( 2 ,3 ) INNER JOIN [dbo].[tbl_Core_Scenarios] csActuals WITH (NOLOCK) ON csActuals.FiscalYear = cs.FiscalYear --Mini 100 records AND csActuals.ScenarioTypeID = 1 INNER JOIN [dbo].[tbl_Planning_Operational_Data_Exploded] pode ON pode.ScenarioID = csActuals.ScenarioID -- Huge up to 300 million records INNER JOIN [dbo].[tbl_Core_Fiscal_Date] cfd WITH (NOLOCK) ON pode.CalendarDate = cfd.CalendarDate --Mini 1000 records WHERE cfd.FiscalPeriod <= cs.ActualsThrough AND cs.ActualsThrough > 0 GROUP BY ats.[ScenarioID] ,pode.[CompanyID] ,pode.[OperationalAccountID] ,pode.[CurrencyID] ,pode.[CustomerID] ,pode.[ItemID] ,pode.[CalendarDate]
Query Plan Generated: https://www.brentozar.com/pastetheplan/?id=Sk69AQ-As
Basically this query is quite simple, I have a very large table "Exploded", I need to take sections of it, group them and modify the "ScenarioID", then re-insert them into the same table.
I can optimize or shift indexing strategy for all of the small tables, but adding indexes to the "Exploded" table is very costly in other parts (not shown there are many) of the database, I would prefer not to add any additional indexes to that table.
As mentioned in the linked post above this query can run can run quite slowly as it generates a very large Hash Match, I think this is the group by doing that, but I need the group by, that and the summation are keys parts to what I'm doing here:
Which spills to TempDB and is heavily affected by the speed of TempDB. Is there any way I could improve the query above given the constraints I have on the related tables?