Tuning in Action

1. Tuning in Action Dennis Shasha

2. Application Context • Each record, when inserted, undergoes several tests to see where and how it should be inserted. • Programmer temptation is to treat each record separately and in a loop.

3. Starting Point • Input relation consisting of records to be inserted, alloc. • Stored procedure selects rows of alloc into local scalar variables in a loop and then processes each one based on conditions. • The record itself may entail interior loops.

4. Example • Suppose that extra_price is a scalar variable in the stored procedure derived from alloc.extra_price • We have a condition of the form: if (extra_price > 0) then select x, y from R, S where R.A = S.B and R.C = extra_price

5. What Can We Do? • If statement can be translated to where clause, possibly linked to an insert. • select x, y, alloc.extra_price from alloc, R, S where alloc.extra_price = R.extra_price and R.A = S.B

6. Example 2 • An input row translates into many output rows, one for each date between start_date and end_date. • How does one generate several rows from a single one? • One possibility: do it outside the database, but then interfaces change.

7. What Can One Do? • Set up a table that holds only dates alldates. • Then can insert as follows: insert V select alloc.id, alloc.etc, alldates.date from alloc, alldates where alloc.startdate <= alldates.date and alloc.enddate >= alldates.date

8. Example 3 • Each date further gets different values depending on the day of the week. If weekday(this_date) = 0 then price = Sunday_price

9. What Can We Do? • If there is a record for each date, then again the if can be translated to a where update R set price = Sunday_price where weekday(this_date) = 0

10. Several updates • Would need one update per day of week. • Better to have a table of prices S(myweekday, price) with seven items and then do a join: update R set price = S.price from R, S where weekday(R.this_date) = S.myweekday

11. General Structure Issues • For each row of alloc and each date, we call a stored procedure that does several joins and inserts. • What should we attack first?