question

Henu_Nogia avatar image
Henu_Nogia asked

how do i optimize sql sp

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
stored-proceduresmssqloptimizationmssql2008
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If I'm right in assuming that this is an attempt to calculate the profit that can be ascribed to any manager, as being the sum of the profits ascribed to all the management that report to him, and so on, then there is a much much simpler way of doing this. There is nothing that can be rescued from this code beyond, maybe, reverse-engineering the requirements.
2 Likes 2 ·
Can you share the query plans for a typical and an atypical execution of the procedure?
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Your structure is fundamentally flawed in some way. You're using SELECT DISTINCT against the same table over and over with not a single WHERE clause to filter the information. DISTINCT is an aggregation command that, without any indexes to use because of the lack of filtering, will be forced to perform scans on your table. The only way to improve this query, as is, will be to get faster disks. From what I can tell, your fundamental table structure is flawed, otherwise you wouldn't have to query the same table over and over. Rethink how you're storing this data. To get help here, I don't recommend you concentrate on this query. Instead, describe the business problem you're attempting to solve with this structure. That's what needs help.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Unfortunately, there's just not a run-faster switch on stuff. All the DISTINCT statements are absolutely hurting performance. But, just guessing, probably necessary because the structure is flawed and the data inside the table may be incorrect. You can try removing all the DISTINCT statements and seeing if the data returned is the same and what the performance is. The other issue is all the WHERE clauses are removed, so there's no opportunity for the optimizer to use indexes in order to speed up access to the data. It has to scan the tables. The fact that you're scanning the table over and over again... there just aren't opportunities here for speeding things up. Sorry I'm not being more helpful. When a structure is fundamentally flawed, the opportunities for making it run faster are limited.
1 Like 1 ·
Hi Grant thanks for taking some timeout and replying to it. I am not a SQL genius and this is the first time I'm working in professional environment with SQL. This Query was written by SQL developer and I am supporting the reports, which gets data from the tables getting data through this query. I would rather optimize it slightly to make it perform ideally in prod server without eating up all the memory available. Any suggestions would be appreciated.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.