From 2638644df8ec12a4a2c896eec8d00a1afd74aa11 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 26 Jul 2014 13:50:55 -0700 Subject: [PATCH] optimize freeside-cdrd pending query, RT#29752 --- FS/bin/freeside-cdrd | 31 +++++++++++++++++-------------- 1 file changed, 17 insertions(+), 14 deletions(-) diff --git a/FS/bin/freeside-cdrd b/FS/bin/freeside-cdrd index b21bd5b07..45d58789d 100644 --- a/FS/bin/freeside-cdrd +++ b/FS/bin/freeside-cdrd @@ -39,24 +39,27 @@ my $extra_sql = " AND optionvalue = '1' ". " AND ( susp IS NULL OR susp = 0)". " AND ( cancel IS NULL OR cancel = 0)". - " AND 0 < ( - SELECT COUNT(*) FROM svc_phone LEFT JOIN cust_svc USING (svcnum) + " AND EXISTS ( + SELECT 1 FROM svc_phone LEFT JOIN cust_svc USING (svcnum) WHERE cust_pkg.pkgnum = cust_svc.pkgnum - AND 0 < ( SELECT COUNT(*) FROM cdr - WHERE ( freesidestatus IS NULL OR freesidestatus = '' ) - AND ( charged_party = svc_phone.phonenum - OR charged_party = svc_phone.countrycode - || svc_phone.phonenum - OR src = svc_phone.phonenum - OR src = svc_phone.countrycode - || svc_phone.phonenum - ) - ) + AND EXISTS ( SELECT 1 FROM cdr + WHERE ( freesidestatus IS NULL OR freesidestatus = '' ) + AND ( charged_party = svc_phone.phonenum + OR charged_party = svc_phone.countrycode + || svc_phone.phonenum + OR src = svc_phone.phonenum + OR src = svc_phone.countrycode + || svc_phone.phonenum + ) + LIMIT 1 + ) + LIMIT 1 ) - AND 0 = ( - SELECT COUNT(*) FROM queue + AND NOT EXISTS ( + SELECT 1 FROM queue WHERE queue.job = 'FS::cust_main::queued_bill' AND queue.custnum = cust_pkg.custnum + LIMIT 1 ) "; -- 2.11.0