1 / 39

Optimizing SQL Security Tree: Insights and Strategies for Performance Enhancement

Dive deep into the technical intricacies of SQL security trees with David Kurtz from Go-Faster Consultancy Ltd. This presentation explores advanced SQL optimization techniques, including complex queries, execution plans, and indexing strategies. Learn how to effectively manage department security trees within HRMS systems, streamline data access using operator access restrictions, and implement best practices for financials roll-up reporting. Delve into topics like query simplification, performance rules, and optimizing for Oracle database environments.

gzifa
Download Presentation

Optimizing SQL Security Tree: Insights and Strategies for Performance Enhancement

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

  2. Why Trees?

  3. This is a technicalpresentation • Complex SQL • Optimisers • Query Execution Plans • Indexes • Replication

  4. HRMS Department Security Tree Financials Roll-Up Reporting nVision Summary Ledgers PeopleSoft Applications

  5. Department Security Tree • A operator has access to those employees who have, or who will have, jobs in or below (as defined by the department security tree in force as at a given date) those departments to which the operator has been given access

  6. Panel Search Dialogue

  7. Panel Search Record Query SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, ... FROM PS_PERS_SRCH_GBL WHERE EMPLID LIKE '8%' AND OPRCLASS='ALLPANLS' ORDER BY EMPLID

  8. PERS_SRCH / EMPLMT_SRCH CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT … FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND ND.COUNTRY=NDT.COUNTRY AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND ( B.EFFDT>=%CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD#=B.EMPL_RCD# AND B2.EFFDT<=%CURRENTDATEIN) AND B.EFFSEQ= (SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID=B.EMPLID AND B3.EMPL_RCD#=B.EMPL_RCD# AND B3.EFFDT=B.EFFDT ) ) ) AND SEC.ACCESS_CD='Y' AND EXISTS (SELECT 'X' FROM PSTREENODE SEC3 WHERE SEC3.SETID = SEC.SETID AND SEC3.SETID = B.SETID_DEPT AND SEC3.TREE_NAME='DEPT_SECURITY' AND SEC3.EFFDT= SEC.TREE_EFFDT AND SEC3.TREE_NODE=B.DEPTID AND SEC3.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_DEPT SEC2 WHERE SEC.OPRID = SEC2.OPRID AND SEC.SETID = SEC2.SETID AND SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM AND SEC3.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM AND SEC2.TREE_NODE_NUM_END AND SEC2.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END))

  9. Tree-Reading Security View

  10. Options for Optimisation • Simplification • Flattening • Optimiser • Oracle only: Cost -v- Rule • Pre-process • Generated tables • Replication • Data Latency -v- Performance

  11. Rule Old (Stable) Inflexible Predictable Influence Cost New Hints (Oracle) Statistics Distributions Maintenance Optimisers

  12. WHERE EXISTS(sub-query) CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT … FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND ND.COUNTRY=NDT.COUNTRY AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND (B.EFFDT>=%CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD#=B.EMPL_RCD# AND B2.EFFDT<=%CURRENTDATEIN) AND B.EFFSEQ= (SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID=B.EMPLID AND B3.EMPL_RCD#=B.EMPL_RCD# AND B3.EFFDT=B.EFFDT ) ) ) AND SEC.ACCESS_CD='Y' AND EXISTS (SELECT 'X' FROM PSTREENODE SEC3 WHERE SEC3.SETID = SEC.SETID AND SEC3.SETID = B.SETID_DEPT AND SEC3.TREE_NAME='DEPT_SECURITY' AND SEC3.EFFDT= SEC.TREE_EFFDT AND SEC3.TREE_NODE=B.DEPTID AND SEC3.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_DEPT SEC2 WHERE SEC.OPRID = SEC2.OPRID AND SEC.SETID = SEC2.SETID AND SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM AND SEC3.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM AND SEC2.TREE_NODE_NUM_END AND SEC2.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END))

  13. WHERE EXISTS(sub-query) ... AND EXISTS (SELECT 'X' FROM PSTREENODE SEC3 WHERE SEC3.SETID = SEC.SETID AND SEC3.SETID = B.SETID_DEPT AND SEC3.TREE_NAME='DEPT_SECURITY' AND SEC3.EFFDT= SEC.TREE_EFFDT AND SEC3.TREE_NODE=B.DEPTID AND SEC3.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END ...

  14. WHERE NOT EXISTS(sub-query) CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT … FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC, PSTREENODE SEC3 WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLID AND ND.COUNTRY=NDT.COUNTRY AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND ( B.EFFDT>=%CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID=B2.EMPLID AND B.EMPL_RCD#=B2.EMPL_RCD# AND B2.EFFDT<=%CURRENTDATEIN ) AND B.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD#=B3.EMPL_RCD# AND B.EFFDT=B3.EFFDT))) AND SEC.ACCESS_CD='Y' AND SEC3.SETID = SEC.SETID AND SEC3.SETID = B.SETID_DEPT AND SEC3.TREE_NAME='DEPT_SECURITY' AND SEC3.EFFDT= SEC.TREE_EFFDT AND SEC3.TREE_NODE=B.DEPTID AND SEC3.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_DEPT SEC2 WHERE SEC.OPRID = SEC2.OPRID AND SEC.SETID = SEC2.SETID AND SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM AND SEC3.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM AND SEC2.TREE_NODE_NUM_END AND SEC2.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END)

  15. WHERE NOT EXISTS(sub-query) … AND NOT EXISTS (SELECT 'X' FROM PS_SCRTY_TBL_DEPT SEC2 WHERE SEC.OPRID = SEC2.OPRID AND SEC.SETID = SEC2.SETID AND SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM AND SEC3.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM AND SEC2.TREE_NODE_NUM_END AND SEC2.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END)

  16. Workaround to Outer-join to 2 tables CREATE OR REPLACE VIEW fudge_vw (...) AS SELECT ... FROM PSTREENODE E, PS_SCRTY_TBL_DEPT C WHERE C.ACCESS_CD='Y' AND E.SETID=C.SETID AND E.TREE_NAME='DEPT_SECURITY' AND E.EFFDT=C.TREE_EFFDT AND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END

  17. WHERE NOT EXISTS(sub-query) • Don’t try this at home AND FDG.SETID = B.SETID_DEPT AND FDG.TREE_NODE=B.DEPTID AND SEC2.OPRID IS NULL AND FDG.OPRID = SEC2.OPRID(+) AND FDG.SETID = SEC2.SETID(+) AND FDG.T_TREE_NODE_NUM >= SEC2.TREE_NODE_NUM(+) AND FDG.T_TREE_NODE_NUM <=SEC2.TREE_NODE_NUM_END(+) AND FDG.S_TREE_NODE_NUM <> SEC2.TREE_NODE_NUM (+) AND FDG.S_TREE_NODE_NUM <= SEC2.TREE_NODE_NUM (+) AND FDG.S_TREE_NODE_NUM_END >= SEC2.TREE_NODE_NUM (+)

  18. Fully Flattened View • Don’t try this at home CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT ... FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC2, PS_FUDGE_VW FDG WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLID AND ND.COUNTRY=NDT.COUNTRY AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND ( B.EFFDT>= %CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID=B2.EMPLID AND B.EMPL_RCD#=B2.EMPL_RCD# AND B2.EFFDT<= %CURRENTDATEIN) AND B.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD#=B3.EMPL_RCD# AND B.EFFDT=B3.EFFDT))) AND FDG.SETID = B.SETID_DEPT AND FDG.TREE_NODE=B.DEPTID AND SEC2.OPRID IS NULL AND FDG.OPRID = SEC2.OPRID(+) AND FDG.SETID = SEC2.SETID(+) AND FDG.T_TREE_NODE_NUM >= SEC2.TREE_NODE_NUM(+) AND FDG.T_TREE_NODE_NUM <= SEC2.TREE_NODE_NUM_END(+) AND FDG.S_TREE_NODE_NUM <> SEC2.TREE_NODE_NUM(+) AND FDG.S_TREE_NODE_NUM <= SEC2.TREE_NODE_NUM(+) AND FDG.S_TREE_NODE_NUM_END >= SEC2.TREE_NODE_NUM(+)

  19. So what is the benefit of flattening? • Depends upon the conditions • WHERE EMPLID = ‘1234’ • slightly worse • WHERE EMPLID like ‘1234%’ • no difference • WHERE NAME = ‘SMITH’ • better • WHERE NAME LIKE ‘SMI%’ • much better

  20. So what is the benefit of flattening?

  21. And there’s more!

  22. Pre-generated tables • Pre-join the data • Once when generate • Not every time in the view • Extra indexes • Latency • frequency of regeneration

  23. Security Table CREATE TABLE PS_SECURITY AS SELECT E.TREE_NODE, C.OPRID, C.SETID FROM PS_SCRTY_TBL_DEPT C, PSTREENODE E WHERE C.ACCESS_CD='Y' AND E.SETID=C.SETID AND E.TREE_NAME='DEPT_SECURITY' AND E.EFFDT=C.TREE_EFFDT AND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END AND NOT EXISTS( SELECT 'X' FROM PS_SCRTY_TBL_DEPT G WHERE C.OPRID=G.OPRID AND C.TREE_NODE_NUM<>G.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END AND G.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)

  24. PERS_SRCH CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT ... FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_PERS_NID_TYPE NDT, SECURITY SEC WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLID AND ND.COUNTRY=NDT.COUNTRY AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND ( B.EFFDT>= %CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID=B2.EMPLID AND B.EMPL_RCD#=B2.EMPL_RCD# AND B2.EFFDT<= %CURRENTDATEIN) AND B.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD#=B3.EMPL_RCD# AND B.EFFDT=B3.EFFDT))) AND SEC.SETID = B.SETID_DEPT AND SEC.TREE_NODE=B.DEPTID

  25. PERSONAL_DATA, JOB & NID CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT DISTINCT ... FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLID AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND ( B.EFFDT>=%CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD#=B.EMPL_RCD# AND B2.EFFDT<=%CURRENTDATEIN) AND B.EFFSEQ= (SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID=B.EMPLID AND B3.EMPL_RCD#=B.EMPL_RCD# AND B3.EFFDT=B.EFFDT)))

  26. Current and future JOB CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT DISTINCT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT FROM PS_JOB B WHERE ( B.EFFDT>=%CURRENTDATEIN OR ( B.EFFDT=( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD#=B.EMPL_RCD# AND B2.EFFDT<=%CURRENTDATEIN) AND B.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID=B.EMPLID AND B3.EMPL_RCD#=B.EMPL_RCD# AND B3.EFFDT=B.EFFDT)))

  27. Maintain via PeopleCode • JOB.DEPTID.SavePostChg /* maintain GEN_JOB_TBL whenever an update to PS_JOB is made */ SQLExec(”delete from PS_GEN_JOB_TBL where EMPLID = :1 and EMPL_RCD# = :2", EMPLID, EMPL_RCD#); SQLExec("insert into PS_GEN_JOB_TBL select * from PS_GEN_JOB_VW where EMPLID = :1 and EMPL_RCD# = :2", EMPLID, EMPL_RCD#);

  28. Current or first JOB, but no future CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT FROM PS_JOB B WHERE ( B.EFFDT= (SELECT MAX(D.EFFDT) FROM PS_JOB D WHERE B.EMPLID=D.EMPLID AND B.EMPL_RCD#=D.EMPL_RCD# AND D.EFFDT<=%CURRENTDATEIN) OR B.EFFDT= (SELECT MIN(E.EFFDT) FROM PS_JOB E WHERE B.EMPLID=e.EMPLID AND B.EMPL_RCD#=E.EMPL_RCD# HAVING MIN(E.EFFDT)>%CURRENTDATEIN)) AND B.EFFSEQ=(SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD#=B3.EMPL_RCD# AND B.EFFDT=B3.EFFDT))

  29. Maintain via PeopleCode • JOB.DEPTID.SavePostChg /* maintain GEN_JOB_TBL whenever an update to PS_JOB is made */ &TMP = 0; SQLExec("select 1 from PS_GEN_JOB_TBL where EMPLID = :1 and EMPL_RCD# = :2", EMPLID, EMPL_RCD#, &TMP); If %SqlRows > 0 Then SQLExec("update PS_GEN_JOB_TBL set (DEPTID, SETID_DEPT) = (SELECT DEPTID, SETID_DEPT) from PS_GEN_JOB_VW where EMPLID = :1 and EMPL_RCD# = :2) where EMPLID = :1 and EMPL_RCD# = :2", EMPLID, EMPL_RCD#); Else SQLExec("insert into PS_GEN_JOB_TBL select * from PS_GEN_JOB_VW where EMPLID = :1 and EMPL_RCD# = :2", EMPLID, EMPL_RCD#); End-If;

  30. Panel Search Record CREATE OR REPLACE VIEW PERS_SRCH_GBL(...) AS SELECT /*+ALL_ROWS*/ ... FROM PS_PERSONAL_DATA A, PS_GEN_JOB_TBL B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SECURITY SEC WHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLID AND ND.COUNTRY=NDT.COUNTRY AND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE AND SEC.SETID = B.SETID_DEPT AND SEC.TREE_NODE=B.DEPTID

  31. Query Security Record CREATE OR REPLACE VIEW PS_EMPLMT_SRCH_QRY (EMPLID, EMPL_RCD#, OPRCLASS) AS SELECT DISTINCT A.EMPLID, A.EMPL_RCD#, S.OPRID FROM PS_SECURITY S, PS_GEN_JOB_TBL A WHERE S.TREE_NODE=A.DEPTID AND S.SETID=A.SETID_DEPT

  32. Current & Current or future JOB CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT, MIN(B.EFFDT) FROM PS_JOB B WHERE (B.EFFDT>= (SELECT NVL(MAX(D.EFFDT),%CURRENTDATEIN) FROM PS_JOB D WHERE B.EMPLID=D.EMPLID AND B.EMPL_RCD#=D.EMPL_RCD# AND D.EFFDT<=%CURRENTDATEIN) AND B.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD#=B3.EMPL_RCD# AND B.EFFDT=B3.EFFDT)) GROUP BY B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT

  33. Read only & Read/Write Security CREATE TABLE PS_SECURITY AS SELECT E.TREE_NODE, C.OPRID, C.SETID, C.ACCESS_CD FROM PS_SCRTY_TBL_DEPT C, PSTREENODE E WHERE C.ACCESS_CD != 'N' AND E.SETID=C.SETID AND E.TREE_NAME='DEPT_SECURITY' AND E.EFFDT=C.TREE_EFFDT AND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END AND NOT EXISTS( SELECT 'X' FROM PS_SCRTY_TBL_DEPT G WHERE C.OPRID=G.OPRID AND C.TREE_NODE_NUM<>G.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END AND G.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)

  34. Current Only/Read Write Security • Current & Current or future JOB CREATE OR REPLACE VIEW PS_EMPLMT_SRCH_QRY (EMPLID, EMPL_RCD#, OPRCLASS) AS SELECT DISTINCT A.EMPLID, A.EMPL_RCD#, S.OPRID FROM PS_SECURITY S, PS_GEN_JOB_TBL A WHERE S.TREE_NODE=A.DEPTID AND S.SETID=A.SETID_DEPT AND A.EFFDT <= %CURRENTDATEIN AND S.ACCESS_CD = 'Y'

  35. The benefit of generated tables?

  36. The benefit of generated tables?

  37. Tree Reading Query Performance • Security Views • Flatten • Cost Based Optimiser • Pre-Generated tables

  38. Implementation recommendations • Panel Search Records • Two generated tables with PeopleCode • Query Security Records • Two generated tables • Remove duplicates • Distinct • Current Security Only

  39. HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

More Related