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.