sql on fire part 2 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL on Fire! Part 2 PowerPoint Presentation
Download Presentation
SQL on Fire! Part 2

Loading in 2 Seconds...

play fullscreen
1 / 38

SQL on Fire! Part 2 - PowerPoint PPT Presentation


  • 420 Views
  • Uploaded on

SQL on Fire! Part 2. Tips and Tricks around SQL. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

SQL on Fire! Part 2


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql on fire part 2

SQL on Fire! Part 2

Tips and Tricks around SQL

slide2

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

slide3

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.
mass deleting of rows
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!
mass deleting of rows5
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’)

slide6

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
slide7

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
slide8

New order processing – Optimal plan

Access Plan:

-----------

RETURN

( 1)

|

1

INSERT

( 4)

1

/---+---\

1 180

TBSCAN TABLE: ORDERS

( 5)

|

1

TABFNC: GENROW

slide9

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)
slide10

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
slide11

Destructive Read – Optimal plan

Access Plan:

-----------

RETURN

( 1)

|

DELETE

( 4)

/----+---\

IXSCAN TABLE: ORDERS

( 5)

|

INDEX: i1

queue processing 2 phase
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).

queue processing claim order
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;

queue processing claim order15
Queue processing – claim order

Access Plan:

------------

RETURN

( 1)

|

TBSCAN

( 2)

|

SORT

( 3)

|

UPDATE

( 4)

/---+---\

IXSCAN TABLE: ORDERS

( 5)

|

INDEX: ORDERIND

moving duplicate rows
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;

move duplicate rows
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)

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

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;

slide22

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)

slide23

Rec-Plan

RETURN

( 1)

|

TBSCAN

( 2)

|

TEMP

( 3)

|

UNION

( 4)

/----+---\

TBSCAN TBSCAN

( 5) ( 6)

| |

TEMP TABFNC: GENROW

( 3)

slide24

Merge

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

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
slide26

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;

merge update from
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

slide28

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;

merge upsert

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);

merge single row upsert
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

merge rules of engagement
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
calculating nesting levels
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));
calulating nesting levels
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;

appendix v8 feature roadmap
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
slide37

Conclusion

  • Exploit SQL to:
    • Increase concurrency
    • Reduce I/O
    • Reduce code-path
    • Make the application more readable
  • SQL provides powerful support
serge rielau
Serge Rielau

SQL on Fire! Part 2

IBM

srielau@ca.ibm.com