SQL on Fire! Part 2 - Tips and Tricks

1. SQL on Fire! Part 2 Tips and Tricks around SQL

2. Agenda • Part I • SQL vs. SQL PL example • Error handling • Tables out of nowhere • Pivoting • Aggregation • Deleting duplicate rows • Alternatives to Min and Max • Part II • Mass deletes • Order processing • Moving rows between tables • Recursion • Merge • Calculating nesting levels Easy Difficult

3. Motivation – The OLTP Mantra • Reduce Codepath • Reduce Logical and Physical I/O • Reduce Network Traffic • Minimize Lock Contention • Avoid Deadlocks • High performance starts with the application.

4. Mass deleting of rows • Problem:Delete a large number of rows without excessive usage of log space. • Classic: • Delete using cursorVery slow • EXPORT remaining data, LOAD replaceOutage where table is unavailable. • Can do better!

5. Mass deleting of rows CREATEPROCEDURE purgeInventory(IN dt DATE)BEGINDECLARESQLCODEINTEGER;loop: LOOPDELETEFROM(SELECT 1 FROM InventoryWHERE InvDate <= dtFETCHFIRST 1000 ROWSONLY) AS D;IFSQLCODE = 100 THENLEAVE loop; ENDIF;COMMIT;ENDLOOP loop;ENDCALL purgeInventory(‘2003-10-01’)

6. New order processing - classic • "Submit order, provide reference # back" • Retrieve next order # SELECT nextnum FROM ordermeta; 1 I/O, S • Increment next order # UPDATE ordermeta 1 I/O SET nextnum = nextnum + 1; S->X • Insert new order INSERTINTO orders VALUES(nextnum, ...) 1 I/O • Return nextnum to user • Deadlock, 3 SQL Stmts, 3 I/O, single row

7. New order processing - improved • Use SEQUENCE/IDENTITY INSERTINTO order VALUES(NEXTVALUE FOR orderseq, ...); SET ordernum = PREVIOUSVALUEFOR orderseq; • No Deadlock, 2 SQL statements, 1 I/O, single row only • Use SELECT FROM INSERT SELECT ordernum INTO :ordernum FROMNEWTABLE(INSERT INTO orders • VALUES(NEXT VALUE FOR orderseq, ...)); • No Deadlock, 1 I/O,1 SQL Stmt, set oriented

8. New order processing – Optimal plan Access Plan: ----------- RETURN ( 1) | 1 INSERT ( 4) 1 /---+---\ 1 180 TBSCAN TABLE: ORDERS ( 5) | 1 TABFNC: GENROW

9. Queue processing – Destructive read • "retrieve and delete next in line“ • Retrieve "oldest" row in queue SELECT ordernum, ... INTO :ordernum 1I/O, S FROM orders ORDERBY ordernum FETCH FIRST ROW ONLY; • Delete the row DELETEFROM order 1 I/O, S->X WHERE :ordernum = ordernum; • Deadlock, 2 SQL Stmts, single row (or IN list)

10. Destructive read - Improved • Delete through ORDER BY SELECT ordernum, ... INTO :ordernum, ... FROMOLDTABLE(DELETE FROM(SELECT * FROM orders ORDER BY ordernum FETCH FIRST ROW ONLY)); • no Deadlock, 1 I/O, set oriented

11. Destructive Read – Optimal plan Access Plan: ----------- RETURN ( 1) | DELETE ( 4) /----+---\ IXSCAN TABLE: ORDERS ( 5) | INDEX: i1

12. Queue processing – 2-phase CREATETABLE orders(ordernum INTEGER NOT NULL, agentid INTEGER); CREATEUNIQUEINDEX orderind ON orders(ordernum ASC)INCLUDE(agentid ASC); ALTERTABLE orders ADDCONSTRAINTPK PRIMARYKEY (ordernum); Tip 1: Combine unique and non unique index using INCLUDE. Tip 2: Add primary key after creating index to control which index is used (index name and include columns).

13. Queue processing – 2 Phase

14. Queue processing – claim order SET vthisorder = (SELECT ordernumFROMOLDTABLE(UPDATE(SELECT ordernum, statusFROM ordersWHERE agentid ISNULLORDERBYordernumFETCHFIRSTROWONLY)AS USET agentid = vagentid)); COMMIT; …; -- Long processingDELETE FROM orders WHERE ordernum = vthisorder;COMMIT;

15. Queue processing – claim order Access Plan: ------------ RETURN ( 1) | TBSCAN ( 2) | SORT ( 3) | UPDATE ( 4) /---+---\ IXSCAN TABLE: ORDERS ( 5) | INDEX: ORDERIND

16. Moving duplicate rows • TaskDelete rows from one table and insert into another CREATETABLE Archive LIKE Inventory;WITHdel(Item, Quantity, InvDate) AS (SELECT Item, Quantity, InvDateFROMOLDTABLE (DELETE FROM (SELECT Item, Quantity, InvDate,row_number() OVER(PARTITIONBY Item ORDERBY InvDate DESC) AS rn FROM Inventory) WHERE rn > 1)),ins(x) AS (SELECT 1 FROMNEWTABLE(INSERTINTO ArchiveSELECT * FROMdel))SELECTCOUNT(1) FROMins;

17. Move duplicate rows RETURN ( 1) | TBSCAN ( 2) | SORT ( 3) | GRPBY ( 4) | INSERT ( 5) /---+---\ DELETE TABLE: ARCHIVE ( 6) /---+---\ FETCH TABLE: INVENTORY ( 7) /---+---\ FILTER TABLE: INVENTORY ( 8) | FETCH ( 9) /----+---\ IXSCAN TABLE: INVENTORY ( 10) | INDEX: SRIELAU Do-At-Open Dam(1-row)

18. Recursion • ProblemHave table of sales per working day.Need table of sales per calendar day. • DDLCREATETABLE Sales(day VARCHAR(10), date DATE, amount INTEGER)

19. Recursion

20. Recursion • Produce a date range CREATE FUNCTION dates(start DATE, end DATE)RETURNS TABLE(dt DATE)RETURN WITHrec(dt) AS (VALUES (start) UNION ALL SELECT dt + 1 DAY FROM rec WHEREdt < end) SELECT dt FROM rec; SELECT DAYNAME(date) AS day, date, COALESCE(sales, 0) AS salesFROM TABLE(dates(DATE('2006-05-12'), DATE('2006-05-23'))) AS datesLEFT OUTER JOIN sales ON dates.dt = sales.date;

21. Recursion

22. Recursion inside out • Seed -> rec-temp • For each row in temp execute recursion • Append to temp • Finish when 2. catches up with appends Read Cursor (z, z, z) (z, z, z) Seed (h, i, j) (k, m, n) (k, l, m) Insert (a, b, c) (d, e, f) (g, h, i) (j, k, l) (a, b, c) (d, e, f) (g, h, i) (j, k, l) (h, i, j) (k, m, n) (k, l, m) Read Cursor (a, b, c) (d, e, f) (g, h, i) (j, k, l)

23. Rec-Plan RETURN ( 1) | TBSCAN ( 2) | TEMP ( 3) | UNION ( 4) /----+---\ TBSCAN TBSCAN ( 5) ( 6) | | TEMP TABFNC: GENROW ( 3)

24. Merge • Unifies Update, Delete, Insert • Procedural statement • Set oriented processing per branch • Consistency points at each branch • SQL Standard

25. Merge Make Up MERGE INTO <target> USING <source> ON <match-condition> {WHEN [NOT] MATCHED [AND <predicate>] THEN [UPDATE SET ...|DELETE|INSERT VALUES ....|SIGNAL ...]} [ELSE IGNORE] • <target> is any updatable query • <source> is whatever query you please • <match-condition>partitions <source> into MATCHED and NOT MATCHEDEach target-row must only be matched once! • WHEN .. [<predicate>] executes THEN for subset of [not] matched rows. • each row in <source> is processed once in first qualifying WHEN only

26. Update From CREATE TABLE T(pk INT NOT NULL PRIMARY KEY,c1 INT); CREATE TABLE S(pk INT NOT NULL PRIMARY KEY,c1 INT); Standard pre SQL4 UPDATE T SET c1 = (SELECT c1 FROM SWHERE S.pk = T.pk)WHERE pk IN (SELECT pk FROM S); IBM Informix/MS SQL Server UPDATE T SET c1 = S.c1 FROM T, S WHERE T.pk = S.pk;

27. Merge: Update From MERGE INTO T USING S ON T.pk = S.pk WHEN MATCHED THEN UPDATE SET c1 = S.c1; RETURN | UPDATE /---+---\ FILTER TABLE: T | NLJOIN /-------+-------\ TBSCAN FETCH | /----+---\ TABLE: S IXSCAN TABLE: T | INDEX: T_PK

28. Upsert Standard pre SQL4(one way of many) FOR m AS SELECT T.pk tpk, S.pk spk, S.c1 FROM T RIGHT JOIN S ON T.pk = S.pk DO IF m.tpk IS NULL THEN INSERT INTO T VALUES(m.spk, m.c1); ELSE UPDATE T SET c1 = m.c1 WHERE pk = tpk; END IF; END FOR;

29. RETURN | INSERT /---+---\ TBSCAN TABLE: T | TEMP | UPDATE /---+---\ NLJOIN TABLE: T /------------------+-----------------\ NLJOIN UNION 25.7489 0.00166765 /-------+-------\ /------+-----\ TBSCAN FETCH FILTER FILTER | /----+---\ | | TABLE: S IXSCAN TABLE: T TBSCAN TBSCAN | | | INDEX: T_PK TABFNC: GENROW TABFNC: GENROW Merge Upsert MERGEINTO T USING S ON T.pk = S.pkWHENMATCHEDTHENUPDATESET c1 = S.c1WHENNOTMATCHEDTHENINSERTVALUES(pk, c1);

30. Merge single row Upsert MERGEINTO T USING (VALUES(1, 2)) AS S(pk, c1)ON S.pk = T.pkWHENMATCHEDTHENUPDATESET c1 = S.c1WHENNOTMATCHEDTHENINSERTVALUES (pk, c1) RETURN | INSERT /------+------\ UPDATE TABLE: T /------+-------\ NLJOIN TABLE: T /-------------+-------------\ NLJOIN UNION /-----+-----\ /------+-----\ TBSCAN TBSCAN FILTER FILTER | | | | TABFNC: GENROW TABLE: T TBSCAN TBSCAN | | TABFNC: GENROW TABFNC: GENCROW

31. Merge rules of engagement • Place INSERT branch at the end.Otherwise second temp between INSERT and UPDATE • Aim for NLJOIN over MERGE target in OLTP.Other joins result in exclusive lock on target table.E.g. drop optimization level to 3

32. Calculating nesting levels • ProblemGiven a log table with nested events.How deep is nesting at any given time? • DDLCREATETABLE log(timestamp TIMESTAMP, event CHAR(3), data VARCHAR(10));

33. Calculating nesting levels

34. Calculating nesting levels

35. Calulating nesting levels SELECTREPEAT('|',SUM(CASE eventWHEN 'In' THEN 1WHEN 'Out' THEN -1 END)OVER (ORDERBY timestamp) + CASE event WHEN 'In' THEN -1ELSE 0 END) || CASE event WHEN 'In' THEN '>' WHEN 'Out' THEN '<' ENDAS nesting, timestamp, event FROM Log ORDERBY timestamp;

36. Appendix – V8 feature roadmap • FP2 • MERGE (prefer FP9 for performance optimizations) • ORDER BY and FETCH FIRST in subquery • FP4 (TPC-C) • SELECT FROM UPDATE/DELETE/INSERT • UPDATE/DELETE/INSERT of subquery and order by. • FP7 • CALL in “inline” SQL PL, • “native” SQL Procedures (prefer FP9 for stability) • DROP/SET DEFAULT and identity columns • FP9 • Automatic storage

37. Conclusion • Exploit SQL to: • Increase concurrency • Reduce I/O • Reduce code-path • Make the application more readable • SQL provides powerful support

38. Serge Rielau SQL on Fire! Part 2 IBM srielau@ca.ibm.com