Re: optimizing many inserts with JDBC - Will Glass-Husain - com.mysql.lists.java

11 messages in com.mysql.lists.javaRe: optimizing many inserts with JDBC

From	Sent On	Attachments
Will Glass-Husain	25 Aug 2003 16:10
col...@dreamwerx.net	25 Aug 2003 16:29
col...@dreamwerx.net	25 Aug 2003 16:34
Shankar Unni	25 Aug 2003 16:43
col...@dreamwerx.net	25 Aug 2003 16:51
Mark Matthews	25 Aug 2003 16:59
Mark Matthews	25 Aug 2003 17:00
Will Glass-Husain	25 Aug 2003 17:31
Mark Matthews	25 Aug 2003 17:37
Paul DuBois	29 Aug 2003 08:05
Shankar Unni	29 Aug 2003 11:10

Subject:	Re: optimizing many inserts with JDBC
From:	Will Glass-Husain (wgl...@forio.com)
Date:	08/25/2003 05:31:00 PM
List:	com.mysql.lists.java

Interesting comments, thanks. Two quick questions

INSERT INTO foo VALUES (...), (...), (...) and on and on.

-- in reference to the above, is there any disadvantage to using a PreparedStatement to generate an INSERT statement that inserts say, 100 records at a time?

-- how should I determine a reasonable max_allowed_packet? could I naively assume that this is roughly the number of characters in a SQL query?

Best, WILL

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Shankar Unni wrote:

Colbey wrote:

You should be using a prepared statement if not already.. create it once, update, execute, loop...

And even better, use PreparedStatement.addBatch() and PreparedStatement.executeBatch() to send several rows of data together when doing inserts. You can use a reasonable batch size (say 100 rows at a time, but you can use less or more depending on your row size and performance measurements).

Statement/PreparedStatement.addBatch() has no optimization in it, as MySQL (until 4.1) didn't accept multiple queries in one network packet, and even in 4.1, prepared statements don't accept multiple parameters per network packet. This will change in some future version of MySQL, most likely 5.0.

If you want to insert as fast as possible, and can't leave the data as a file for "LOAD DATA INFILE" type commands, your best bet is to use the multiple-value insert form of "INSERT INTO", and make the list of values as long as possible (up to the size of max_allowed_packet), i.e.:

INSERT INTO foo VALUES (...), (...), (...) and on and on.

You should find that you get _very_ high rates of insertion, especially if you set max_allowed_packet pretty high and can get lots of rows into a single query.

-Mark

-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQE/SqM9tvXNTca6JD8RAhicAJ0YS4oZlWdrwwd3B/kQ7yhBVQ8uRQCeOZJ4 n3lbqJQ13OI8UOcxReyFRds= =wpSP -----END PGP SIGNATURE-----