From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Frank Morton <fmorton@base2inc.com>
To: pgsql-sql@hub.org
Subject: Re: [SQL] Slow Inserts Again
Date: Mon, 3 May 1999 16:50:09 +0300
Message-ID: <l03130305b35358ca72f2@[147.233.159.109]> (raw)
In-Reply-To: <06b501be9567$e5941590$8355e5ce@base2inc.com>
At 16:21 +0300 on 03/05/1999, Frank Morton wrote:
> I have tried many combinations of things to speed this
> up as you all have suggested. I have had no success
> using "copy" at all because of problems with quotes
> and other punctuation in the data.
I must tell you, this doesn't sound reasonable to me. It's usually very
easy, if you already have a program that writes down the fields, to make
sure it scans the contents thereof and adds a backslash before each tab,
newline and backslash in every one of the fields.
If what you need is to dump-reload, then this is exactly what the dump
program will do for you.
> This last attempt, I bracket each insert statement with
> "begin;" and "end;".
Not necessary. An insert has an implicit begin-end around it. If you want
to change that behaviour, group several inserts together in begin-end.
Putting them individually is redundant.
> What I am seeing this time around is in the beginning,
> the inserts were reasonable in speed. Say 6 or 7
> per second. But now that it is up to record 100,000 or
> so (3 DAYS later) the time between inserts is about
> 10 SECONDS. As progress is made, the inserts
> continue to get slower and slower. So at the current
> rate, I have another 138 hours before completion!
Perhaps you need to break the connection. That is, every 1000 records,
disconnect from the backend, and connect again. A vacuum every so often may
also help somewhat.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, fmorton@base2inc.com, pgsql-sql@hub.org
Subject: Re: [SQL] Slow Inserts Again
In-Reply-To: <l03130305b35358ca72f2@[147.233.159.109]>
References: <06b501be9567$e5941590$8355e5ce@base2inc.com> <l03130305b35358ca72f2@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox