really quick multiple inserts can use COPY?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux