Jun 212011
 

I recently had a requirement to insert a few hudred rows into a relational database ever couple of seconds. Generally this could be accomplished during the request, but I didn’t want to introduce issues if there were spikes. Therefore, I figured I would cache the data and then write it out in a background thread every few seconds. This would also increase my response time during requests.

I wasn’t sure whether or not JDBC batch or using a single insert statement that inserted multiple rows would be faster. Therefore, I setup a little test to see which was going to work better. First some code:

Here is the code for a single insert statement that inserts multiple rows.

Here is the code for the JDBC batch:

The average time over 10 iterations where roughly as follows:

JDBC Batch: ~100 milliseconds
Single Insert: ~10 milliseconds

It looks like the single insert statement wins by a large margin.

UPDATE: This method yields very similar results on both MySQL and PostgreSQL using the latest drivers from each organization.