Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Export Speed…

  • This topic is empty.
Viewing 8 reply threads
  • Author
    Posts
    • #11516
      daniel
      Member

      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.

    • #29057
      daniel
      Member

      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.

    • #29058
      daniel
      Member

      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.

    • #29059
      peterlaursen
      Participant

      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.

    • #29060
      daniel
      Member
      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.

    • #29061
      Manoj
      Member

      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

    • #29062
      Manoj
      Member

      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.

    • #29063
      peterlaursen
      Participant

      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).

    • #29064
      daniel
      Member
      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.

Viewing 8 reply threads
  • You must be logged in to reply to this topic.