From 4314003dc0393a9565b57fe16c65d9f5deecb92f Mon Sep 17 00:00:00 2001 From: Sophie Meynieux Date: Mon, 28 Feb 2011 16:25:23 +0100 Subject: [PATCH] Bug 6078: Request to find borrowers without issues overload the server Optimization of the SQL request using NOT EXISTS instead of NOT IN BibLibre MT5946 Signed-off-by: Chris Cormack Signed-off-by: Paul Poulain --- 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.4.1