Attachment 9174 Details for Bug 7846 – Bug 7846 - get_batch_summary massive speedup

[patch] Bug 7846 - get_batch_summary massive speedup

Bug-7846---getbatchsummary-massive-speedup.patch (text/plain), 2.62 KB, created by Dobrica Pavlinusic on 2012-04-13 10:49:31 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Dobrica Pavlinusic

Created: 2012-04-13 10:49:31 UTC

Size: 2.62 KB

patch

obsolete

>From 8078acb8bccb3c57448b1d19be537e2e4d9b70fb Mon Sep 17 00:00:00 2001
>From: Dobrica Pavlinusic <dpavlin@rot13.org>
>Date: Thu, 29 Mar 2012 15:39:17 +0200
>Subject: [PATCH] Bug 7846 - get_batch_summary massive speedup
>
>current code is using DISTINCT and another SQL query which can be replaced with GROUP BY
>for massive speedup. In our case, generating Manage Batches screen DBI time decreased
>from 24.762 s to 0.147 s
>
>Aside from correct usage of relational database, this change also cleans up code nicely.
>
>Test scenario:
>
>1. open Manage Batches screen and take note of time needed to generate it
>2. apply this patch
>3. reload page and check page genration time
>---
> C4/Creators/Lib.pm |   19 +++++++------------
> 1 files changed, 7 insertions(+), 12 deletions(-)
>
>diff --git a/C4/Creators/Lib.pm b/C4/Creators/Lib.pm
>index 8fd25fd..c80da05 100644
>--- a/C4/Creators/Lib.pm
>+++ b/C4/Creators/Lib.pm
>@@ -263,8 +263,9 @@ NOTE: Do not pass in the keyword 'WHERE.'
> sub get_batch_summary {
>     my %params = @_;
>     my @batches = ();
>-    my $query = "SELECT DISTINCT batch_id FROM creator_batches WHERE creator=?";
>-    $query .= ($params{'filter'} ? " AND $params{'filter'};" : ';');
>+    my $query = "SELECT batch_id,count(batch_id) as _item_count FROM creator_batches WHERE creator=?";
>+    $query .= ($params{'filter'} ? " AND $params{'filter'}" : '');
>+    $query .= " GROUP BY batch_id";
>     my $sth = C4::Context->dbh->prepare($query);
> #    $sth->{'TraceLevel'} = 3;
>     $sth->execute($params{'creator'});
>@@ -272,17 +273,7 @@ sub get_batch_summary {
>         warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
>         return -1;
>     }
>-    ADD_BATCHES:
>     while (my $batch = $sth->fetchrow_hashref) {
>-        my $query = "SELECT count(batch_id) FROM creator_batches WHERE batch_id=? AND creator=?;";
>-        my $sth1 = C4::Context->dbh->prepare($query);
>-        $sth1->execute($batch->{'batch_id'}, $params{'creator'});
>-        if ($sth1->err) {
>-            warn sprintf('Database returned the following error on attempted SELECT count: %s', $sth1->errstr);
>-            return -1;
>-        }
>-        my $count = $sth1->fetchrow_arrayref;
>-        $batch->{'_item_count'} = @$count[0];
>         push(@batches, $batch);
>     }
>     return \@batches;
>@@ -521,7 +512,11 @@ be passed off as a T::P template parameter and used to build an html table.
> 
> =cut
> 
>+use Carp;
>+use Data::Dump qw(dump);
>+
> sub html_table {
>+Carp::cluck("# html_table ",dump(@_));
>     my $headers = shift;
>     my $data = shift;
>     return undef if scalar(@$data) == 0;      # no need to generate a table if there is not data to display
>-- 
>1.7.2.5

Actions: View | Diff | Splinter Review

Attachments on bug 7846: 8686 | 8739 | 8742 | 9000 | 9174 | 9175 | 9545