From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Vaibhav Patil <infovaibhav(at)yahoo(dot)com> |
Cc: | "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: COPY using Hibernate |
Date: | 2010-01-15 16:31:39 |
Message-ID: | 4B50986B.4090806@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Vaibhav Patil wrote:
> The other way I am trying is the pg/plsql stored procedure. As per the
> requirement, I have to insert around a million integers in a table
> having just one column. I am not aware of how to use "COPY FROM STDIN"
> using pg/plsql. I'll pass these integers as array to stored procedure. I
> want to avoid dependency of file, otherwise it could have been done
> easily using "COPY FROM file". Looking for more information on internet
> to solve the issue. If anybody aware of the way, please help me.
It's a real pity the JDBC driver doesn't support the COPY protocol so
you could just do this via PGConnection. There have been patches around
for ages ... though I presume there are good reasons why they haven't
been merged.
Do check to make sure that the stored proc approach is actually faster
than just doing batched multi-valued INSERTs. Open a transaction, then
prepare a statement like this:
INSERT INTO sometable VALUES (?),(?),(?),(?),(?);
(but with say 20 parameters - you'll need to play around and see how
many is optimal), then add a series of invocations of the prepared
statement to a JDBC batch insert/update and execute it.
I wouldn't be surprised if that was quite a bit faster than doing it
with an array and PL/PgSQL.
--
Craig Ringer
From | Date | Subject | |
---|---|---|---|
Next Message | Maciek Sakrejda | 2010-01-15 16:42:28 | Re: COPY using Hibernate |
Previous Message | Craig Ringer | 2010-01-15 16:21:09 | Re: COPY using Hibernate |