question

sandeepiii avatar image
sandeepiii asked

Query issue

Hi Expert,Could you pls help me with below query in tuning.Thanks WITH ou_hier AS (SELECT EFF_DT, LoadStatusId, InsertType, SURR_ID, SUM_NAVN, REGNR_TYPE, P_GRUPPE_ID, C_GRUPPE_ID, ORG_KSVAKD FROM LHCM_CIB_CPY.cpy.RHRH_OrgUnitHierarchy AS hier WHERE (EFF_DT = (SELECT MAX(EFF_DT) AS Expr1 FROM LHCM_CIB_CPY.cpy.RHRH_OrgUnitHierarchy WHERE (C_GRUPPE_ID = hier.C_GRUPPE_ID)))), HierarchyTopToBottom(EFF_DT, LoadStatusId, InsertType, SURR_ID, SUM_NAVN, REGNR_TYPE, P_GRUPPE_ID, C_GRUPPE_ID, ORG_KSVAKD, HIER_CODE, TOP_F, LWS_F, ANC_LEVEL, DESC_LEVEL, SortOrder, Level1, Level2, Level3, Level4, Level5, Level6, Level7, Level8, Level9, Level10, Level11, Level12) AS (SELECT DISTINCT ou_hier.EFF_DT, ou_hier.LoadStatusId, ou_hier.InsertType, ou_hier.SURR_ID, ou_hier.SUM_NAVN, ou_hier.REGNR_TYPE, ou_hier.P_GRUPPE_ID, ou_hier.C_GRUPPE_ID, ou_hier.ORG_KSVAKD, cv_s.CL_CODE AS HIER_CODE, '1' AS TOP_F, CASE WHEN EXISTS (SELECT ou_hier2.P_GRUPPE_ID FROM LHCM_CIB_CPY.cpy.RHRH_OrgUnitHierarchy ou_hier2 WHERE ou_hier2.P_GRUPPE_ID = ou_hier.C_GRUPPE_ID) THEN '0' ELSE '1' END AS LWS_F, 1 AS ANC_LEVEL, 2 AS DESC_LEVEL, CAST(ou_hier.C_GRUPPE_ID AS VARCHAR(4000)) AS SortOrder, ou_hier.C_GRUPPE_ID AS Level1, CAST(' ' AS NCHAR(5)) AS Level2, CAST(' ' AS NCHAR(5)) AS Level3, CAST(' ' AS NCHAR(5)) AS Level4, CAST(' ' AS NCHAR(5)) AS Level5, CAST(' ' AS NCHAR(5)) AS Level6, CAST(' ' AS NCHAR(5)) AS Level7, CAST(' ' AS NCHAR(5)) AS Level8, CAST(' ' AS NCHAR(5)) AS Level9, CAST(' ' AS NCHAR(5)) AS Level10, CAST(' ' AS NCHAR(5)) AS Level11, CAST(' ' AS NCHAR(5)) AS Level12 FROM ou_hier INNER JOIN sor.vwCV_S AS cv_s ON ou_hier.P_GRUPPE_ID = cv_s.CL_CODE WHERE (cv_s.SCM_CODE = 'IP_NVG_GFS') AND (cv_s.CL_CODE = 'BFORI') AND (ou_hier.C_GRUPPE_ID = 'BFORR') UNION ALL SELECT ou_hier.EFF_DT, ou_hier.LoadStatusId, ou_hier.InsertType, ou_hier.SURR_ID, ou_hier.SUM_NAVN, ou_hier.REGNR_TYPE, ou_hier.P_GRUPPE_ID, ou_hier.C_GRUPPE_ID, ou_hier.ORG_KSVAKD, hier.HIER_CODE, '0' AS TOP_F, CASE WHEN EXISTS (SELECT ou_hier2.P_GRUPPE_ID FROM LHCM_CIB_CPY.cpy.RHRH_OrgUnitHierarchy ou_hier2 WHERE ou_hier2.P_GRUPPE_ID = ou_hier.C_GRUPPE_ID) THEN '0' ELSE '1' END AS LWS_F, hier.ANC_LEVEL + 1 AS Expr1, hier.DESC_LEVEL + 1 AS Expr2, CAST(hier.SortOrder + ', ' + ou_hier.C_GRUPPE_ID AS VARCHAR(4000)) AS SortOrder, hier.Level1, CASE WHEN hier.DESC_LEVEL = 2 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level2 END AS Level2, CASE WHEN hier.DESC_LEVEL = 3 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level3 END AS Level3, CASE WHEN hier.DESC_LEVEL = 4 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level4 END AS Level4, CASE WHEN hier.DESC_LEVEL = 5 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level5 END AS Level5, CASE WHEN hier.DESC_LEVEL = 6 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level6 END AS Level6, CASE WHEN hier.DESC_LEVEL = 7 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level7 END AS Level7, CASE WHEN hier.DESC_LEVEL = 8 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level8 END AS Level8, CASE WHEN hier.DESC_LEVEL = 9 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level9 END AS Level9, CASE WHEN hier.DESC_LEVEL = 10 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level10 END AS Level10, CASE WHEN hier.DESC_LEVEL = 11 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level11 END AS Level11, CASE WHEN hier.DESC_LEVEL = 12 THEN ou_hier.C_GRUPPE_ID ELSE hier.Level12 END AS Level12 FROM ou_hier INNER JOIN HierarchyTopToBottom AS hier ON ou_hier.P_GRUPPE_ID = ou_hier.C_GRUPPE_ID) SELECT EFF_DT, LoadStatusId, InsertType, SURR_ID, SUM_NAVN, REGNR_TYPE, P_GRUPPE_ID, C_GRUPPE_ID, ORG_KSVAKD, CAST(HIER_CODE AS NVARCHAR(10)) AS HIER_CODE, CAST(TOP_F AS NCHAR(1)) AS TOP_F, CAST(LWS_F AS NCHAR(1)) AS LWS_F, CAST(ANC_LEVEL AS SMALLINT) AS ANC_LEVEL, CAST(DESC_LEVEL AS SMALLINT) AS DESC_LEVEL, SortOrder, Level1, Level2, Level3, Level4, Level5, Level6, Level7, Level8, Level9, Level10, Level11, Level12 FROM HierarchyTopToBottom
developerdevelopment
1 comment
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 you want help tuning this query, you'll want to add some information about how fast the query is performing right now, how fast you need it to perform, which indexes you have on the tables, which tuning options you have already tried out etc. And for future questions, when posting code, please mark the code in the editing window and click the little icon with Ones and Zeroes. That will make the SQL code look nice and code-ish when you save.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Without the structures and execution plan, all I can do is point out common code smells for you to address. The CASE ... EXISTS statement as a correlated sub-query in the SELECT list is likely to be a pretty major performance bottleneck. You're better of finding a way to make that into a JOIN instead of what you have. Also, the DISTINCT statement on that list requires SQL Server to do aggregation, which is frequently a cause of slower queries. Put the appropriate filtering and joins in place to get a unique data set rather than rely on the aggregation. If the data is broken, fix the data. I'm not sure what that function HierarchyTopToBottom does (beyond it's name), but that may need separate tuning. And all this assumes that these tables, the joins, and the filtering criteria are all using good indexes with up to date statistics. If any of that's not true, you'll need to address that as well.
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.

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.