Re: performance of insert/delete/update

On Fri, 2002-11-22 at 22:18, Josh Berkus wrote:
> Scott,
>
> > > > The absolutely most important thing to do to speed up inserts and
> > > > updates
> > > > is to squeeze as many as you can into one transaction.
>
> I was discussing this on IRC, and nobody could verify this assertion.
> Do you have an example of bunlding multiple writes into a transaction
> giving a performance gain?

Unfortunately, I missed the beginning of this thread, but I do
know that eliminating as many indexes as possible is the answer.
If I'm going to insert "lots" of rows in an off-line situation,
then I'll drop *all* of the indexes, load the data, then re-index.
If deleting "lots", then I'll drop all but the 1 relevant index,
then re-index afterwards.

As for bundling multiple statements into a transaction to increase
performance, I think the questions are:
- how much disk IO does one BEGIN TRANSACTION do? If it *does*
do disk IO, then "bundling" *will* be more efficient, since
less disk IO will be performed.
- are, for example, 500 COMMITs of small amounts of data more or
less efficient than 1 COMMIT of a large chunk of data? On the
proprietary database that I use at work, efficiency goes up,
then levels off at ~100 inserts per transaction.

Ron
--
+------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "they love our milk and honey, but preach about another |
| way of living" |
| Merle Haggard, "The Fighting Side Of Me" |
+------------------------------------------------------------+