Re: Speed improvement - Group batch Insert - Rewrite the INSERT at the driver level (using a parameter)

J. W. Ulbts wrote:

> The normal batch would execute this:
> --------------------
> INSERT INTO sometable (col1, col2) VALUES (1, 'word1');
> INSERT INTO sometable (col1, col2) VALUES (2, 'word2');
> INSERT INTO sometable (col1, col2) VALUES (3, 'word3');
> INSERT INTO sometable (col1, col2) VALUES (4, 'word4');
> --------------------
>
> If the batch insert is rewritten by the driver it would generate and execute this:
> --------------------
> INSERT INTO sometable (col1, col2) VALUES (1, 'word1'), (2, 'word2'), (3, 'word3'), (4, 'word4');
> --------------------

That would require teaching the driver how to parse INSERT statements,
and in general the driver tries to avoid parsing SQL where ever possible.

Where exactly is the performance benefit that you see coming from? I
would expect the driver to already be reusing a named statement when
batching a PreparedStatement (you could check the loglevel=2 output to
verify this), so you only pay the parsing cost a few times until use of
a named statement kicks in. Is the overhead of Bind/Execute really that
high?

If your use case is just "I want to do bulk inserts as fast as possible"
then perhaps the newly merged COPY suport is a better way to go.

-O

PS: my reading of the mysql blog entry that you linked was that it was
the parsing/rewriting that the MySQL driver does that caused a 10x
slowdown in the first place, and the "speedup" was actually just fixing
that.