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
_______________________________________________
Koha-patches mailing list
[email protected]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-patches
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
