distal-attribute
distal-attribute
distal-attribute
distal-attribute

Multi statements for faster single INSERTs

User, date Message
Written by ansgar
4 years ago
Category: News
5023 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.