question

SET ansi_nulls ON go SET quoted_identifier ON go CREATE PROCEDURE [ETL].[Sp_create_master_prft_ctr_mgr] @batchid INT AS DECLARE @log_id INT, @error_num INT, @error_msg NVARCHAR(4000), @cmd NVARCHAR(4000), @invalid_count INT BEGIN SET nocount ON; -- Logging INSERT INTO [ETL].[migration_log] ([tablename], [status], [startdate], [timestamp], [batchid]) VALUES ('dbo.DTIRIS_Master_Prf_Cntr_Mngr', 'BEGIN', Getdate(), Getdate(), @batchid) SELECT @log_id = Scope_identity() BEGIN try TRUNCATE TABLE byd_reporting.dbo.dtiris_master_prf_cntr_mngr INSERT INTO dbo.dtiris_master_prf_cntr_mngr SELECT DISTINCT mngrlevel AS Report_To_Mngr_OrdLevel, mngrorgid AS Report_To_Mngr_OrgID, mngrorg AS Report_To_Mngr_OrgName, --(CASE WHEN o.[OrgUnitManager] IS NULL THEN b.[OrgUnitManager] ELSE o.[OrgUnitManager] END) AS [MNGRNAME], ( CASE WHEN o.[orgunitid] IS NULL THEN b.orgid ELSE o.[orgunitid] END ) AS OrgUnitID, ( CASE WHEN o.[orgunitname] IS NULL THEN b.orgunitname ELSE o.[orgunitname] END ) AS OrgUnitName, b.[orgunitmanager] AS Report_To_MngrName, c.email_addr FROM (SELECT DISTINCT orgid, [orgunitmanager], orgunitname, mngrorgid, mngrorg, mngrlevel FROM (SELECT DISTINCT [orgunitid] AS OrgID, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '1' AS mngrlevel, [orgunitmanager], orgunitname FROM [ByD_Reporting].[dbo].[vw_dtiris_branck_mngr_hierarchy] --WHERE [OrgUnitManager] = @Mngr_Name --AND OrgUnitID = @Mngr_OrgUnitID UNION ALL SELECT DISTINCT [child1_orgunitid] AS OrgID, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '2' AS mngrlevel, [orgunitmanager], child1_orgunitname FROM [ByD_Reporting].[dbo].[vw_dtiris_branck_mngr_hierarchy] --WHERE [OrgUnitManager] = @Mngr_Name --AND OrgUnitID = @Mngr_OrgUnitID UNION ALL SELECT DISTINCT --TOP 1000 [OrgUnitManager] [child2_orgunitid], orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '3' AS mngrlevel, [orgunitmanager], child2_orgunitname FROM [ByD_Reporting].[dbo].[vw_dtiris_branck_mngr_hierarchy] --WHERE [OrgUnitManager] = @Mngr_Name --AND OrgUnitID = @Mngr_OrgUnitID UNION ALL SELECT DISTINCT --TOP 1000 [OrgUnitManager] [child3_orgunitid], orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '3' AS mngrlevel, [orgunitmanager], child3_orgunitname -- ,[CHILD3_OrgUnitName] FROM [ByD_Reporting].[dbo].[vw_dtiris_branck_mngr_hierarchy] --WHERE [OrgUnitManager] = @Mngr_Name --AND OrgUnitID = @Mngr_OrgUnitID UNION ALL SELECT DISTINCT child4_orgunitid, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '5' AS mngrlevel, orgunitmanager, child4_orgunitname FROM dbo.vw_dtiris_branck_mngr_hierarchy UNION ALL SELECT DISTINCT child5_orgunitid, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '6' AS mngrlevel, orgunitmanager, child5_orgunitname FROM dbo.vw_dtiris_branck_mngr_hierarchy UNION ALL SELECT DISTINCT child6_orgunitid, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '7' AS mngrlevel, orgunitmanager, child6_orgunitname FROM dbo.vw_dtiris_branck_mngr_hierarchy UNION ALL SELECT DISTINCT child7_orgunitid, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '8' AS mngrlevel, orgunitmanager, child7_orgunitname FROM dbo.vw_dtiris_branck_mngr_hierarchy UNION ALL SELECT DISTINCT child8_orgunitid, orgunitname AS MngrOrg, orgunitid AS MngrOrgID, '9' AS mngrlevel, orgunitmanager, child8_orgunitname FROM dbo.vw_dtiris_branck_mngr_hierarchy --UNION ALL --SELECT DISTINCT CHILD9_OrgUnitID, OrgUnitName AS MngrOrg, OrgUnitID AS MngrOrgID, '10' AS mngrlevel, OrgUnitManager, CHILD9_OrgUnitName --FROM dbo.VW_DTIRIS_Branck_Mngr_Hierarchy --UNION ALL --SELECT DISTINCT CHILD10_OrgUnitID, OrgUnitName AS MngrOrg, OrgUnitID AS MngrOrgID, '11' AS mngrlevel, OrgUnitManager, CHILD10_OrgUnitName -- FROM dbo.VW_DTIRIS_Branck_Mngr_Hierarchy --UNION ALL --SELECT DISTINCT CHILD11_OrgUnitID, OrgUnitName AS MngrOrg, OrgUnitID AS MngrOrgID, '12' AS mngrlevel, OrgUnitManager, CHILD11_OrgUnitName --FROM dbo.VW_DTIRIS_Branck_Mngr_Hierarchy ) OrgQ WHERE OrgQ.orgid IS NOT NULL) b LEFT OUTER JOIN [ByD_Reporting].[dbo].[vw_dtiris_branck_mngr_hierarchy] o ON b.orgid = o.orgunitid LEFT OUTER JOIN dbo.vw_manager_email c ON b.[orgunitmanager] = c.mngrname -- Logging UPDATE [ETL].[migration_log] SET [status] = 'COMPLETED', [timestamp] = Getdate(), [enddate] = Getdate() WHERE logid = @log_id END try BEGIN catch SELECT @error_num = Error_number(), @error_msg = Error_message() -- Logging UPDATE [ETL].[migration_log] SET [status] = 'ERROR; Error Num-' + Cast(@error_num AS VARCHAR), [timestamp] = Getdate() WHERE logid = @log_id RETURN -1 END catch RETURN 1 END go Here is the view it is joining over and over, The problem is when this SP is run it consumes all the memory and DBA ends up terminating it. Any help would be highly appreciated CREATE VIEW [dbo].[VW_DTIRIS_Branck_Mngr_Hierarchy] AS SELECT DISTINCT -- TOP 100 PERCENT p.OrgUnitManager , p.OrgUnitID , p.OrgUnitName , p.ParentID AS CHILD1_ParentID , C.OrgUnitManager AS CHILD1_OrgUnitManager , C.OrgUnitID AS CHILD1_OrgUnitID , C.OrgUnitName AS CHILD1_OrgUnitName , C2.OrgUnitManager AS CHILD2_OrgUnitManager , C2.OrgUnitID AS CHILD2_OrgUnitID , C2.OrgUnitName AS CHILD2_OrgUnitName , C3.OrgUnitManager AS CHILD3_OrgUnitManager , C3.OrgUnitID AS CHILD3_OrgUnitID , C3.OrgUnitName AS CHILD3_OrgUnitName , C4.OrgUnitID AS CHILD4_OrgUnitID , C4.OrgUnitName AS CHILD4_OrgUnitName , C4.OrgUnitManager AS CHILD4_OrgUnitManager , C5.OrgUnitID AS CHILD5_OrgUnitID , C5.OrgUnitName AS CHILD5_OrgUnitName , C5.OrgUnitManager AS CHILD5_OrgUnitManager , C6.OrgUnitName AS CHILD6_OrgUnitName , C6.OrgUnitManager AS CHILD6_OrgUnitManager , C6.OrgUnitID AS CHILD6_OrgUnitID , C7.OrgUnitID AS CHILD7_OrgUnitID , C7.OrgUnitName AS CHILD7_OrgUnitName , C7.OrgUnitManager AS CHILD7_OrgUnitManager , C8.OrgUnitID AS CHILD8_OrgUnitID , C8.OrgUnitName AS CHILD8_OrgUnitName , C8.OrgUnitManager AS CHILD8_OrgUnitManager , C9.OrgUnitID AS CHILD9_OrgUnitID , C9.OrgUnitName AS CHILD9_OrgUnitName , C9.OrgUnitManager AS CHILD9_OrgUnitManager , C10.OrgUnitID AS CHILD10_OrgUnitID , C10.OrgUnitName AS CHILD10_OrgUnitName , C10.OrgUnitManager AS CHILD10_OrgUnitManager , C11.OrgUnitID AS CHILD11_OrgUnitID , C11.OrgUnitName AS CHILD11_OrgUnitName , C11.OrgUnitManager AS CHILD11_OrgUnitManager FROM dbo.VW_DIM_Current_OrgStructure AS C6 LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C7 ON C6.OrgUnitID = C7.ParentID RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C5 ON C6.ParentID = C5.OrgUnitID RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C4 RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C3 ON C4.ParentID = C3.OrgUnitID RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS p LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C ON p.OrgUnitID = C.ParentID LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C2 ON C.OrgUnitID = C2.ParentID ON C3.ParentID = C2.OrgUnitID ON C5.ParentID = C4.OrgUnitID LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C8 ON C7.OrgUnitID = C8.ParentID LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C9 ON C8.OrgUnitID = C9.ParentID LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C10 ON C9.OrgUnitID = C10.ParentID LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C11 ON C10.OrgUnitID = C11.ParentID --WHERE (p.OrgUnitManager IS NOT NULL) --ORDER BY p.OrgUnitManager, CHILD1_OrgUnitManager, CHILD2_OrgUnitManager, CHILD3_OrgUnitManager GO