apao, Member
Hi everyone,
I've just upgraded from myDBR 4.6.x to 4.8.0 and big CSV reports (about 40 000 rows and 75 columns) seem to need more time than before to be processed.
But I'm not 100% sure of it (the database size and/or the current load of the server might explain it as well) so I would need to revert to my previous version of myDBR to confirm it.
Is there a way to do so?
Thanks in advance for your help.
PS: or do you know if CSV reports actually need more time than before to be processed?
myDBR Team, Key Master
Hi,
the export speed between 4.6 and 4.8 should be roughly the same. Nothing in the code has changed to attribute to this, so there must be other factors. What is the PHP version you are running? PHP 7 should give you speed increase as PHP code execution is roughly twice as fast in PHP 7.
In exports, the Excel-exports in 4.8 have been heavily optimized resulting to 4x faster exports in large datasets. Due to the complexity of Excel format, the CSV-export will always be faster as it pretty much generates the export as fast as the database delivers it.
There is no route back to old application version as the database structures have already been updated to match the 4.8 version.
--
myDBR Team
apao, Member
First of all, thanks for your answer.
We're running PHP 5.4.36 on the machine but unfortunately, we can not upgrade to PHP 7 that easily because other applications use the PHP server and we're not 100% sure they would be PHP 7-compatible.
However, with the same PHP version, our biggest report needed about 2 or 3 minutes to be processed some months ago and it now needs 6 or 7 minutes.
We're investigating to see if something else might explain this loss.
Meanwhile, there's something you said that surprised me :
the CSV-export will always be faster as it pretty much generates the export as fast as the database delivers it.
I agree that CSV-export is faster, but in our case, it has never been able to generate the export as fast as the database delivers it. For our largest report (40 000 rows / 75 columns), we have an SQL query that needs less than 30 seconds to run on our database server... but the report itself has always needed at least 2 minutes to be generated by myDBR (this does not include the download time from the server to the client).
I had always thought it was due to the time needed by myDBR to populate the CSV file with the data but if you say it's nearly immediate, what are these 2 minutes (that have now become 6 or 7 minutes) used for ?
myDBR Team, Key Master
Hi,
we tested the myDBR 4.6 and myDBR 4.8 on PHP 5.6 and there was no big difference in execution time (both export around 5 minutes ±10sec). For comparison, PHP 7 runs the same in around 3.5 minutes. The size of the result CSV file generated with our data was 15Mb. 40 000 rows * 75 columns is 3 million values to be formatted, that is around 8300 values / second including all overheads. Your mileage might vary due to differencies in DB, servers, actual query and data.
When a PHP connects to the database there is an overhead with the PHP code, network and the web code. When you also deal with larger datasets (like yours) the server memory / I/O restrictions come into the play as well in addition to the database overhead (query time). When you run a query in the database, none of them exist (aparat from the query time). Of course the actual CSV generation also takes some time.
As said, the CSV export code is quite straightforward, the format itself is very simple compared to other export formats.
--
myDBR Team
apao, Member
Even if it unfortunately does not solve my problem, thanks for all the information of your post.
myDBR Team, Key Master
If your report is a direct query containing only a large dataset (no additional features used like dbr.calc, dbr.running_total etc), you can speed up the CSV export by using 'csv.direct_mode':
select 'dbr.export.options', 'csv.direct_mode', 1;
Have you trid that?
--
myDBR Team
apao, Member
No, I hadn't tried this. Is this a recent feature (less than 1 year old)?
However, I've just tried it and it does not seem to work: there's about no data in the report when I launch it.
In fact, I have 2 resultsets in my export: one for the data itself and one just before to sum up the parameters set by the user. If I activate the csv direct mode option, only the first resultset (the one which sums up the user's parameters) is exported. So this unfortunately does not meet my needs.
myDBR Team, Key Master
The csv.direct_mode was instoduced back in myDBR 4.6.
The CSV files usually contain just one result set (rows + columns). myDBR does support multiple result sets in a CSV file and we extended this to the latest build, so you could try updating myDBR and giving it a try.
--
myDBR Team
apao, Member
Incredible! It's so much faster!
I still need 2 minutes to download the report once it's generated but the generation itself is really quick.
However I had an empty row between my two resultsets so far (it was helpful to separate them) but it disappears when I activate the csv direct mode option. Is this a bug?
Another question: I would like to apply the csv direct mode option on every report created but a few of them are exported in HTML. Is there any side-effect if I apply the csv direct mode option on them?
apao, Member
PS: and on a more general note, if I apply the csv direct mode option by default on every report, what happens if functions like those you listed (dbr.calc, dbr.running_total...) are later added on some reports?
myDBR Team, Key Master
No side effect. The csv.direct_mode is just for the CSV import, it has not effect on HTML-reports.
There is no linefeed between the resultsets (as said, CSV files usually do not conttain multiple resultsets). You might consider exporting them separately.
--
myDBR Team
apao, Member
I can't export them separately: the first resultset is the list of parameters set by the user for the report (this is something I added because parameters are not natively mentioned in CSV exports).
About the linefeed between the resultsets exported in CSV, there is one (I mean, an empty line between the 2 resultsets) as long as you don't activate the csv direct mode option. And there is none when you activate it. That's why it looks like a bug.
PS: if we except this little "bug", the csv direct mode option seems to have no drawback, so if there is no side-effect as well, why isn't it natively activated on every report?
myDBR Team, Key Master
The reason the direct mode is so fast that is directly produces a CSV file which is 1:1 with the raw query result. The default CSV export produces the same result as the report itself: adhering things like crosstabs, dbr.calc, dbr.running_total etc.
So if you do not need any of those features, you can use the direct mode. If myDBR would be to detect this, it would first need to read in all the data to see if any of the mentioned features are used making the direct mode slower. So, the direct mode is for the utmost speed.
As for the empty line between the 2 resultsets, the CSV export should not have it (not even in the standad mode) as a CSV file contains just one entity and not having the empty line allows combining multiple result sets as one without use of union.
--
myDBR Team
myDBR Team, Key Master
The CSV direct mode now adheres the csv.linefeed_between_resultsets default and the setting. Just run the updater to get the latest build.
--
myDBR Team
apao, Member
It works like a charm, many thanks!
About the incompatibility between the csv direct mode option and crosstabs/dbr.calc/dbr.running_total, what happens if csv direct mode option is activated in a report where crosstabs/dbr.calc/dbr.running_total are activated as well?
Is csv direct mode option just ignored (which would perfectly suit my needs) or does it (on the contrary) disable crosstabs/dbr.calc/dbr.running_total?
I ask this because I plan to activate the csv direct mode option by default on every report (and report writers won't be able to change it) but I don't want to prevent report writers from using crosstabs/dbr.calc/dbr.running_total if they need to.
myDBR Team, Key Master
When the CSV direct mode is enabled the CSV creation is done directly from the raw input disregarding any other options.
The direct mode is really intended for those mass exports (like your 40k rows with 75 columns). There really is no need to enable it in all the reports as CSV-export is fast (fast like seconds) in normal size reports.
--
myDBR Team
apao, Member
Ok, this might be a better idea indeed.
There's something I noticed during my tests: my web browser proposes the download after less than 10 seconds whereas my DBMS needs about 30 seconds to return the full resultset when I execute the query in the SQL editor of my DBMS. Does myDBR propose the download before getting the full resultset from the DBMS, which would mean myDBR is still generating the file while I'm downloading it?
This would explain why the download process is (relatively) long.
myDBR Team, Key Master
When you export with the CSV direct mode, myDBR process the download as it receives it from the DB. This reduces the resources needed fo the processing for big data sets.
--
myDBR Team
apao, Member
Your product is not only well-thought, it also comes with a super-responsive support.
You deserve every cents of the 129 euros we pay each year!
You must log in to post.