pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedreally quick multiple inserts can use COPY?
8+ messages / 7 participants
[nested] [flat]
* really quick multiple inserts can use COPY?
@ 2006-12-11 16:19 Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-11 16:53 ` Re: really quick multiple inserts can use COPY? Guillaume Cottenceau <gc@mnc.ch>
2006-12-11 16:57 ` Re: really quick multiple inserts can use COPY? Andreas Kretschmer <akretschmer@spamfence.net>
2006-12-11 17:01 ` Re: really quick multiple inserts can use COPY? Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-11 17:11 ` Re: really quick multiple inserts can use COPY? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 8+ messages in thread
From: Jens Schipkowski @ 2006-12-11 16:19 UTC (permalink / raw)
To: pgsql-performance
Hello!
In our JAVA application we do multiple inserts to a table by data from 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 = "INSERT INTO tblfoo(foo,bar) VALUES("+it.next()+","+CONST.BAR+");";
}
I was searching for an quicker way - MSSQL offers Array Inserts - at
PostgreSQL. The only solution seem to be "INSERT INTO foo SELECT" and this
is really dirty.
I improved the inserts using the subselect with union.
sql = "INSERT INTO tblfoo(foo,bar) ";
for(){
sql += "SELECT "+it.next()+","+CONST.BAR+" UNION " ...
}
This results in a really long INSERT INTO SELECT UNION statement and works
correct and quick but looks dirty.
When I heard about COPY I thought this will be the right way. But it does
not work using JDBC. Testing via psql does it perfect but sending the same
SQL statements via JDBC throws an error.
-> BEGIN
sql = "COPY tblfoo(foo,bar) FROM STDIN;\n1 'foobar'\n2 'foobar'\n\\.";
-> COMMIT
ERROR: syntax error at or near "1" at character 34
So, my questions:
Is it possible to use COPY FROM STDIN with JDBC?
Will it bring performance improvement compared to SELECT UNION solution?
many thanks in advance,
Jens Schipkowski
--
**
APUS Software GmbH
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
@ 2006-12-11 16:53 ` Guillaume Cottenceau <gc@mnc.ch>
2006-12-12 07:45 ` Re: really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
3 siblings, 1 reply; 8+ messages in thread
From: Guillaume Cottenceau @ 2006-12-11 16:53 UTC (permalink / raw)
To: Jens Schipkowski <jens.schipkowski@apus.co.at>; +Cc: pgsql-performance
"Jens Schipkowski" <jens.schipkowski 'at' apus.co.at> writes:
> Hello!
>
> In our JAVA application we do multiple inserts to a table by data from
> 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 = "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.
--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-11 16:53 ` Re: really quick multiple inserts can use COPY? Guillaume Cottenceau <gc@mnc.ch>
@ 2006-12-12 07:45 ` Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-12 08:12 ` Re: really quick multiple inserts can use COPY? nicky <nicky@valuecare.nl>
0 siblings, 1 reply; 8+ messages in thread
From: Jens Schipkowski @ 2006-12-12 07:45 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: pgsql-performance
Thanks a lot to all for your tips.
Of course, I am doing all the INSERTs using a transaction. So the cost per
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 using
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> wrote:
> "Jens Schipkowski" <jens.schipkowski 'at' apus.co.at> writes:
>
>> Hello!
>>
>> In our JAVA application we do multiple inserts to a table by data from
>> 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 = "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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-11 16:53 ` Re: really quick multiple inserts can use COPY? Guillaume Cottenceau <gc@mnc.ch>
2006-12-12 07:45 ` Re: really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
@ 2006-12-12 08:12 ` nicky <nicky@valuecare.nl>
0 siblings, 0 replies; 8+ messages in thread
From: nicky @ 2006-12-12 08:12 UTC (permalink / raw)
To: Jens Schipkowski <jens.schipkowski@apus.co.at>; +Cc: pgsql-performance
Jens Schipkowski wrote:
> Thanks a lot to all for your tips.
>
> Of course, I am doing all the INSERTs using a transaction. So the cost
> per 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
> using 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>
> wrote:
>
>> "Jens Schipkowski" <jens.schipkowski 'at' apus.co.at> writes:
>>
>>> Hello!
>>>
>>> In our JAVA application we do multiple inserts to a table by data from
>>> 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 = "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>
>
This link might be what you are looking for, it has some information
about implementing COPY in the JDBC driver. Check the reply message as
well.
http://archives.postgresql.org/pgsql-jdbc/2005-04/msg00134.php
Another solution might be to have Java dump the contents of the HashMap
to a CVS file and have it load through psql with COPY commands.
Good luck,
Nick
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
@ 2006-12-11 16:57 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-12-11 19:22 ` Re: really quick multiple inserts can use COPY? imad <immaad@gmail.com>
3 siblings, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2006-12-11 16:57 UTC (permalink / raw)
To: pgsql-performance
Jens Schipkowski <jens.schipkowski@apus.co.at> schrieb:
> Hello!
>
> In our JAVA application we do multiple inserts to a table by data from 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.
Can you change this from INSERT-Statements to COPY? Copy is *much*
faster than INSERT.
If no, do you using all INSERTs in one transaction? I believe, a 'BEGIN'
and a 'COMMIT' around all INSERTs may increase the speed.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-11 16:57 ` Re: really quick multiple inserts can use COPY? Andreas Kretschmer <akretschmer@spamfence.net>
@ 2006-12-11 19:22 ` imad <immaad@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: imad @ 2006-12-11 19:22 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
On 12/11/06, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Jens Schipkowski <jens.schipkowski@apus.co.at> schrieb:
>
> > Hello!
> >
> > In our JAVA application we do multiple inserts to a table by data from 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.
>
> Can you change this from INSERT-Statements to COPY? Copy is *much*
> faster than INSERT.
>
> If no, do you using all INSERTs in one transaction? I believe, a 'BEGIN'
> and a 'COMMIT' around all INSERTs may increase the speed.
Performance increment can also be gained by disabling constraints in the
transaction. These disabled constraints are invoked at the end of the
transaction according to the SQL standard, so no worries about data
consistency.
Hmmm... PG currently supports disabling foreign constraints only. But that
can still be significant.
--Imad
www.EnterpriseDB.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
@ 2006-12-11 17:01 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
3 siblings, 0 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-11 17:01 UTC (permalink / raw)
To: Jens Schipkowski <jens.schipkowski@apus.co.at>; +Cc: pgsql-performance
> So, my questions:
> Is it possible to use COPY FROM STDIN with JDBC?
Should be. Its at least possible using DBI and DBD::Pg (perl)
my $copy_sth = $dbh -> prepare( "COPY
general.datamining_mailing_lists (query_id,email_key) FROM STDIN;") ;
$copy_sth -> execute();
while (my ($email_key ) = $fetch_sth -> fetchrow_array ()) {
$dbh -> func("$query_id\t$email_key\n", 'putline');
}
$fetch_sth -> finish();
$dbh -> func("\\.\n", 'putline');
$dbh -> func('endcopy');
$copy_sth->finish();
Some JDBC expert would tell better how its done with JDBC.
> Will it bring performance improvement compared to SELECT UNION solution?
COPY is quite faast.
Regds
mallah.
>
> many thanks in advance,
> Jens Schipkowski
>
> --
> **
> APUS Software GmbH
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: really quick multiple inserts can use COPY?
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
@ 2006-12-11 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-12-11 17:11 UTC (permalink / raw)
To: Jens Schipkowski <jens.schipkowski@apus.co.at>; +Cc: pgsql-performance
"Jens Schipkowski" <jens.schipkowski@apus.co.at> writes:
> Is it possible to use COPY FROM STDIN with JDBC?
You should be asking the pgsql-jdbc list, not here. (I know I've seen
mention of a JDBC patch to support COPY, but I dunno if it's made it into
any official version.)
> Will it bring performance improvement compared to SELECT UNION solution?
Please, at least be smart enough to use UNION ALL not UNION.
If you're using 8.2 you could also consider using INSERT with multiple
VALUES-lists.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-12-12 08:12 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-11 16:19 really quick multiple inserts can use COPY? Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-11 16:53 ` Guillaume Cottenceau <gc@mnc.ch>
2006-12-12 07:45 ` Jens Schipkowski <jens.schipkowski@apus.co.at>
2006-12-12 08:12 ` nicky <nicky@valuecare.nl>
2006-12-11 16:57 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-12-11 19:22 ` imad <immaad@gmail.com>
2006-12-11 17:01 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-11 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
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