From 9c53db0f01da31ee9afcc3264645daad01b7e2a5 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 19 Mar 2020 11:57:16 +0100 Subject: [PATCH] Bug 24904: Speed up installer process - 1 query per table Since bug 13897 we are using YAML file to store the installer data. We are generating/executing 1 query per row, which is much more slower than before. We should generate 1 query per table to get back to the previous execution time. With this patch we are going to generate a single one big SQL query per table. Test plan: 1/ Checkout a commit before bug 13897: git checkout -b before_13897 0706922221dcdf9fa8ed7fcf86245c5622f33a3c 2/ Execute the following commands (several times, to get a median!): mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl sql" kohadev; Note the different times displayed in the output, especially the first 2 installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.yml installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.yml They are the number of seconds took to insert them 3/ checkout master git checkout -B master origin/master # Will reset your master branch! 4/ Execute the previous commands but without the 'sql' parameter passed to the benchmark script mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl" kohadev; Note the different times. 5/ Apply this patch and retry 4 Please post the execution times when you signoff on this patch. Spoiler: total time goes from 21s to 4s. marc21_framework_DEFAULT.yml from 12s to .5s --- C4/Installer.pm | 12 +++++++----- 1 file changed, 7 insertions(+), 5 deletions(-) diff --git a/C4/Installer.pm b/C4/Installer.pm index 012bc1c3cd..a3f81e556c 100644 --- a/C4/Installer.pm +++ b/C4/Installer.pm @@ -487,19 +487,21 @@ sub load_sql { my @rows = @{ $table->{$table_name}->{rows} }; # my @columns = ( sort keys %{$rows[0]} ); # column names my $fields = join ",", map{sprintf("`%s`", $_)} @columns; # idem, joined - my $placeholders = join ",", map { "?" } @columns; # '?,..,?' string - my $query = "INSERT INTO $table_name ( $fields ) VALUES ( $placeholders )"; - my $sth = $dbh->prepare($query); + my $query = "INSERT INTO $table_name ( $fields ) VALUES "; my @multiline = @{ $table->{$table_name}->{'multiline'} }; # to check multiline values; + my $placeholders = '(' . join ( ",", map { "?" } @columns ) . ')'; # '(?,..,?)' string + my @values; foreach my $row ( @rows ) { - my @values = map { + push @values, map { my $col = $_; ( @multiline and grep { $_ eq $col } @multiline ) ? join "\r\n", @{$row->{$col}} # join multiline values : $row->{$col}; } @columns; - $sth->execute( @values ); } + # Doing only 1 INSERT query for the whole table + $query .= join ', ', ( $placeholders ) x scalar @rows; + $dbh->do( $query, undef, @values ); } for my $statement ( @{ $yaml->{'sql_statements'} } ) { # extra SQL statements $dbh->do($statement); -- 2.20.1