[Spce-user] Fraud calculation takes hours

[Walter Klomp]

Hi,

I actually moved my database to external server and optimised some indexes to make this faster (but SSD and more memory definitely helps)

While you (Sipwise) are at it to optimise this query, you may also want to consider to calculate this not from the 1st of the month, but from a month before, until now…

so if you do the calculation today it should run from 27 Dec to 26 Jan (this time) - so that it’s a more accurate period, otherwise in the beginning of the month somebody can do a huge amount of fraud, and at the end of the month they run out immediately.  Same for the daily limits, they should be from yesterday, current time, to now.

as in this example.

>> WHERE CASE WHEN bpinfo.interval = 'month'
>>   THEN cdr.start_time
>>     BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-%d %H:%i:%s'))
>>         AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(),
>>         '%Y-%m-%d %H:%i:%s'))-1
>>   ELSE cdr.start_time
>>     BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y-%m-%d %H:%i:%S'))
>>         AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(),
>>         '%Y-%m-%d %H:%i:%S'))
>>   END
Does that make sense ?

Thanks
Walter




> 


> On 26 Jan 2018, at 12:08 AM, Alejandro Grijalba <agrijalba at innovasur.es> wrote:
> 
> 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 <http://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 <http://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 <http://ca.id/> as contract_id,
>>       (select bma.id <http://bma.id/> from billing.billing_mappings bma where
>>       bma.contract_id=ca.id <http://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 <http://bma.id/> desc limit 1) as id from
>>       billing.contracts ca
>>    ) AS bm_actual ON bm_actual.contract_id = c.id <http://c.id/>
>>   JOIN billing.billing_mappings bm ON bm.id <http://bm.id/> = bm_actual.id <http://bm_actual.id/>
>>   JOIN billing.billing_profiles bp ON bp.id <http://bp.id/> = bm.billing_profile_id
>>   JOIN billing.contacts n ON n.id <http://n.id/> = c.contact_id
>>   JOIN billing.resellers r ON r.id <http://r.id/> = n.reseller_id
>>   LEFT JOIN billing.contract_fraud_preferences cfp 
>>     ON cfp.contract_id = c.id <http://c.id/>
>>   WHERE c.status = 'active'
>>   HAVING interval_limit > 0
>> ) AS bpinfo
>> JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id <http://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 <http://bpinfo.id/>
>> HAVING interval_cost >= interval_limit;
>> 
>> 
>> 
>> 
>> thanks&regards,
>> rene
>> 
> 
> 
> -- 
> Alejandro Grijalba Martínez
> Departamento de Sistemas
> 
> <firma_correo.png>
> 
> E agrijalba at innovasur.com <mailto: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/>_______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com>
> https://lists.sipwise.com/listinfo/spce-user <https://lists.sipwise.com/listinfo/spce-user>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20180126/da3ce64b/attachment-0001.html>