Improve mysql JDBC insert call

Question

i have a legacy Java system that every time it gets an order it makes a JDBC call to a stored procedure for each field in the order. Generally the stored procedure will get called 20 to 30 times for each order. The store procedure is just doing an insert into a table for each field.

i need to improve the performance of this operation. one thought i had was to create an insert query string that does multiple inserts in one JDBC call. MySql supports a multiple insert string.

INSERT INTO PersonAge (name, age)
VALUES  ('Helen', 24),
        ('Katrina', 21),
        ('Samia', 22),
        ('Hui Ling', 25),
        ('Yumie', 29)

This has the advantage of only requiring one JDBC call per order. Any other ideas on how to improve performance?

Answer 1

Create a prepared statement, or a batch update (in Java, it would be PreparedStatement, or Statement.executeBatch()). The prepared statement will probably be faster, because you only have to submit the values to the database, not the entire query. Try each of them, and run a benchmark.