Efficient Multiple SQL insertion

Question

What is the best/most time efficient way to insert 1000 rows into one table (jdbc/connector-mysql database)? (it is a buffer and need to be dumped into the database everytime it is full)

1- One auto generated/concanated SQL statement?

2- for (int i = 0; i<1000; i++) { con.prepareStatement(s.get(i)); } con.commit();

3- stored procedure ?

4- bulk data insertion via a file?

5- (your solution)

Answer 1

The LOAD DATA INFILE statement is probably your best bet for performance. (#4 from your list of options above) Though it will probably take more code to accomplish your task since you need to create the intermediate file, get it to the server and then call LOAD DATA to get it into your db.

MySql Help pages quote:

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.