Multi statements for faster single INSERTs

User, date	Message
Written by ansgar 2 years ago Category: News 3951 posts since Fri, 07 Apr 06	When you have an .sql with a huge number of single INSERT statements, such as this: INSERT INTO mytable (id, name, ...) VALUES (1, 'foo', ...); INSERT INTO mytable (id, name, ...) VALUES (2, 'bar', ...); INSERT INTO mytable (id, name, ...) VALUES (3, 'xyz', ...); ... Such dumps take quite a long time to import normally, as there is a significant overhead per query, multiplied many times for large dumps. HeidiSQL now can package such statements into one larger multi statement query and send this at once the server. As I was testing 4000 INSERTs on a local, simple table I experienced a huge performance improvement: - single queries: ~40 seconds - one packaged multi stament: <1 second. HeidiSQL automatically calculates a package size of some kb below your max_allowed_packet size. Which is in some cases not small enough as I noticed connection cut offs as I sent just 200kb in one query. This will probably need some more tweaking. Apart from that, feel free to check that out: Thanks to david71rj for giving me the hint on multi statements.
	Please login to leave a reply, or register at first. Username: Password: Remember me

User, date

Message

Written by ansgar
2 years ago
Category: News
3951 posts since Fri, 07 Apr 06

When you have an .sql with a huge number of single INSERT statements, such as this:

INSERT INTO mytable (id, name, ...) VALUES (1, 'foo', ...);
INSERT INTO mytable (id, name, ...) VALUES (2, 'bar', ...);
INSERT INTO mytable (id, name, ...) VALUES (3, 'xyz', ...);
...

Such dumps take quite a long time to import normally, as there is a significant overhead per query, multiplied many times for large dumps. HeidiSQL now can package such statements into one larger multi statement query and send this at once the server. As I was testing 4000 INSERTs on a local, simple table I experienced a huge performance improvement:
- single queries: ~40 seconds
- one packaged multi stament: <1 second.

HeidiSQL automatically calculates a package size of some kb below your max_allowed_packet size. Which is in some cases not small enough as I noticed connection cut offs as I sent just 200kb in one query. This will probably need some more tweaking. Apart from that, feel free to check that out:

Thanks to david71rj for giving me the hint on multi statements.

Please login to leave a reply, or register at first.