From: | Kris Jurka <books(at)ejurka(dot)com> |
---|---|
To: | Andreas Koch <a_koch(at)moellinger(dot)hx(dot)uni-paderborn(dot)de> |
Cc: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: Populating a database with huge amounts of data |
Date: | 2003-07-01 23:13:55 |
Message-ID: | Pine.LNX.4.33.0307011910300.32766-100000@leary.csoft.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
On Tue, 1 Jul 2003, Andreas Koch wrote:
> Hi,
>
> I have to populate a database with a huge amount of data (over a
> Network). While reading the Data from the Database using a single SELECT
> statement takes only a few seconds, writing the Data with a series of
> INSERT statements take over 10 Minutes. I think this is because the
> INSERT statement plus the Data have to be send as ASCII over the network
> and need much more bytes then sending only the data in binary form. 10
> Minutes are by far too mutch time for my aplication to wait for the
> transaction so I was looking for a faster way to insert the data into my
> database. My first step was to disable Autocommit by using BEGIN at the
> start and COMMIT at the End of sending the data via INSERT INTO. This
> speeds up the process a little bit, but not enough. Now I'm trying to
> use COPY FROM STDIN, but don't get it right. First I tryed :
>
> Statement.execute("COPY table FROM STDIN");
> String Data = new String("");
> for(int i=0; ....) {
> Data = new String(Data+integer1[i]+"\t"+integer2[i]+"\n");
> }
> Data = new String (Data+"\\.\n");
> Statement.execute(Data);
>
> This don't work because Statement.execute(Data) inserts a 'Q' at the
> beginning of the Data String so that the first entry looks like Q7
> instead of 7 and the copy statement returns an error because it expects
> an iterger, not a string. So I tryed to put everything in one String :
>
> String Data = new String("COPY table FROM STDIN; ");
> for(int i=0; ....) {
> Data = new String(Data+integer1[i]+"\t"+integer2[i]+"\n");
> }
> Data = new String (Data+"\\.\n");
> Statement.execute(Data);
>
> which also comes up with an error (parser error at or near
> "[interger1[0]]").
>
> I don't know if COPY FROM STDIN is the right way to speed up the
> population of the database or why it don't work the way I tryed it. I
> would be very happy for every help I can get.
>
Mike Adler produces a patch to allow copy in the JDBC driver a while ago.
See http://archives.postgresql.org/pgsql-jdbc/2003-02/msg00345.php
Now that the 7.4dev series allows recovery from a copy error I'm more in
favor of this patch.
Kris Jurka
From | Date | Subject | |
---|---|---|---|
Next Message | Barry Lind | 2003-07-02 01:27:50 | Re: New patches |
Previous Message | Kris Jurka | 2003-07-01 23:08:46 | Re: db |