Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Multi statements for faster single INSERTs

ansgar's profile image ansgar posted 9 years ago in News Permalink
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.
1 attachment(s):
  • multistatements

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