monthly job lock db ‘razordw’

[rockiee281]

insert into razor_fact_reserveusers_monthly (startdate_sk, enddate_sk, product_id, version_name,month1)
Select
(select date_sk from razor_dim_date where datevalue = date_add( NAME_CONST('begindate',_latin1'2014-02-01' COLLATE 'latin1_swedish_ci'),interval -1 MONTH)) startdate,
(select date_sk from razor_dim_date where datevalue = last_day(date_add( NAME_CONST('enddate',_latin1'2014-02-28' COLLATE 'latin1_swedish_ci'),interval -1 MONTH))) enddate,
p.product_id,p.version_name,
count(distinct f.deviceidentifier)
from
razor_fact_clientdata f, razor_dim_date d, razor_dim_product p where f.date_sk = d.date_sk and f.product_sk = p.product_sk and d.datevalue between  NAME_CONST('begindate',_latin1'2014-02-01' COLLATE 'latin1_swedish_ci') and  NAME_CONST('enddate',_latin1'2014-02-28' COLLATE 'latin1_swedish_ci') and p.product_active = 1 and p.channel_active = 1 and p.version_active = 1 and exists
(select 1 from razor_fact_clientdata ff, razor_dim_product pp, razor_dim_date dd where ff.product_sk = pp.product_sk and ff.date_sk = dd.date_sk and pp.product_id = p.product_id and dd.datevalue between date_add( NAME_CONST('begindate',_latin1'2014-02-01' COLLATE 'latin1_swedish_ci'),interval -1 MONTH) and last_day(date_add( NAME_CONST('enddate',_latin1'2014-02-28' COLLATE 'latin1_swedish_ci'),interval -1 MONTH)) and ff.deviceidentifier = f.deviceidentifier and pp.product_active = 1 and pp.channel_active = 1 and pp.version_active = 1 and ff.isnew = 1) group by p.product_id
on duplicate key update month1 = values(month1)

[rockiee281]

Need to imporve the procedure to reduce execution time

[cobub]

Thanks. When the data increase quickly, this procedure will run for a long time. We're planning to adjust the structure to solve this problem .

[rockiee281]

Glad to hear this :+1: