diff options
| author | Ivan Kohler <ivan@freeside.biz> | 2017-01-31 19:17:13 -0800 |
|---|---|---|
| committer | Ivan Kohler <ivan@freeside.biz> | 2017-01-31 19:17:13 -0800 |
| commit | 18af4f3316291938fa8f0a74e083209f62eac4fa (patch) | |
| tree | 5e29ccb5bb496ec7a4519f88a4547305d32334fb | |
| parent | 0549def83bcba56e53482ea0f33472e18b215649 (diff) | |
further optimize condition_sql for "Invoice eligible for automatic collection" condition, RT#74451
| -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 0276255..d782c12 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 + ) + ) "; } |