From 18af4f3316291938fa8f0a74e083209f62eac4fa Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Tue, 31 Jan 2017 19:17:13 -0800 Subject: [PATCH] further optimize condition_sql for "Invoice eligible for automatic collection" condition, RT#74451 --- .../part_event/Condition/cust_bill_hasnt_noauto.pm | 38 +++++++--------------- 1 file changed, 12 insertions(+), 26 deletions(-) diff --git a/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm b/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm index 027625569..d782c12c1 100644 --- a/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm +++ b/FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm @@ -26,32 +26,18 @@ sub condition { sub condition_sql { my( $class, $table, %opt ) = @_; - # XXX: can be made faster with optimizations? - # -remove some/all sub-selects? - # -remove the two main separate selects? - - "0 = (select count(1) from cust_pkg - where cust_pkg.no_auto = 'Y' and cust_pkg.pkgnum in - (select distinct cust_bill_pkg.pkgnum - from cust_bill_pkg, cust_pkg - where cust_bill_pkg.pkgnum = cust_pkg.pkgnum - and cust_bill_pkg.invnum = cust_bill.invnum - and cust_bill_pkg.pkgnum > 0 - ) - ) - AND - 0 = (select count(1) from part_pkg - where part_pkg.no_auto = 'Y' and part_pkg.pkgpart in - (select cust_pkg.pkgpart from cust_pkg - where pkgnum in - (select distinct cust_bill_pkg.pkgnum - from cust_bill_pkg, cust_pkg - where cust_bill_pkg.pkgnum = cust_pkg.pkgnum - and cust_bill_pkg.invnum = cust_bill.invnum - and cust_bill_pkg.pkgnum > 0 - ) - ) - ) + # can be made still faster with optimizations? + + "NOT EXISTS ( SELECT 1 FROM cust_pkg + LEFT JOIN part_pkg USING (pkgpart) + WHERE ( cust_pkg.no_auto = 'Y' OR part_pkg.no_auto = 'Y' ) + AND cust_pkg.pkgnum IN + ( SELECT DISTINCT cust_bill_pkg.pkgnum + FROM cust_bill_pkg + WHERE cust_bill_pkg.invnum = cust_bill.invnum + AND cust_bill_pkg.pkgnum > 0 + ) + ) "; } -- 2.11.0