Re: How to make the each looping concurrent thread to improve WHILE-loop
performance?
Daniel Pitts wrote:
Basically, the concept goes like this:
1. Start batch
2. Insert a bunch of rows
3. commit batch
4. --- All of the inserts get sent to the DB in one go.
Pros:
- good use of connection and potentially of PreparedStatement to augment
performance.
- the only way to maintain consistency across related modifications.
- one part of the transaction fails, the whole thing rolls back, if you're
vigilant.
Cons:
- one part of the transaction fails, the whole thing rolls back, unless you're
vigilant.
- ties up a thread until it's all over.
- ties up db resources (e.g., the connection) until it's all over.
This has the downside that you can't rely on side-effects of the
inserts until after commit. Specifically, you can't get the auto-
generated primary key for each insert.
The use of auto-generated items as keys is controversial, and at best fraught
with peril. Thiw downside would not exist if one used real keys, i.e., columns
that correspond to attributes of the model. Auto-generated values require
special handling for data loads and unloads. Auto-generated values need to be
kept hidden from the model domain.
There are apologists for the route of using only auto-generated values as
keys. They feel the cited difficulties to be worth the effort.
There are those in the latter group who go beyond any justifiable use of
auto-generated key values to assign single-column keys to multi-column-key
(relationship) tables, those whose composite keys comprise only a
concatenation of foreign-key references.
I used to use auto-generated keys all over the place. (Not in composite-key
tables, however.) Now I'm in the natural-key (a.k.a., "real-key") camp.
- Lew