diff options
| author | Ivan Kohler <ivan@freeside.biz> | 2017-01-31 19:17:13 -0800 |
|---|---|---|
| committer | Christopher Burger <burgerc@freeside.biz> | 2018-06-18 12:00:17 -0400 |
| commit | 20bfaf53d36f64261b9c6fe7a548b64ad40f5868 (patch) | |
| tree | 19315d01e9911968a79cf94c7814f443e9480a5e /FS/FS | |
| parent | 3d48c56539401f62cc46173479bb29879ec6d019 (diff) | |
further optimize condition_sql for "Invoice eligible for automatic collection" condition, RT#74451
Diffstat (limited to 'FS/FS')
| -rw-r--r-- | FS/FS/part_event/Condition/cust_bill_hasnt_noauto.pm | 38 |
1 files 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 + ) + ) "; } |