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.
Yours,
Andreas
