Re: Why are multiqueries soo much faster than batch updates? - Mark Matthews - com.mysql.lists.java

3 messages in com.mysql.lists.javaRe: Why are multiqueries soo much fas...

From	Sent On	Attachments
Kevin A. Burton	08 Dec 2004 21:49
Mark Matthews	09 Dec 2004 06:15
Kevin A. Burton	09 Dec 2004 11:02

Subject:	Re: Why are multiqueries soo much faster than batch updates?
From:	Mark Matthews (ma...@mysql.com)
Date:	12/09/2004 06:15:56 AM
List:	com.mysql.lists.java

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

Kevin A. Burton wrote:

OK...

I'm running MySQL 4.1.7 and Connector-J 3.1.5...

I'm trying to tune bulk SQL generation so that I can remove per-trip latency. Its usually about 1ms per call which I want to remove to all but a few queries. This doesn't sound like much of course but if you have 1000 DB calls thats 1 second....

In our current arch we're using 4.0.18 and 3.0.12 which only supports batch updates (prep stmts) which have been really slow for us. 500 batch updates always takes 500ms.

I just tested using the multiquery option in the JDBC driver with one large UPDATE clause and its MUCH faster.

I ran a simple test with a TEST_PERF table with two rows... one ID with an AUTO_INCREMENT and a VALUE col which I just set to '1';

I performed the test with 200 inserts ... one with multiqueries and one with batch updates:

testing multiquery...done duration: 556 testing batch prep stmts... testing batch prep stmts...done duration: 38312

Yes... thats 38 seconds for using batch updates and .5 second using the new multiquery option... w00t!

All above times are in milliseconds. I was doing this over my home DSL which magnifies the latency. If I do this in our cluster I get similar values just reduced times.

Can batch prepared statements just be updated to use the mechanism that allowMultiQuery uses?

Kevin,

Not easily, it would require a full SQL parser to be implemented in the driver, because the _true_ way you would want to do this, at least for INSERTs is _not_ use multi queries, but use the extended insert syntax, as that will cause 'batching' to happen on index updates as well.

The batching of prepared statement parameters is something that is planned for the server (you'll see there's a placeholder for this in the protocol, it's just not implemented yet). It will probably appear in the 5.1 version. The 'plumbing' is already there in the driver, there's just nothing to receive it on the other end.

iD8DBQFBuF4ytvXNTca6JD8RAhmCAJ92SA9lSMKhW9kFVfG0d4MlSnEtuACfQAMI AvWgNztj9XQt7jmvpdTHy6w= =81tz -----END PGP SIGNATURE-----