[Koha-patches] [Signed Off] [PATCH] Bug 6078: Request to find borrowers without issues overload the server
Chris Cormack
chris at bigballofwax.co.nz
Tue Jun 7 08:42:13 CEST 2011
From: Sophie Meynieux <sophie.meynieux at biblibre.com>
Optimization of the SQL request using NOT EXISTS instead of NOT IN
BibLibre MT5946
Signed-off-by: Chris Cormack <chris at bigballofwax.co.nz>
---
reports/borrowers_out.pl | 18 ++++++------------
1 files changed, 6 insertions(+), 12 deletions(-)
diff --git a/reports/borrowers_out.pl b/reports/borrowers_out.pl
index 0a135e1..278e412 100755
--- a/reports/borrowers_out.pl
+++ b/reports/borrowers_out.pl
@@ -230,18 +230,12 @@ sub calculate {
@$filters[0]=~ s/\*/%/g if (@$filters[0]);
$strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] );
- my $strqueryfilter = "SELECT DISTINCT borrowernumber FROM ";
- $strqueryfilter .= "(SELECT borrowernumber from old_issues WHERE borrowernumber IS NOT NULL ";
- if ($filters->[1]){
- $strqueryfilter .= "AND old_issues.timestamp> '$filters->[1]' ";
- }
- $strqueryfilter .= "UNION SELECT borrowernumber FROM issues WHERE 1 ";
- if ($filters->[1]){
- $strqueryfilter .= "AND issues.timestamp> '$filters->[1]' ";
- }
- $strqueryfilter .= ") active_borrowers";
-
- $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)";
+ $strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber ";
+ $strcalc .= " AND issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]);
+ $strcalc .= ") ";
+ $strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber ";
+ $strcalc .= " AND old_issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]);
+ $strcalc .= ") ";
$strcalc .= " group by borrowers.borrowernumber";
$strcalc .= ", $colfield" if ($column);
$strcalc .= " order by $colfield " if ($colfield);
--
1.7.2.2
More information about the Koha-patches
mailing list