Обсуждение: Inserting a large number of records
What is the most efficient way to insert a large number of records into a table? Greg Alton ActivSoftware http://www.activsoftware.com
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
Oliver Jowett wrote: > 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. 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.
It will never be as fast as COPY Dave On 14-Jul-05, at 11:41 AM, Steve Wampler wrote: > Oliver Jowett wrote: > >> 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. >> > > 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 > >
Steve Wampler wrote: > Oliver Jowett wrote: > >>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. > > > 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
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: > >> 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. >> > > 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 > >