[Spce-user] Fraud calculation takes hours

[Alejandro Grijalba]

10 minutes and counting so far :(


El 25/01/2018 a las 16:45, Rene Krenn escribió:
> Hi,
>
> thanks for pointing.
>
> Can you try running this modified query below on your test database a few times,
> and report back how long it took (longer or less 2 minutes)?
>
> SELECT bpinfo.id, bpinfo.reseller_id, bpinfo.interval, bpinfo.type,
>         IF (bpinfo.fraud_use_reseller_rates > 0,
>            SUM(cdr.source_reseller_cost),
>            SUM(cdr.source_customer_cost)) as interval_cost,
>         bpinfo.interval_limit,
>         bpinfo.interval_lock,
>         bpinfo.interval_notify,
>         bpinfo.fraud_use_reseller_rates as use_reseller_rates
> FROM (
>    SELECT c.id, n.reseller_id, bp.fraud_use_reseller_rates, i.interval,
>      IF (i.interval = 'month',
>          IF (cfp.fraud_interval_limit > 0,
>              'account_limit', 'profile_limit'),
>          IF (cfp.fraud_daily_limit > 0,
>              'account_limit', 'profile_limit')
>         ) AS type,
>      IF (i.interval = 'month',
>          IF (cfp.fraud_interval_limit > 0,
>              cfp.fraud_interval_limit, bp.fraud_interval_limit),
>          IF (cfp.fraud_daily_limit > 0,
>              cfp.fraud_daily_limit, bp.fraud_daily_limit)
>         ) AS interval_limit,
>      IF (i.interval = 'month',
>          IF (cfp.fraud_interval_limit > 0,
>              cfp.fraud_interval_lock, bp.fraud_interval_lock),
>          IF (cfp.fraud_daily_limit > 0,
>              cfp.fraud_daily_lock, bp.fraud_daily_lock)
>         ) AS interval_lock,
>      IF (i.interval = 'month',
>          IF (cfp.fraud_interval_limit > 0,
>              cfp.fraud_interval_notify, bp.fraud_interval_notify),
>          IF (cfp.fraud_daily_limit > 0,
>              cfp.fraud_daily_notify, bp.fraud_daily_notify)
>         ) AS interval_notify
>    FROM (SELECT IF("day" = 'month','month','day') AS 'interval') i,
>         billing.contracts c
>    INNER JOIN (
>      select ca.id as contract_id,
>        (select bma.id from billing.billing_mappings bma where
>        bma.contract_id=ca.id and
>        (bma.end_date >= NOW() OR bma.end_date IS NULL)
>        AND (bma.start_date <= NOW() OR bma.start_date IS NULL) order
>        by bma.start_date desc, bma.id desc limit 1) as id from
>        billing.contracts ca
>     ) AS bm_actual ON bm_actual.contract_id = c.id
>    JOIN billing.billing_mappings bm ON bm.id = bm_actual.id
>    JOIN billing.billing_profiles bp ON bp.id = bm.billing_profile_id
>    JOIN billing.contacts n ON n.id = c.contact_id
>    JOIN billing.resellers r ON r.id = n.reseller_id
>    LEFT JOIN billing.contract_fraud_preferences cfp
>      ON cfp.contract_id = c.id
>    WHERE c.status = 'active'
>    HAVING interval_limit > 0
> ) AS bpinfo
> JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id
> WHERE CASE WHEN bpinfo.interval = 'month'
>    THEN cdr.start_time
>      BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00'))
>          AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH,
>          '%Y-%m-01 00:00:00'))-1
>    ELSE cdr.start_time
>      BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))
>          AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 DAY,
>          '%Y-%m-%d 00:00:00'))-1
>    END
> GROUP BY bpinfo.id
> HAVING interval_cost >= interval_limit;
>
>
>
>
> thanks&regards,
> rene
>


-- 
Documento sin título

Alejandro Grijalba Martínez
Departamento de Sistemas

E agrijalba at innovasur.com
D Calle Sierra Morena 12 A Edif.CTSAII. Pta. 2º, Oficina 9. 23620 
Mengíbar (Jaén) / España.
T +34 953 267 919 <tel:+34953267919>

www.innovasur.com <http://www.innovasur.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20180125/c06e60e2/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: firma_correo.png
Type: image/png
Size: 2284 bytes
Desc: not available
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20180125/c06e60e2/attachment-0001.png>