Date: Tue, 12 Dec 2006 08:45:49 +0100
To: "Guillaume Cottenceau" <gc@mnc.ch>
Subject: Re: really quick multiple inserts can use COPY?
From: "Jens Schipkowski" <jens.schipkowski@apus.co.at>
Organization: APUS Software GmbH
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <op.tkekypo381rjf6@xjens.apus.local> <87vekiqspr.fsf@meuh.mnc.lan>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.tkfrunwy81rjf6@xjens.apus.local>
In-Reply-To: <87vekiqspr.fsf@meuh.mnc.lan>
User-Agent: Opera Mail/9.02 (Linux)

Thanks a lot to all for your tips.

Of course, I am doing all the INSERTs using a transaction. So the cost p=
er  =

INSERT dropped from 30 ms to 3 ms.
The improvement factor matches with the hint by Brian Hurt.
Sorry, I forgot to mention we are using PostgreSQL 8.1.4.
Thanks for the code snippet posted by mallah. It looks like you are usin=
g  =

prepared statements, which are not available to us.
But I will check our database access if its possible to do a workaround,=
  =

because this looks clean and quick to me.

regards
Jens Schipkowski


On Mon, 11 Dec 2006 17:53:52 +0100, Guillaume Cottenceau <gc@mnc.ch> wro=
te:

> "Jens Schipkowski" <jens.schipkowski 'at' apus.co.at> writes:
>
>> Hello!
>>
>> In our JAVA application we do multiple inserts to a table by data fro=
m
>> a  Hash Map. Due to poor database access implemention - done by
>> another  company (we got the job to enhance the software) - we cannot=

>> use prepared  statements. (We are not allowed to change code at
>> database access!)
>> First, we tried to fire one INSERT statement per record to insert.
>> This  costs 3 ms per row which is to slow because normally we insert
>> 10.000  records which results in 30.000 ms just for inserts.
>>
>> for(){
>> sql =3D "INSERT INTO tblfoo(foo,bar)  =

>> VALUES("+it.next()+","+CONST.BAR+");";
>> }
>
> You should try to wrap that into a single transaction. PostgreSQL
> waits for I/O write completion for each INSERT as it's
> implicitely in its own transaction. Maybe the added performance
> would be satisfactory for you.
>


-- =

**
APUS Software GmbH