From a62a5ee94ba509217138a370a1f600d1627bde11 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic 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 Signed-off-by: Chris Cormack --- C4/Creators/Lib.pm | 13 ++----------- 1 files changed, 2 insertions(+), 11 deletions(-) diff --git a/C4/Creators/Lib.pm b/C4/Creators/Lib.pm index 8fd25fd..1e70430 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=?"; + 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; -- 1.7.5.4