Hello!
In our JAVA application we do multiple inserts to a table by data from a
Hash Map. Due to poor database access implemention - done by another
company (we got the job to enhance the software) - we cannot use prepared
statements. (We are not allowed to change code at database access!)
First, we tried to fire one INSERT statement per record to insert. This
costs 3 ms per row which is to slow because normally we insert 10.000
records which results in 30.000 ms just for inserts.
for(){
sql = "INSERT INTO tblfoo(foo,bar) VALUES("+it.next()+","+CONST.BAR+");";
}
I was searching for an quicker way - MSSQL offers Array Inserts - at
PostgreSQL. The only solution seem to be "INSERT INTO foo SELECT" and this
is really dirty.
I improved the inserts using the subselect with union.
sql = "INSERT INTO tblfoo(foo,bar) ";
for(){
sql += "SELECT "+it.next()+","+CONST.BAR+" UNION " ...
}
This results in a really long INSERT INTO SELECT UNION statement and works
correct and quick but looks dirty.
When I heard about COPY I thought this will be the right way. But it does
not work using JDBC. Testing via psql does it perfect but sending the same
SQL statements via JDBC throws an error.
-> BEGIN
sql = "COPY tblfoo(foo,bar) FROM STDIN;\n1 'foobar'\n2 'foobar'\n\\.";
-> COMMIT
ERROR: syntax error at or near "1" at character 34
So, my questions:
Is it possible to use COPY FROM STDIN with JDBC?
Will it bring performance improvement compared to SELECT UNION solution?
many thanks in advance,
Jens Schipkowski
--
**
APUS Software GmbH
