1 / 11

Tuning in Action

Learn how to optimize the insertion of records by leveraging conditional logic. Explore techniques for handling multiple conditions, generating multiple output rows, and updating records based on specific criteria.

dayr
Download Presentation

Tuning in Action

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. 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(id, extra_price, otherfields) • 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 be involved in interior loops.

  4. Example • Suppose that extra_price is a scalar variable in the stored procedure derived from alloc.extra_price • An insert brings information from table R depending on extra_price field of alloc: if (extra_price > 0) then insert T select R.x, R.y, extra_price 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 so one statement can do everything. • Insert Tselect R.x, R.y, alloc.extra_price from alloc, R, S where R.A = S.B and alloc.extra_price = R.extra_priceand R.extra_price > 0

  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.otherfields, alldates.date from alloc, alldates where alloc.startdate <= alldates.date and alloc.enddate >= alldates.date

  8. Example 3 • Each date further gets different price 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 or even per date • Better to have a table of prices S(mydate, price) with seven items and then do a join: update R set price = S.price from R, S where R.this_date = S.mydate

  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?

More Related