1 / 11

Efficient Record Insertion Strategies in Database Applications

Learn effective techniques to optimize record insertion processes, exploit conditional statements, and manage data transformations efficiently in database applications.

clive
Télécharger la présentation

Efficient Record Insertion Strategies in Database Applications

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 • We have an insert applying to each row of alloc of the form: if (extra_price > 0) then insert T select R.x, R.y from R, S where R.A = S.B and R.extra_price = 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(dayofweek, price) with seven items and then do a join: update R set price = S.price from R, S where dayofweek(R.this_date) = S.dayofweek

  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