To insert many records in one time it’s better to use extended INSERT:
INSERT INTO table_name (field1, field2, ..., fieldN) VALUES(value11, value12, ..., value1N), (value21, value22, ..., value2N), ... , (valueN1, valueN2, ..., valueNN);
There is an one default restiction in MYSQL. It doesn’t allow execute query with more than 1 MB.
The speed of this kind of insert is amazing! For example, extended insert of 30000 records took 5.252 sec. Inserting record by record as usual took 29.681 sec. That’s why if you need to insert a huge set of records the extended INSERT is the best choice!
Are you actually constructing the long insert statement on client and then sending it to server? What a nasty piece of code that would be. Decent databases use server-side cursor for this purpose.
No, I construct the long query on the server-side. Advantage is a speed. Maybe it’s not an approach for J2EE. It doesn’t use direct queries 😉
I don’t think that’s the good idea to use such syntax instead of one which is more close to standard by a reason of portability of application.
Example (to be close to your case):
INSERT INTO table_name (field1, field2, …, fieldN)
SELECT value21, value22, …, value2N UNION ALL
…
SELECT value21, value22, …, value2N
But the one disadvantage of extended insert and the one provided above is that you cannot guaranty that you do not have duplicates inside the set of values. But if you have and there is any unique index used, the whole statement’s gonna fail after spending time on compilation of it.
To solves this just change UNION ALL to UNION you can solve this problem. It’s slower because it will make additional work to make a distinct set of rows before insertion.
By the way how about concurrent access to the database. I am sure that you are locking the whole table for 5 seconds.