|
Home > Archive > PostgreSQL JDBC > July 2005 > Inserting a large number of records
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Inserting a large number of records
|
|
| Greg Alton 2005-07-14, 9:23 am |
| What is the most efficient way to insert a large number of records into
a table?
Greg Alton
ActivSoftware
http://www.activsoftware.com
---------------------------(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
| |
| Oliver Jowett 2005-07-14, 11:24 am |
| Greg Alton wrote:
> What is the most efficient way to insert a large number of records into
> a table?
I use a PreparedStatement INSERT and addBatch() / executeBatch() with
autocommit off and no constraints or indexes present.
-O
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Steve Wampler 2005-07-14, 11:24 am |
| Oliver Jowett wrote:
> Greg Alton wrote:
>
>
>
> I use a PreparedStatement INSERT and addBatch() / executeBatch() with
> autocommit off and no constraints or indexes present.
Does anyone have an idea as to how the performance of this would compare
to using a COPY? I've used the COPY patches with jdbc and 7.4.x with
impressive results, but if the above is 'nearly' as good then I don't have
to put off upgrading to 8.x while waiting on jdbc to officially include
support for COPY. (I can't test the above right now. Maybe soon, though.)
Thanks!
Steve
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
---------------------------(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
| |
| Dave Cramer 2005-07-14, 1:23 pm |
| It will never be as fast as COPY
Dave
On 14-Jul-05, at 11:41 AM, Steve Wampler wrote:
> Oliver Jowett wrote:
>
>
> Does anyone have an idea as to how the performance of this would
> compare
> to using a COPY? I've used the COPY patches with jdbc and 7.4.x with
> impressive results, but if the above is 'nearly' as good then I
> don't have
> to put off upgrading to 8.x while waiting on jdbc to officially
> include
> support for COPY. (I can't test the above right now. Maybe soon,
> though.)
>
> Thanks!
> Steve
> --
> Steve Wampler -- swampler@noao.edu
> The gods that smiled on your birth are now laughing out loud.
>
> ---------------------------(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
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Oliver Jowett 2005-07-15, 3:24 am |
| Steve Wampler wrote:
> Oliver Jowett wrote:
>
>
>
> Does anyone have an idea as to how the performance of this would compare
> to using a COPY? I've used the COPY patches with jdbc and 7.4.x with
> impressive results, but if the above is 'nearly' as good then I don't have
> to put off upgrading to 8.x while waiting on jdbc to officially include
> support for COPY. (I can't test the above right now. Maybe soon, though.)
I have one dataset that is about 20 million rows and takes about 40
minutes to import via batched INSERTs including translation from the
original format (I'd guess perhaps 10-15% overhead). The same dataset
dumped by pg_dump in COPY format takes about 15 minutes to restore
(using psql not JDBC though)
-O
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Dave Cramer 2005-07-15, 7:23 am |
| Here's a version ( a little old ) of the driver with copy implemented.
http://download.postgresintl.com/copy.tar.gz
Dave
On 14-Jul-05, at 11:41 AM, Steve Wampler wrote:
> Oliver Jowett wrote:
>
>
> Does anyone have an idea as to how the performance of this would
> compare
> to using a COPY? I've used the COPY patches with jdbc and 7.4.x with
> impressive results, but if the above is 'nearly' as good then I
> don't have
> to put off upgrading to 8.x while waiting on jdbc to officially
> include
> support for COPY. (I can't test the above right now. Maybe soon,
> though.)
>
> Thanks!
> Steve
> --
> Steve Wampler -- swampler@noao.edu
> The gods that smiled on your birth are now laughing out loud.
>
> ---------------------------(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
>
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
|
|
|
|