Import Performance When Viewing SQL

[expired user #5839]'s profile image [expired user #5839] posted 13 years ago in Import/Export Permalink
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
ansgar's profile image ansgar posted 13 years ago Permalink
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.
[expired user #5839]'s profile image [expired user #5839] posted 13 years ago Permalink
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.
ansgar's profile image ansgar posted 13 years ago Permalink
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.
[expired user #5839]'s profile image [expired user #5839] posted 13 years ago Permalink
That's a shame, because waiting 10 minutes to import a 2MB file is unacceptableunhappyI 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 :)
ansgar's profile image ansgar posted 13 years ago Permalink
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".
[expired user #5839]'s profile image [expired user #5839] posted 13 years ago Permalink
Nice. I shall give that a go. Thanks
kalvaro's profile image kalvaro posted 13 years ago Permalink
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.
[expired user #5839]'s profile image [expired user #5839] posted 13 years ago Permalink
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
ansgar's profile image ansgar posted 13 years ago Permalink
Oh ok. In that case I assume you have a large number of small INSERTs, which each one producing some overhead.
[expired user #5839]'s profile image [expired user #5839] posted 13 years ago Permalink
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.