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.l.johnson@cox.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"                |
+------------------------------------------------------------+