1 / 11

Database Tuning in Action: Maximizing Efficiency for Record Insertion

Learn strategies to optimize record insertion efficiency by leveraging stored procedures, condition-based processing, and table organization.

jun
Télécharger la présentation

Database Tuning in Action: Maximizing Efficiency for Record Insertion

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. • 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 • 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 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.etc, 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