1. 

   Markh76, Member

   I have a fairly large result set(70k rows) that is a union of the same query from 4 databases that are on the same server. When I run it with a small date range and bring back maybe 10k rows, it exports to csv as expected. When I run it with a larger date range, it runs for about 6 minutes and then displays the results as html? Default output on the report is CSV.

   When I run the query in mysql workbench it takes between 8 or 9 minutes to complete. I'm stumped. Have php timeout set to 10 minutes..

   Any other ideas to get this to a csv output would be appreciated.

   Thanks, Mark
2. 

   myDBR Team, Key Master

   Mark,
   there really is no reason why the output format would change depending on result set size unless there is some resource constraints on the server. We can take a look if we can speed up the CSV export.

   --
   myDBR Team
3. 

   myDBR Team, Key Master

   Mark,
   If your query takes 8 or 9 minutes ion MySQL Workbench to complete, it will take longer in myDBR as CSV export needs additional time to format individual items (MySQL Workbench just dumps the data). You could take a look if your query can be optimized.

   We've added two options you can use to speed up the CSV export.

   You can use the "csv.direct_mode"-option if your report contains just one resultset and you do not use special formatting like crosstabs.

   select 'dbr.export.options', 'csv.direct_mode', 1;

   If you do not use formatting (dbr.colstyle, dbr.cellstyle etc) you can additionally speed up the CSV export via 'csv.skip_formatting' option.

   select 'dbr.export.options', 'csv.skip_formatting', 1;

   Using both options should speed up CSV export on large datasets (tens of thousands rows) quite a bit.

   Let us know if this helps you.
   --
   myDBR Team
4. 

   Markh76, Member

   Thanks for the fast response. While the suggestions did speed it up some, still goes to html if I bring back more than say 15 days worth of data.. When it fails and shows an html table, the html response never stops and keeps the webpage spinning and somewhat unresponsive, like it's still loading.

   I was able to speed my query up with no server load to about 1.5 minutes by dumping into a temp table and then 'select * from temptable' for result set but something else going on here when I run it in mydbr:

   Test 1:
   15 day range on data, started at 4:10:00pm, download csv window pop ups up at 4:11:25pm.. 1 minute and 25 seconds.

   Test 2:
   30 day range on data, started at 4:12:00pm, html table starts to render at 4:13:48pm, at about 1 minute 40 seconds.

   I'm totally confused.

   Mark
5. 

   myDBR Team, Key Master

   You did run the updater to get the latest build?

   As said, there really is no reason for HTML output to show up unless the server runs out of resources somehow (which it should not if you use the above options with then new build). The HTML output you are getting is the actual report content?

   --
   myDBR Team

Reply

You must log in to post.