Attachment 101031 Details for Bug 24904 – Bug 24904: Speed up installer process - 1 query per table

[patch] Bug 24904: Speed up installer process - 1 query per table

Bug-24904-Speed-up-installer-process---1-query-per.patch (text/plain), 4.14 KB, created by Bernardo Gonzalez Kriegel on 2020-03-19 15:27:36 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Bernardo Gonzalez Kriegel

Created: 2020-03-19 15:27:36 UTC

Size: 4.14 KB

patch

obsolete

>From b4a28817c33afe3de88e833db1a21000c7e751b0 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Thu, 19 Mar 2020 11:57:16 +0100
>Subject: [PATCH] Bug 24904: Speed up installer process - 1 query per table
>MIME-Version: 1.0
>Content-Type: text/plain; charset=UTF-8
>Content-Transfer-Encoding: 8bit
>
>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
>
>Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
>My numbers, 99.99% confidence
>
>3.906 Â±0.252    SQL files
>15.516 Â±0.772   YAML wo/patch
>3.324 Â±0.193    YAML w/patch
>
>Very good!
>---
> 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.17.1

Actions: View | Diff | Splinter Review

Attachments on bug 24904: 101005 | 101006 | 101030 | 101031 | 101071