0

I'm writing back data of a table in Java to my MYSQl dB. But to execute one executeBatch it takes 1.241 s! Here my code:

updateLieferant = conn.prepareStatement(
    "UPDATE "+dbnames.artikel.name+" SET Abteilung = ? , statusAusmessen = ? , Status = ? " 
    +" WHERE " +dbnames.auftragsnummer +" = ? " +" AND " +dbnames.artikelnummer +" = ?");

updateLieferant.setString( 1, "blabla" );
updateLieferant.setString( 2, "blabla" );
updateLieferant.setString( 3, "blabla" );
updateLieferant.setString( 4, "blabla");
updateLieferant.setString( 5, "blabla" );

long time = System.nanoTime(); 
updateLieferant.executeBatch();
time = System.nanoTime()- time;

System.out.println(time/1000000);

outputs 1241 ms... is there anything I'm doing wrong? From this page I see that it should take around 100ms. http://rostislav-matl.blogspot.ch/2011/08/fast-inserts-to-postgresql-with-jdbc.html

Josh Crozier
  • 233,099
  • 56
  • 391
  • 304
user1511924
  • 53
  • 1
  • 6

1 Answers1

0

executeBatch of for executing a statement a large number of times, as in the BPI section in the link you posted (it's in a loop), but here you are executing it only once.

Instead, try 

updateLieferant.executeUpdate();
NickJ
  • 9,380
  • 9
  • 51
  • 74
  • True, but I am acutally doing this in a loop. With executeUpdate() i get a time of 657 ms which is still 3 times more than what I see in the posted link. – user1511924 Dec 13 '13 at 10:45
  • In that case, you problem is probably not with JDBC but with the database itself. How big is the table you are updating, and do you have indexes on the columns in the WHERE clause? – NickJ Dec 13 '13 at 11:02
  • No I am not using indexes. The table has 316813 rows and 15 columns – user1511924 Dec 13 '13 at 12:11
  • 1
    I suggest adding indexes to the columns used in the WHERE clause. That should speed things up - without them it has to do a sequential scan of all 316813 rows for both columns to check which rows to update. An index scan will be much faster. – NickJ Dec 13 '13 at 14:00
  • Brilliant! That made the job! Now I'm where I wanted to be. Thanks a lot! – user1511924 Dec 19 '13 at 17:38