Export Speed…

[daniel]

Hi,

Checking through the export code I noticed you do an explicit seek (mysql_data_seek) before you fetch the row. This doesn't seem very efficient and is a pretty slow function on the mysql library. It also means that exports will slow down badly when doing large exports. As far as I know for sequential reads there is absolutely no need to use the mysql_data_seek function. In fact considering all you do later is a rescount++; I assume you aren't doing any funny random access anywhere.

I'd suggest removing the line

Code:

m_tunnel->mysql_data_seek(myres, rescount);

from where it appears before

Code:

myrow = m_tunnel->mysql_fetch_row(myres);

in the export code (e.g. ExportData.cpp:2345)

I haven't tested it, but will later, but my guess is that on large exports the current code is slow.

Daniel.

[daniel]

Did some quick testing. Without the explicit seek >600 000 rows took about 90 seconds to export. WITH the explicit seek, it becomes increasingly slow as more rows are export (internally it may be a linked list, which would explain the increasing seek time for a row that is deeper). After about 40 000 rows it was crawling….I'd guess it would take > 1 hour to export all of them.

So the question is; Is there some particular reason the seek is in there? Hopefully there isn't and we can chop that line and get some fantastic speed increases.

D.

[daniel]

Me again,

Quote:

Is there some particular reason the seek is in there?

Did a SVN Blame….line was put in by [email protected] <_< :P But the SVN entry doesn't list a reason, just the release that it was put in for….. D.

[peterlaursen]

Please wait for the developer team to comment. Manohar has not been working with SQLyog for more than a year (he is with MONyog now), so this is a little bit strange.

[daniel]

peterlaursen wrote on Jun 8 2009, 01:29 PM:

Please wait for the developer team to comment. Manohar has not been working with SQLyog for more than a year (he is with MONyog now), so this is a little bit strange.

Sorry, in fairness it was put in in Rev: 73 (6.14 RC2) November 2007. I'm not suggesting he is to blame per se. Back in 2007 there may have been a very good reason to do that with the current MySQL Native API, but it doesn't appear necessary now.

Also it looks like you guys just push a snapshot of internal code to the googlecode when a release is done, so it could really just be anyone who put it in originally.

D.

[Manoj]

Hi Daniel,

Thanks for reporting this. We are sharing the mysql resultset for export as well as showing it in the grid if we are exporting from table data tab/result tab. So there is a chance that the result sets current row index may change if a paint fired during exporting. After a successfull export SQLyog will show the number of records exported in the dialog, if you don't mind can you double check the number of records exported is same in both case? Anyway we will check this in our end and will check what we can do to speed up.

Thanks again,

Manoj

[Manoj]

forgot to mention another point

We are not using this this method while doing “Backup Table as SQL dump” as well as Scheduled Backup. We are using this only in “Export table data/result as…” were we are sharing data.

[peterlaursen]

and “Export table data/result as…” is not intended for backup of tables but for exporting result sets (actually if you use it with a table a warning popup will display).

[daniel]

peterlaursen wrote on Jun 8 2009, 04:18 PM:

and “Export table data/result as…” is not intended for backup of tables but for exporting result sets (actually if you use it with a table a warning popup will display).

Fair enough….but there are loads of times I draw reports for analysis that produce over 1 million entries. Anyway, if you can get a speed increase, then happy days.

D.

[Author]

Posts