Import Performance When Viewing SQL

User, date	Message
Written by Antnee 2 years ago Category: Import/Export 9 posts since Thu, 21 Jul 11	When I load an SQL file for import and view it, it is always significantly slower to perform the import than if I don't view the file. I've just imported a simple table with ~90k rows from a PHPMyAdmin export and HeidiSQL sits for ages doing something, but I've no idea what. Nothing shows up in the process list. Then, after a few minutes, they all whiz through in the SQL log at the bottom and I get this message: /* 174,020 rows affected, 0 rows found. Duration for 44 queries: 3.061 sec. */ There is a preceding TRUNCATE TABLE in the file prior to the import queries. This particular file is only 1.97MB so I don't get the option to bypass the viewing of the file, whereas an 11MB file that has 170k rows will import in seconds if I don't view it in the editor. Viewing it locks up my Heidi instance for ages. I'm running 6.0.0.3603 on XP SP3 Please help! The only similar issues I've been able to find by searching so far are related to an older build just importing slowly full stop. Thanks
Written by ansgar 2 years ago 3975 posts since Fri, 07 Apr 06	Before reporting problems please update to the latest build. I guess it's all the same in the latest one, but could easily be there is something fixed which I don't recall right now. Huge SQL dumps will never be fast loaded and executed if you load them into a query tab - this is why I introduced that "run directly" mode.
Written by Antnee 2 years ago 9 posts since Thu, 21 Jul 11	Is the latest nightly build considered stable? I only ever upgrade to stable versions, which is why I'm still on 3603. Is there a way to lower the size at which the "run directly" option appears? I would be happy with that to be honest.
Written by ansgar 2 years ago 3975 posts since Fri, 07 Apr 06	The latest builds contain fixed for several old problems, on the other side they can contain new issues. It's more a gutt feeling when I say: prefer the latest build over the last stable release. There is no way to lower the 5mb for the "run directly" mode.
Written by Antnee 2 years ago 9 posts since Thu, 21 Jul 11	That's a shame, because waiting 10 minutes to import a 2MB file is unacceptableI detest PHPMyAdmin and I REALLY don't want to have to resort to using it, and I have no way to get the file to the server to run at a command line. Oh well. It's a minor gripe in an otherwise excellent product that, to be fair, I don't pay for :)
Written by ansgar 2 years ago 3975 posts since Fri, 07 Apr 06	Oh I just recall how you can probably run your dump lightning fast. That's not possible in the 6.0 release, so be sure to have the latest build first. Then, load the file into your editor. Now, before clicking the "play" button, use the drop down menu right besides that button and activate "Send batch in one go". Now, click "play".
Written by Antnee 2 years ago 9 posts since Thu, 21 Jul 11	Nice. I shall give that a go. Thanks
Written by kalvaro 2 years ago 442 posts since Thu, 29 Nov 07	Nightly builds are not stable by definition. I normally install the stable release, copy the directory into another location and create a link to the copy as "HeidiSQL (latest)". In this copy, I update to latest snapshot. This way I can keep both versions.
Written by Antnee 2 years ago 9 posts since Thu, 21 Jul 11	Anse, I just tried what you suggested. Started at 14:24 here, sat on "Splitting SQL queries..." (which I wasn't expecting it to do) until 14:31 and then completed in 0.187 seconds: /* 0 rows affected, 0 rows found. Duration for 44 queries: 0.187 sec. */ This is with build 3911
Written by ansgar 2 years ago 3975 posts since Fri, 07 Apr 06	Oh ok. In that case I assume you have a large number of small INSERTs, which each one producing some overhead.
Written by Antnee 2 years ago 9 posts since Thu, 21 Jul 11	There are 44 queries, each with approx 2,000 rows if I remember correctly. Each row consists of four INT(10) fields
	Please login to leave a reply, or register at first. Username: Password: Remember me