Idea to speed-up sql export significantly

Hi HeidiSQL team,

I use different MySQL front ends for different task because no single one is best in all disciplines. But by now I use HeidiSQL for almost all tasks.

But one thing is a problem for me: The SQL export. Although it is convenient and gives me all options I need, it is extremely slow.

I exported a table as .sql file with about 500.000 lines with HeidiSQL and it took about 14 minutes. I did the same table with (the long deprecated) MySQL-Front and it took 7 seconds. A tool I created myself is not as fast but it also does the export in less than half a minute.

Since I normally have to deal with even bigger tables than 500.000 rows, it takes ages to export this data.

I assume that a lot of time is necessary for the constant repainting of number of exported rows. It seems for each row that is exported, the number is repainted. Repainting only 5 to 10 times per second could already speed-up the process significantly.

Maybe you could give it a try or use some other method to make export faster.

That would be much appreciated!

Thanks & best regards

Freejay

I have found in MySQL it to be related to Limit By part of the SQL query. Issue 1428

I just export tables. I don't use a query with LIMIT.

Of course HeidiSQL uses a LIMIT in the export logic.

I will try to set some attention on issue #1428, the examples look promising.

Again just to clarify: I'm not talking about queries, I'm not talking about using LIMIT: I'm talking about context menu entry "Export database as SQL" and 14 minutes vs. 7 seconds.

But of course: If issue #1428 also solves my problem: I'm all in! ;-)

And I'm still sure that painting each row number significantly slows down the whole export process.

Have a nice day! 🌞

@freejay what you think HeidiSQL paints on each row is done only after one chunk of rows. So if you leave it at the default 10MB in "Max INSERT size", this should definitely not be a problem. Of course if you set it to 0 or a very low value, this might get an issue.

@ansgar Oh wow! I never looked at that option and it was set to 0 on my system. I don't know if or when I changed the default (I can't remember doing that).

I changed it to 10MB and now it only takes half a minute!

Thanks! That really helps!🤩