1 / 44

SQL Best Coding Practice in PeopleTools 7.x

SQL Best Coding Practice in PeopleTools 7.x. David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk. Who are you?. Familiar with SQL not necessarily the DBA Might be Application developer Team Leader. So Where is all the SQL?. Views Scrollxxx() PeopleCode

isha
Download Presentation

SQL Best Coding Practice in PeopleTools 7.x

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. SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

  2. Who are you? • Familiar with SQL • not necessarily the DBA • Might be • Application developer • Team Leader

  3. So Where is all the SQL? • Views • Scrollxxx() PeopleCode • SQLExec() PeopleCode • Mass Change • Application Engine • PS/Query / Crystal • SQR • Stored Statements

  4. Views

  5. Scrollxxx() PeopleCode

  6. SQLExec() PeopleCode

  7. Mass Change

  8. Application Engine

  9. PS/Query / Crystal

  10. SQR

  11. Stored Statements

  12. Simplicity • As simple as possible • As complicated as necessary • Data Model • Avoid excessive I/O

  13. SQL • Coding Standard • Implicit Type Conversion • Sorts • Indexes • Sub-queries

  14. Coding Standards • Be explicit • Make it readable • Indent sub-queries and brackets • Use table aliases • Lower parse time • Less unexpected results without error messages • Avoid implicit type conversion • Avoid possible Y2K issue • Explicitly specify Y2K compliant date formats

  15. Readability 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)))

  16. Minimum table aliases SELECT COUNT(*) FROM PS_JOB B WHERE ( EFFDT=( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B.EMPLID=EMPLID AND B.EMPL_RCD#=EMPL_RCD# AND B.EFFDT<=%CURRENTDATEIN) AND EFFSEQ= (SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B.EMPLID=EMPLID AND B.EMPL_RCD#=EMPL_RCD# AND B.EFFDT=EFFDT))

  17. Maximum table aliases SELECT COUNT(*) FROM PS_JOB B WHERE ( B.EFFDT=( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID=B.EMPLID AND B1.EMPL_RCD#=B.EMPL_RCD# AND B1.EFFDT<=%CURRENTDATEIN) AND B.EFFSEQ= (SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD#=B.EMPL_RCD# AND B2.EFFDT=B.EFFDT))

  18. Wrong table alias SELECT COUNT(*) FROM PS_JOB B WHERE ( EFFDT=( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B.EMPLID=EMPLID AND B.EMPL_RCD#=EMPL_RCD# AND B.EFFDT<=%CURRENTDATEIN) AND EFFSEQ= (SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID=EMPLID AND B2.EMPL_RCD#=EMPL_RCD# AND B2.EFFDT=EFFDT))

  19. SELECT * FROM PS_JOB WHERE EMPLID=8001 SELECT * FROM PS_JOB WHERE EMPLID=‘8001’ Implicit Type Conversion

  20. SELECT * FROM PS_JOB WHERE EMPLID=8001 TABLE ACCESS (FULL) OF 'PS_JOB’ why did this not use the index? SELECT * FROM PS_JOB WHERE EMPLID=‘8001’ INDEX (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE) Implicit Type Conversion

  21. SELECT * FROM PS_JOB WHERE EMPLID=8001 TABLE ACCESS (FULL) OF 'PS_JOB’ why did this not use the index? SELECT * FROM PS_JOB WHERE TO_NUMBER(EMPLID)=8001 SELECT * FROM PS_JOB WHERE EMPLID=‘8001’ INDEX (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE) Implicit Type Conversion

  22. Sorts • Updating indexed columns • Distinct • Order by • Group by • Union -v- Union All

  23. Indexes • >~ 200 rows • <~ 10% • Very rough guidelines • Avoid updating indexed columns

  24. Distinct • Sorts whole select list • Can drive the join order of the tables • Avoid distinct & order by • Order one way for the distinct • Order another way for the order by

  25. Distinct SELECT DISTINCT A,B,C,D FROM table ORDER BY A,B,C • ‘Order by’ clause unnecessary

  26. Instead of SELECT DISTINCT A,B,C,D FROM table ORDER BY A,C,B Distinct

  27. Instead of SELECT DISTINCT A,B,C,D FROM table ORDER BY A,C,B Try this SELECT DISTINCT A,C,B, D FROM table Distinct

  28. Instead of SELECT A,B,C, SUM(D) FROM table GROUP BY A,B,C ORDER BY A,C,B Group by

  29. Instead of SELECT A,B,C, SUM(D) FROM table GROUP BY A,B,C ORDER BY A,C,B Try this SELECT A,C,B, SUM(D) FROM table GROUP BY A,C,B Group by

  30. Union -v- Union All • Union • Each query is distinct, and so is sorted • Duplicates are eliminated • Union All • One query followed by the next

  31. SELECT 1 FROM dual UNION SELECT 1 FROM dual Returns Dummy ----- 1 SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual Returns Dummy ----- 1 1 Union -v- Union All

  32. SELECT 2 FROM dual UNION SELECT 1 FROM dual Returns Dummy ----- 1 2 SELECT 2 FROM dual UNION ALL SELECT 1 FROM dual Returns Dummy ----- 2 1 Union -v- Union All

  33. Disabling Indexes • Functions on columns • Index disabled • TO_CHAR(column,’DD-MM-YYYY’) = :bind • Index enabled • column = TO_DATE(:bind, ’DD-MM-YYYY’) • Oracle syntax, generic principle

  34. Use all indexed columns • Specify all indexed columns • Cannot exact scan column unless exact scan all previous columns

  35. SELECT … FROM PS_JOB B WHERE EMPLID = :1 AND EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EFFDT = B.EFFDT) SELECT … FROM PS_JOB B WHERE EMPLID = :1 AND EFFDT = ( SELECT MAX(EFFSEQ) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD# = B.EMPL_RCD# AND B1.EFFDT = B.EFFDT) Use all indexed columns

  36. High Water Marks (Oracle) • Oracle specific • Delete -v- Truncate DELETE FROM table; TRUNCATE TABLE table; • Full Scans - HWM

  37. Sub-queries • Correlated • executed once per parent row • Not Correlated • executed once in advance

  38. Correlated DELETE FROM table1 t1 WHERE EXISTS( SELECT ‘x’ FROM table2 t2 WHERE t1.keycolumn = t2.keycolumn) Non-Correlated DELETE FROM table1 t1 WHERE t1.keycolumn IN( SELECT t2.keycolumn FROM table2 t2) Sub-queries

  39. Sub-queries (Oracle) • Oracle specific DELETE FROM table1 t1 WHERE (t1.keycolumn1, t1.keycolumn2) IN( SELECT t2.keycolumn1, t2.keycolumn2 FROM table2 t2)

  40. ROWID (Oracle) • Oracle specific • physical address of row

  41. Sub-queries (Oracle) • Oracle specific DELETE FROM table1 t1 WHERE t1.rowid IN( SELECT t1b.rowid FROM table1 t1b, table2 t2 WHERE t1b.keycolumn1 = t2.keycolumn2 AND t1b.keycoulmn2 = t2.keycolumn2)

  42. Order of ‘From’ clause (Oracle) • Oracle specific • Rule Based Optimiser • backwards • Cost Based Optimiser • doesn’t matter • CBO + Ordered Hint • forwards • Distinct tends to override this

  43. Summary • Lots of places to write SQL • Code should be • efficient • simple • readable • explicit

  44. SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

More Related