mysql-jdbc insert optimisation
Today, at work, we faced insert and delete problems in our mysql database.
usually we have bulks of inserts/updates/deletes from a backend engine, and selects from the website.
the inserts/updates/deletes bulks are usually around 100-500, but at peak times it goes up to 40 000.
the application that does it is a java application and it does it with PreparedStatement and batch.
When we first did this, we thought it would be enough, and no further optimisation could occur.
To my surprise, there is more to it. instead of 5000 "insert into table values (?,?,?,?,?)", this proves to be 5 times faster, in our case:
"insert into table values(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),….5000 times..,(?,?,?,?,?)".
there is some time wasted in building the string, but it is much better this way, as the load on our servers is on the mysql engine, the rest of the applications are low.
you can find the source code of a java class that tests the difference between the 2 approaches, so you can test it at home.
the times are: ~ 1235 ms for the thousand of inserts in batch and 211 ms for the one big insert with 5000 values() pairs.
the stringbuilding of the sql takes ~ 300 ms which is good for our purposes.
1. note: for deleting, always use delete from table where id IN (?,?,?…..?)
2. for the source code file, i did copy from eclipse and paste in vim on the web-server. the code got autoindenting and from 1 comment it commented all the way down. here is the trick that saves you: :set paste before pasting and :set nopaste after.