From 13413f922eb9e0087b5f0fa6bd5d6782749476db Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Fri, 12 Oct 2018 14:09:54 +0200 Subject: [PATCH] Bug 21560: report ODS export optimization SQL reports can be exported with CSV, TSV of ODS format. When report has thousands of rows, using ODS format is around 10 times longer than other formats. It also loads CPU and RAM a lot. The longest call is expandTable(). I found that the call on OpenOffice-OODoc lib can be optimized. Based on https://grep.metacpan.org/search?qci=&q=expandTable&qft=&qd=OpenOffice-OODoc&f=examples%2Ftext2table Test plan : 1) Don't apply patch yet 2) Create a new SQL report : Home > Reports > Create from SQL 3) Enter a SQL that will return thousands of results 4) Run report 5) Click Download > Semicolon separated text (.csv), look execution time 6) Click Download > Open Document Spreadsheet, look execution time 7) Apply patch 8) Redo 5) and 6) and compare times, CSV export should be the same but ODS export should be better My tests shows 1,5 seconds for CSV export. And for ODS export : 18 seconds without patch and 8 seconds with patch. Signed-off-by: David Nind Signed-off-by: Chris Cormack Signed-off-by: David Nind --- reports/guided_reports.pl | 22 +++++++++++++--------- 1 file changed, 13 insertions(+), 9 deletions(-) diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 24dd1081dc..2b9150435e 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -906,37 +906,41 @@ elsif ($phase eq 'Export'){ my $ods_fh = File::Temp->new( UNLINK => 0 ); my $ods_filepath = $ods_fh->filename; + # Create document use OpenOffice::OODoc; my $tmpdir = dirname $ods_filepath; odfWorkingDirectory( $tmpdir ); - my $container = odfContainer( $ods_filepath, create => 'spreadsheet' ); - my $doc = odfDocument ( - container => $container, - part => 'content' - ); - my $table = $doc->getTable(0); + my $doc = odfDocument( file => $ods_filepath, create => 'spreadsheet' ); + + # Prepare sheet my @headers = header_cell_values( $sth ); my $rows = $sth->fetchall_arrayref(); my ( $nb_rows, $nb_cols ) = ( 0, 0 ); $nb_rows = @$rows; $nb_cols = @headers; - $doc->expandTable( $table, $nb_rows + 1, $nb_cols ); + my $sheet = $doc->expandTable( 0, $nb_rows + 1, $nb_cols ); + my @rows = $doc->getTableRows($sheet); - my $row = $doc->getRow( $table, 0 ); + # Write headers row + my $row = $rows[0]; my $j = 0; for my $header ( @headers ) { $doc->cellValue( $row, $j, $header ); $j++; } + + # Write all rows my $i = 1; for ( @$rows ) { - $row = $doc->getRow( $table, $i ); + $row = $rows[$i]; for ( my $j = 0 ; $j < $nb_cols ; $j++ ) { my $value = Encode::encode( 'UTF8', $rows->[$i - 1][$j] ); $doc->cellValue( $row, $j, $value ); } $i++; } + + # Done $doc->save(); binmode(STDOUT); open $ods_fh, '<', $ods_filepath; -- 2.11.0