April 19, 2016 at 6:28 pm
Hey guys,
I'm in a fairly new role and I've been asked to look into loading a DW incrementally on a nightly basis but I believe this will be quite difficult for a number of reasons. Before taking up that challenge I thought I'd identify the pain points in the current process to try and get some quick wins so the load time is reduced where possible. The process is currently made up of several SSIS packages that call quite a few SPs, and there is one in particular that is taking around 50 minutes to complete.
The code for this proc is as follows:
USE [EDW_SSA_EBX]
GO
/****** Object: StoredProcedure [dbo].[usp_load_utb_ebx_risk__tag_02] Script Date: 20/04/2016 10:19:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_load_utb_ebx_risk__tag_02] AS
-----
DECLARE @proc_name VARCHAR(100)
SET @proc_name = 'usp_load_utb_ebx_risk__tag_02'
DECLARE @datetime_start DATETIME
SET @datetime_start = GETDATE()
-----
DECLARE @curr_ebix_late_date DATETIME
SET @curr_ebix_late_date = (SELECT MAX(late_date) FROM EDW_SSA_EBX.dbo.utb_ebx_cdat)
DECLARE @balance_date DATETIME
SET @balance_date = DATEADD(DAY,-1,@curr_ebix_late_date)
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
cm_ap= rt.cm_ap + rdt.cm_isd - rdt.cm_isd_pf
,cm_pf= rt.cm_pf + rdt.cm_isd_pf
,cm_isd= rt.cm_isd + rdt.cm_isd
,cm_esd= rt.cm_esd + rdt.cm_esd
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_SSA_EBX.dbo.utb_ebx_risk_details__tagrdt
ONrt.ck_risk_ukey = rdt.ck_risk_ukey
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
cm_frq_ldg= (rt.cm_ap) - ((rt.cm_ap) / (1 + (ISNULL(rd.freq_load,0) / -100)))
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_SSA_EBX.dbo.utb_ebx_risk_detailsrd
ONrt.ck_risk_ukey = rd.risk_ukey
JOIN
EDW_SSA_EBX.dbo.utb_ebx_risk_details__tagrdt
ONrt.ck_risk_ukey = rdt.ck_risk_ukey
WHERE1=1
ANDISNULL(rd.freq_load,0) <> 0
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_rei_key= rt.ck_rei_key + '-' + CAST(rr.reia_ukey AS VARCHAR(50))
,ck_reia_ukey= rr.reia_ukey
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_SSA_EBX.dbo.utb_ebx_rein_riskrr
ONrt.ck_rein_ukey = rr.rein_ukey
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_ben_selection= [ck_benefit_config] + '-' +
[ck_occ_cls_cod] + '-' +
[ck_occ_tpd_dfn_cod] + '-' +
[ck_smk_cod] + '-' +
[ck_wai_per_cod] + '-' +
[ck_ben_per_cod] + '-' +
[ck_prm_bss_cod]
----------
-- Establish Sequence
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence_temp= x.max_seq + 1
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagt
JOIN
(
SELECT
ck_cover_inc
,max_seq= MAX(ck_sequence_temp)
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_sequence_temp < 100
GROUP BY
ck_cover_inc
)x
ONt.ck_cover_inc = x.ck_cover_inc
WHERE1=1
ANDck_sequence_temp = 100
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence_temp= 1
WHERE1=1
ANDck_sequence_temp = 100
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence_temp= ck_sequence_temp + 1
WHERE1=1
ANDck_cover_inc IN
(
SELECT DISTINCT
ck_cover_inc
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_sequence_temp = 0
)
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence_temp= 0
WHERE1=1
ANDck_sequence_temp > 100
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence_temp= ck_sequence_temp + 1
WHERE1=1
ANDck_cover_inc IN
(
SELECT DISTINCT
ck_cover_inc
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_sequence_temp = 0
)
-----
DECLARE @seq INT
SET @seq = 1
WHILE@seq <= (SELECT MAX(ck_sequence_temp) FROM EDW_SSA_EBX.dbo.utb_ebx_risk__tag)
BEGIN
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence_temp= @seq
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagt
JOIN
(
SELECT
ck_cover_inc
,min_seq= MIN(ck_sequence_temp)
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_sequence_temp >= @seq
GROUP BY
ck_cover_inc
HAVINGMIN(ck_sequence_temp) > @seq
)x
ONt.ck_cover_inc = x.ck_cover_inc
ANDt.ck_sequence_temp = x.min_seq
-----
SET @seq = @seq + 1
-----
END
----------
-- Set Prior / Next PK
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_risk_pk_p= p.ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_cover_inc
,ck_sequence_temp
,ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)p
ONc.ck_cover_inc = p.ck_cover_inc
ANDc.ck_sequence_temp = p.ck_sequence_temp + 1
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_risk_pk_n= n.ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_cover_inc
,ck_sequence_temp
,ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)n
ONc.ck_cover_inc = n.ck_cover_inc
ANDc.ck_sequence_temp = n.ck_sequence_temp - 1
----------
-- Prepare SYS Dates
----------
SET @seq = 0
WHILE@seq <= (SELECT MAX(ck_sequence_temp) FROM EDW_SSA_EBX.dbo.utb_ebx_risk__tag)
BEGIN
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_date_sys_e= n.ck_date_sys_s
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_risk_pk
,ck_date_sys_s
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)n
ONc.ck_risk_pk_n = n.ck_risk_pk
WHERE1=1
ANDck_sequence_temp = @seq
ANDc.ck_date_sys_e = '3000-01-01'
-----
SET @seq = @seq + 1
-----
END
----------
-- Prepare EFF Dates
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_date_eff_e= ck_date_eff_s
WHERE1=1
ANDDATEDIFF(DAY,ck_date_eff_s,ck_date_eff_e) = -1
----------
-- Prepare BAL Dates
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_date_bal_s= EDW_SSA_EBX.dbo.cf_datetime_max(ck_date_sys_s,ck_date_eff_s)
,ck_date_bal_e= EDW_SSA_EBX.dbo.cf_datetime_max(ck_date_sys_e,ck_date_eff_e)
----------
-- Prepare Durations
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_date_sys_de= DATEDIFF(DAY,c.ck_date_sys_s,c.ck_date_sys_e)
,ck_date_eff_de= DATEDIFF(DAY,c.ck_date_eff_s,c.ck_date_eff_e)
,ck_date_bal_de= DATEDIFF(DAY,c.ck_date_bal_s,c.ck_date_bal_e)
,ck_date_sys_ds= ISNULL(DATEDIFF(DAY,p.ck_date_sys_e,c.ck_date_sys_s),0)
,ck_date_eff_ds= ISNULL(DATEDIFF(DAY,p.ck_date_eff_e,c.ck_date_eff_s),0)
,ck_date_bal_ds= ISNULL(DATEDIFF(DAY,p.ck_date_bal_e,c.ck_date_bal_s),0)
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
LEFT OUTER JOIN
(
SELECT
ck_risk_pk
,ck_date_sys_e
,ck_date_eff_e
,ck_date_bal_e
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)p
ONc.ck_risk_pk_p = p.ck_risk_pk
----------
-- Assign Statuses
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sts_grp_s= CASE
WHENck_sequence_temp = 1
ANDck_sts_grp_e IN ('PRE','NPW')
THEN'PRE'
WHENck_sequence_temp = 1
ANDck_sts_grp_e IN ('INF','OOF')
THEN'INF'
ELSE'ZZZ'
END
,ck_sts_sys_s= CASE
WHENck_sequence_temp = 1
ANDck_sts_grp_e IN ('PRE','NPW')
THEN99
WHENck_sequence_temp = 1
ANDck_sts_grp_e IN ('INF','OOF')
THEN1
ELSE0
END
WHERE1=1
ANDck_sts_sys_s = 0
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sts_grp_s= p.ck_sts_grp_e
,ck_sts_sys_s= p.ck_sts_sys_e
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_risk_pk
,ck_sts_grp_e
,ck_sts_sys_e
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)p
ONc.ck_risk_pk_p = p.ck_risk_pk
WHERE1=1
ANDc.ck_sts_sys_s = 0
----------
-- Insert Gaps in Cover & Re-Sequence
----------
INSERT INTO dbo.utb_ebx_risk__tag
(ck_risk
,ck_policy
,ck_entity
,ck_relationship
,ck_product
,ck_product_series
,ck_benefit_config
,ck_benefit
,ck_cover
,ck_cover_inc
,ck_sdt_key
,ck_rei_key
,ck_rein_ukey
,ck_reia_ukey
,ck_risk_ukey
,ck_policy_id
,ck_id
,ck_type
,ck_seq
,ck_seqn
,ck_table_no
,ck_endl
,ck_sequence
,ck_sequence_temp
,ck_current
,ck_opt_acc_ben
,ck_opt_buy_bck
,ck_opt_wop
,ck_pf_key
,ck_ben_selection
,ck_occ_cls_cod
,ck_occ_tpd_dfn_cod
,ck_smk_cod
,ck_wai_per_cod
,ck_ben_per_cod
,ck_prm_bss_cod
,ck_state
,ck_composite_key
,ck_renewal_period
,ck_rate_table_key
,ck_rate_level_years
,ck_rate_age_curr
,ck_rate_age_next
,ck_table_rate_curr
,ck_table_rate_next
,ck_prm_clc_key
,ck_prm_frq_cod
,ck_rcd_policy
,ck_rcd_benefit
,ck_date_bal_s
,ck_date_bal_e
,ck_date_bal_ds
,ck_date_bal_de
,ck_date_sys_s
,ck_date_sys_e
,ck_date_sys_ds
,ck_date_sys_de
,ck_date_eff_s
,ck_date_eff_e
,ck_date_eff_ds
,ck_date_eff_de
,ck_date_updated
,ck_sts_grp_s
,ck_sts_grp_e
,ck_sts_sys_s
,ck_sts_sys_e
,ck_sts_cod_s
,ck_sts_cod_e
,ck_sts_rea_sys_s
,ck_sts_rea_sys_e
,ck_sts_rea_cod_s
,ck_sts_rea_cod_e
,ck_sys_sts_cod
,ck_sys_sts_dsc
,ck_sys_sts_dat
,ck_risk_pk
,ck_risk_pk_p
,ck_risk_pk_n
,cm_si
,cm_si_sys_s
,cm_si_sys_e
,cm_si_sys_ms
,cm_si_sys_me
,cm_si_quo_s
,cm_si_quo_e
,cm_si_quo_ms
,cm_si_quo_me
,cm_si_pre_s
,cm_si_pre_e
,cm_si_pre_ms
,cm_si_pre_me
,cm_si_npw_s
,cm_si_npw_e
,cm_si_npw_ms
,cm_si_npw_me
,cm_si_inf_s
,cm_si_inf_e
,cm_si_inf_ms
,cm_si_inf_me
,cm_si_oof_s
,cm_si_oof_e
,cm_si_oof_ms
,cm_si_oof_me
,cm_ap
,cm_ap_sys_s
,cm_ap_sys_e
,cm_ap_sys_ms
,cm_ap_sys_me
,cm_ap_quo_s
,cm_ap_quo_e
,cm_ap_quo_ms
,cm_ap_quo_me
,cm_ap_pre_s
,cm_ap_pre_e
,cm_ap_pre_ms
,cm_ap_pre_me
,cm_ap_npw_s
,cm_ap_npw_e
,cm_ap_npw_ms
,cm_ap_npw_me
,cm_ap_inf_s
,cm_ap_inf_e
,cm_ap_inf_ms
,cm_ap_inf_me
,cm_ap_oof_s
,cm_ap_oof_e
,cm_ap_oof_ms
,cm_ap_oof_me
,cm_isd
,cm_isd_sys_s
,cm_isd_sys_e
,cm_isd_sys_ms
,cm_isd_sys_me
,cm_isd_quo_s
,cm_isd_quo_e
,cm_isd_quo_ms
,cm_isd_quo_me
,cm_isd_pre_s
,cm_isd_pre_e
,cm_isd_pre_ms
,cm_isd_pre_me
,cm_isd_npw_s
,cm_isd_npw_e
,cm_isd_npw_ms
,cm_isd_npw_me
,cm_isd_inf_s
,cm_isd_inf_e
,cm_isd_inf_ms
,cm_isd_inf_me
,cm_isd_oof_s
,cm_isd_oof_e
,cm_isd_oof_ms
,cm_isd_oof_me
,cm_esd
,cm_esd_sys_s
,cm_esd_sys_e
,cm_esd_sys_ms
,cm_esd_sys_me
,cm_esd_quo_s
,cm_esd_quo_e
,cm_esd_quo_ms
,cm_esd_quo_me
,cm_esd_pre_s
,cm_esd_pre_e
,cm_esd_pre_ms
,cm_esd_pre_me
,cm_esd_npw_s
,cm_esd_npw_e
,cm_esd_npw_ms
,cm_esd_npw_me
,cm_esd_inf_s
,cm_esd_inf_e
,cm_esd_inf_ms
,cm_esd_inf_me
,cm_esd_oof_s
,cm_esd_oof_e
,cm_esd_oof_ms
,cm_esd_oof_me
,cm_pf
,cm_pf_sys_s
,cm_pf_sys_e
,cm_pf_sys_ms
,cm_pf_sys_me
,cm_pf_quo_s
,cm_pf_quo_e
,cm_pf_quo_ms
,cm_pf_quo_me
,cm_pf_pre_s
,cm_pf_pre_e
,cm_pf_pre_ms
,cm_pf_pre_me
,cm_pf_npw_s
,cm_pf_npw_e
,cm_pf_npw_ms
,cm_pf_npw_me
,cm_pf_inf_s
,cm_pf_inf_e
,cm_pf_inf_ms
,cm_pf_inf_me
,cm_pf_oof_s
,cm_pf_oof_e
,cm_pf_oof_ms
,cm_pf_oof_me
,cm_frq_ldg
,cm_frq_ldg_sys_s
,cm_frq_ldg_sys_e
,cm_frq_ldg_sys_ms
,cm_frq_ldg_sys_me
,cm_frq_ldg_quo_s
,cm_frq_ldg_quo_e
,cm_frq_ldg_quo_ms
,cm_frq_ldg_quo_me
,cm_frq_ldg_pre_s
,cm_frq_ldg_pre_e
,cm_frq_ldg_pre_ms
,cm_frq_ldg_pre_me
,cm_frq_ldg_npw_s
,cm_frq_ldg_npw_e
,cm_frq_ldg_npw_ms
,cm_frq_ldg_npw_me
,cm_frq_ldg_inf_s
,cm_frq_ldg_inf_e
,cm_frq_ldg_inf_ms
,cm_frq_ldg_inf_me
,cm_frq_ldg_oof_s
,cm_frq_ldg_oof_e
,cm_frq_ldg_oof_ms
,cm_frq_ldg_oof_me)
SELECT
ck_risk= REPLACE(rt.ck_risk,RIGHT(rt.ck_risk,3),RIGHT('0' + CAST(rt.ck_endl - 0.5 AS VARCHAR(10)),5))
,ck_policy= rt.ck_policy
,ck_entity= rt.ck_entity
,ck_relationship= rt.ck_relationship
,ck_product= rt.ck_product
,ck_product_series= rt.ck_product_series
,ck_benefit_config= rt.ck_benefit_config
,ck_benefit= rt.ck_benefit
,ck_cover= rt.ck_cover
,ck_cover_inc= rt.ck_cover_inc
,ck_sdt_key= rt.ck_sdt_key
,ck_rei_key= rt.ck_rei_key
,ck_rein_ukey= rt.ck_rein_ukey
,ck_reia_ukey= rt.ck_reia_ukey
,ck_risk_ukey= rt.ck_risk_ukey
,ck_policy_id= rt.ck_policy_id
,ck_id= rt.ck_id
,ck_type= rt.ck_type
,ck_seq= rt.ck_seq
,ck_seqn= rt.ck_seqn
,ck_table_no= rt.ck_table_no
,ck_endl= rt.ck_endl - 0.5
,ck_sequence= rt.ck_sequence
,ck_sequence_temp= rt.ck_sequence_temp + 0.5
,ck_current= rt.ck_current
,ck_opt_acc_ben= rt.ck_opt_acc_ben
,ck_opt_buy_bck= rt.ck_opt_buy_bck
,ck_opt_wop= rt.ck_opt_wop
,ck_pf_key= rt.ck_pf_key
,ck_ben_selection= rt.ck_ben_selection
,ck_occ_cls_cod= rt.ck_occ_cls_cod
,ck_occ_tpd_dfn_cod= rt.ck_occ_tpd_dfn_cod
,ck_smk_cod= rt.ck_smk_cod
,ck_wai_per_cod= rt.ck_wai_per_cod
,ck_ben_per_cod= rt.ck_ben_per_cod
,ck_prm_bss_cod= rt.ck_prm_bss_cod
,ck_state= rt.ck_state
,ck_composite_key= rt.ck_composite_key
,ck_renewal_period= rt.ck_renewal_period
,ck_rate_table_key= rt.ck_rate_table_key
,ck_rate_level_years= rt.ck_rate_level_years
,ck_rate_age_curr= rt.ck_rate_age_curr
,ck_rate_age_next= rt.ck_rate_age_next
,ck_table_rate_curr= rt.ck_table_rate_curr
,ck_table_rate_next= rt.ck_table_rate_next
,ck_prm_clc_key= rt.ck_prm_clc_key
,ck_prm_frq_cod= rt.ck_prm_frq_cod
,ck_rcd_policy= rt.ck_rcd_policy
,ck_rcd_benefit= rt.ck_rcd_benefit
,ck_date_bal_s= rt.ck_date_bal_e
,ck_date_bal_e= x.ck_date_bal_s
,ck_date_bal_ds= 0
,ck_date_bal_de= x.ck_date_bal_ds
,ck_date_sys_s= rt.ck_date_sys_e
,ck_date_sys_e= x.ck_date_sys_s
,ck_date_sys_ds= 0
,ck_date_sys_de= x.ck_date_sys_ds
,ck_date_eff_s= rt.ck_date_eff_e
,ck_date_eff_e= x.ck_date_eff_s
,ck_date_eff_ds= 0
,ck_date_eff_de= x.ck_date_eff_ds
,ck_date_updated= rt.ck_date_updated
,ck_sts_grp_s= rt.ck_sts_grp_e
,ck_sts_grp_e= x.ck_sts_grp_s
,ck_sts_sys_s= rt.ck_sts_sys_e
,ck_sts_sys_e= x.ck_sts_sys_s
,ck_sts_cod_s= rt.ck_sts_cod_e
,ck_sts_cod_e= x.ck_sts_cod_s
,ck_sts_rea_sys_s= rt.ck_sts_rea_sys_e
,ck_sts_rea_sys_e= x.ck_sts_rea_sys_s
,ck_sts_rea_cod_s= rt.ck_sts_rea_cod_e
,ck_sts_rea_cod_e= x.ck_sts_rea_cod_s
,ck_sys_sts_cod= rt.ck_sys_sts_cod
,ck_sys_sts_dsc= rt.ck_sys_sts_dsc
,ck_sys_sts_dat= rt.ck_sys_sts_dat
,ck_risk_pk= rt.ck_risk_pk + 0.5
,ck_risk_pk_p= rt.ck_risk_pk
,ck_risk_pk_n= rt.ck_risk_pk_n
,cm_si= rt.cm_si
,cm_si_sys_s= 0
,cm_si_sys_e= 0
,cm_si_sys_ms= 0
,cm_si_sys_me= 0
,cm_si_quo_s= 0
,cm_si_quo_e= 0
,cm_si_quo_ms= 0
,cm_si_quo_me= 0
,cm_si_pre_s= 0
,cm_si_pre_e= 0
,cm_si_pre_ms= 0
,cm_si_pre_me= 0
,cm_si_npw_s= 0
,cm_si_npw_e= 0
,cm_si_npw_ms= 0
,cm_si_npw_me= 0
,cm_si_inf_s= 0
,cm_si_inf_e= 0
,cm_si_inf_ms= 0
,cm_si_inf_me= 0
,cm_si_oof_s= 0
,cm_si_oof_e= 0
,cm_si_oof_ms= 0
,cm_si_oof_me= 0
,cm_ap= rt.cm_ap
,cm_ap_sys_s= 0
,cm_ap_sys_e= 0
,cm_ap_sys_ms= 0
,cm_ap_sys_me= 0
,cm_ap_quo_s= 0
,cm_ap_quo_e= 0
,cm_ap_quo_ms= 0
,cm_ap_quo_me= 0
,cm_ap_pre_s= 0
,cm_ap_pre_e= 0
,cm_ap_pre_ms= 0
,cm_ap_pre_me= 0
,cm_ap_npw_s= 0
,cm_ap_npw_e= 0
,cm_ap_npw_ms= 0
,cm_ap_npw_me= 0
,cm_ap_inf_s= 0
,cm_ap_inf_e= 0
,cm_ap_inf_ms= 0
,cm_ap_inf_me= 0
,cm_ap_oof_s= 0
,cm_ap_oof_e= 0
,cm_ap_oof_ms= 0
,cm_ap_oof_me= 0
,cm_isd= rt.cm_isd
,cm_isd_sys_s= 0
,cm_isd_sys_e= 0
,cm_isd_sys_ms= 0
,cm_isd_sys_me= 0
,cm_isd_quo_s= 0
,cm_isd_quo_e= 0
,cm_isd_quo_ms= 0
,cm_isd_quo_me= 0
,cm_isd_pre_s= 0
,cm_isd_pre_e= 0
,cm_isd_pre_ms= 0
,cm_isd_pre_me= 0
,cm_isd_npw_s= 0
,cm_isd_npw_e= 0
,cm_isd_npw_ms= 0
,cm_isd_npw_me= 0
,cm_isd_inf_s= 0
,cm_isd_inf_e= 0
,cm_isd_inf_ms= 0
,cm_isd_inf_me= 0
,cm_isd_oof_s= 0
,cm_isd_oof_e= 0
,cm_isd_oof_ms= 0
,cm_isd_oof_me= 0
,cm_esd= rt.cm_esd
,cm_esd_sys_s= 0
,cm_esd_sys_e= 0
,cm_esd_sys_ms= 0
,cm_esd_sys_me= 0
,cm_esd_quo_s= 0
,cm_esd_quo_e= 0
,cm_esd_quo_ms= 0
,cm_esd_quo_me= 0
,cm_esd_pre_s= 0
,cm_esd_pre_e= 0
,cm_esd_pre_ms= 0
,cm_esd_pre_me= 0
,cm_esd_npw_s= 0
,cm_esd_npw_e= 0
,cm_esd_npw_ms= 0
,cm_esd_npw_me= 0
,cm_esd_inf_s= 0
,cm_esd_inf_e= 0
,cm_esd_inf_ms= 0
,cm_esd_inf_me= 0
,cm_esd_oof_s= 0
,cm_esd_oof_e= 0
,cm_esd_oof_ms= 0
,cm_esd_oof_me= 0
,cm_pf= rt.cm_pf
,cm_pf_sys_s= 0
,cm_pf_sys_e= 0
,cm_pf_sys_ms= 0
,cm_pf_sys_me= 0
,cm_pf_quo_s= 0
,cm_pf_quo_e= 0
,cm_pf_quo_ms= 0
,cm_pf_quo_me= 0
,cm_pf_pre_s= 0
,cm_pf_pre_e= 0
,cm_pf_pre_ms= 0
,cm_pf_pre_me= 0
,cm_pf_npw_s= 0
,cm_pf_npw_e= 0
,cm_pf_npw_ms= 0
,cm_pf_npw_me= 0
,cm_pf_inf_s= 0
,cm_pf_inf_e= 0
,cm_pf_inf_ms= 0
,cm_pf_inf_me= 0
,cm_pf_oof_s= 0
,cm_pf_oof_e= 0
,cm_pf_oof_ms= 0
,cm_pf_oof_me= 0
,cm_frq_ldg= rt.cm_frq_ldg
,cm_frq_ldg_sys_s= 0
,cm_frq_ldg_sys_e= 0
,cm_frq_ldg_sys_ms= 0
,cm_frq_ldg_sys_me= 0
,cm_frq_ldg_quo_s= 0
,cm_frq_ldg_quo_e= 0
,cm_frq_ldg_quo_ms= 0
,cm_frq_ldg_quo_me= 0
,cm_frq_ldg_pre_s= 0
,cm_frq_ldg_pre_e= 0
,cm_frq_ldg_pre_ms= 0
,cm_frq_ldg_pre_me= 0
,cm_frq_ldg_npw_s= 0
,cm_frq_ldg_npw_e= 0
,cm_frq_ldg_npw_ms= 0
,cm_frq_ldg_npw_me= 0
,cm_frq_ldg_inf_s= 0
,cm_frq_ldg_inf_e= 0
,cm_frq_ldg_inf_ms= 0
,cm_frq_ldg_inf_me= 0
,cm_frq_ldg_oof_s= 0
,cm_frq_ldg_oof_e= 0
,cm_frq_ldg_oof_ms= 0
,cm_frq_ldg_oof_me= 0
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
(
SELECT
ck_cover_inc
,ck_sequence_temp
,ck_date_bal_s
,ck_date_bal_ds
,ck_date_sys_s
,ck_date_sys_ds
,ck_date_eff_s
,ck_date_eff_ds
,ck_sts_grp_s
,ck_sts_sys_s
,ck_sts_cod_s
,ck_sts_rea_sys_s
,ck_sts_rea_cod_s
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_date_bal_ds > 0
)x
ONrt.ck_cover_inc = x.ck_cover_inc
ANDrt.ck_sequence_temp = x.ck_sequence_temp - 1
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_date_bal_ds= 0
,ck_date_sys_ds= 0
,ck_date_eff_ds= 0
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_cover_inc + CAST(ck_sequence_temp AS VARCHAR(20)) IN
(
SELECT
ck_cover_inc + CAST(ck_sequence_temp + 0.5 AS VARCHAR(20))
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDRIGHT(ck_risk,2) = '.5'
)
----------
-- Re-Set Sequence
----------
SET @seq = 1
WHILE@seq <= 100
BEGIN
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sequence= @seq
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagt
JOIN
(
SELECT
ck_cover_inc
,min_seq= MIN(ck_sequence_temp)
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_sequence = 0
GROUP BY
ck_cover_inc
)x
ONt.ck_cover_inc = x.ck_cover_inc
ANDt.ck_sequence_temp = x.min_seq
-----
SET @seq = @seq + 1
-----
END
----------
-- Re-Set RISK Key
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_risk= 'EBX-' + CAST(ck_policy_id AS VARCHAR(50))
+ '-' + CAST(ck_id AS VARCHAR(50))
+ '-' + RTRIM(LTRIM(ck_type))
+ '-' + CAST(ck_seq AS VARCHAR(50))
+ '-' + RTRIM(LTRIM(ck_table_no))
+ '-' + RIGHT('00' + CAST(ck_sequence AS VARCHAR(50)),3)
----------
-- Re-Set Prior / Next PK
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_risk_pk_p= p.ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_cover_inc
,ck_sequence
,ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)p
ONc.ck_cover_inc = p.ck_cover_inc
ANDc.ck_sequence = p.ck_sequence + 1
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_risk_pk_n= n.ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_cover_inc
,ck_sequence
,ck_risk_pk
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)n
ONc.ck_cover_inc = n.ck_cover_inc
ANDc.ck_sequence = n.ck_sequence - 1
----------
-- Update Current Flag
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_current= CASE
WHENck_date_bal_s <= @balance_date
ANDck_date_bal_e > @balance_date
THEN'C'
WHENck_date_bal_s <= @balance_date
ANDck_date_bal_e <= @balance_date
THEN'H'
WHENck_date_bal_s > @balance_date
ANDck_date_bal_e > @balance_date
THEN'F'
ELSE'Z'
END
----------
-- Insert Closing NPW / OOF Records
----------
INSERT INTO dbo.utb_ebx_risk__tag
(ck_risk
,ck_policy
,ck_entity
,ck_relationship
,ck_product
,ck_product_series
,ck_benefit_config
,ck_benefit
,ck_cover
,ck_cover_inc
,ck_sdt_key
,ck_rei_key
,ck_rein_ukey
,ck_reia_ukey
,ck_risk_ukey
,ck_policy_id
,ck_id
,ck_type
,ck_seq
,ck_seqn
,ck_table_no
,ck_endl
,ck_sequence
,ck_sequence_temp
,ck_current
,ck_opt_acc_ben
,ck_opt_buy_bck
,ck_opt_wop
,ck_pf_key
,ck_ben_selection
,ck_occ_cls_cod
,ck_occ_tpd_dfn_cod
,ck_smk_cod
,ck_wai_per_cod
,ck_ben_per_cod
,ck_prm_bss_cod
,ck_state
,ck_composite_key
,ck_renewal_period
,ck_rate_table_key
,ck_rate_level_years
,ck_rate_age_curr
,ck_rate_age_next
,ck_table_rate_curr
,ck_table_rate_next
,ck_prm_clc_key
,ck_prm_frq_cod
,ck_rcd_policy
,ck_rcd_benefit
,ck_date_bal_s
,ck_date_bal_e
,ck_date_bal_ds
,ck_date_bal_de
,ck_date_sys_s
,ck_date_sys_e
,ck_date_sys_ds
,ck_date_sys_de
,ck_date_eff_s
,ck_date_eff_e
,ck_date_eff_ds
,ck_date_eff_de
,ck_date_updated
,ck_sts_grp_s
,ck_sts_grp_e
,ck_sts_sys_s
,ck_sts_sys_e
,ck_sts_cod_s
,ck_sts_cod_e
,ck_sts_rea_sys_s
,ck_sts_rea_sys_e
,ck_sts_rea_cod_s
,ck_sts_rea_cod_e
,ck_sys_sts_cod
,ck_sys_sts_dsc
,ck_sys_sts_dat
,ck_risk_pk
,ck_risk_pk_p
,ck_risk_pk_n
,cm_si
,cm_si_sys_s
,cm_si_sys_e
,cm_si_sys_ms
,cm_si_sys_me
,cm_si_quo_s
,cm_si_quo_e
,cm_si_quo_ms
,cm_si_quo_me
,cm_si_pre_s
,cm_si_pre_e
,cm_si_pre_ms
,cm_si_pre_me
,cm_si_npw_s
,cm_si_npw_e
,cm_si_npw_ms
,cm_si_npw_me
,cm_si_inf_s
,cm_si_inf_e
,cm_si_inf_ms
,cm_si_inf_me
,cm_si_oof_s
,cm_si_oof_e
,cm_si_oof_ms
,cm_si_oof_me
,cm_ap
,cm_ap_sys_s
,cm_ap_sys_e
,cm_ap_sys_ms
,cm_ap_sys_me
,cm_ap_quo_s
,cm_ap_quo_e
,cm_ap_quo_ms
,cm_ap_quo_me
,cm_ap_pre_s
,cm_ap_pre_e
,cm_ap_pre_ms
,cm_ap_pre_me
,cm_ap_npw_s
,cm_ap_npw_e
,cm_ap_npw_ms
,cm_ap_npw_me
,cm_ap_inf_s
,cm_ap_inf_e
,cm_ap_inf_ms
,cm_ap_inf_me
,cm_ap_oof_s
,cm_ap_oof_e
,cm_ap_oof_ms
,cm_ap_oof_me
,cm_isd
,cm_isd_sys_s
,cm_isd_sys_e
,cm_isd_sys_ms
,cm_isd_sys_me
,cm_isd_quo_s
,cm_isd_quo_e
,cm_isd_quo_ms
,cm_isd_quo_me
,cm_isd_pre_s
,cm_isd_pre_e
,cm_isd_pre_ms
,cm_isd_pre_me
,cm_isd_npw_s
,cm_isd_npw_e
,cm_isd_npw_ms
,cm_isd_npw_me
,cm_isd_inf_s
,cm_isd_inf_e
,cm_isd_inf_ms
,cm_isd_inf_me
,cm_isd_oof_s
,cm_isd_oof_e
,cm_isd_oof_ms
,cm_isd_oof_me
,cm_esd
,cm_esd_sys_s
,cm_esd_sys_e
,cm_esd_sys_ms
,cm_esd_sys_me
,cm_esd_quo_s
,cm_esd_quo_e
,cm_esd_quo_ms
,cm_esd_quo_me
,cm_esd_pre_s
,cm_esd_pre_e
,cm_esd_pre_ms
,cm_esd_pre_me
,cm_esd_npw_s
,cm_esd_npw_e
,cm_esd_npw_ms
,cm_esd_npw_me
,cm_esd_inf_s
,cm_esd_inf_e
,cm_esd_inf_ms
,cm_esd_inf_me
,cm_esd_oof_s
,cm_esd_oof_e
,cm_esd_oof_ms
,cm_esd_oof_me
,cm_pf
,cm_pf_sys_s
,cm_pf_sys_e
,cm_pf_sys_ms
,cm_pf_sys_me
,cm_pf_quo_s
,cm_pf_quo_e
,cm_pf_quo_ms
,cm_pf_quo_me
,cm_pf_pre_s
,cm_pf_pre_e
,cm_pf_pre_ms
,cm_pf_pre_me
,cm_pf_npw_s
,cm_pf_npw_e
,cm_pf_npw_ms
,cm_pf_npw_me
,cm_pf_inf_s
,cm_pf_inf_e
,cm_pf_inf_ms
,cm_pf_inf_me
,cm_pf_oof_s
,cm_pf_oof_e
,cm_pf_oof_ms
,cm_pf_oof_me
,cm_frq_ldg
,cm_frq_ldg_sys_s
,cm_frq_ldg_sys_e
,cm_frq_ldg_sys_ms
,cm_frq_ldg_sys_me
,cm_frq_ldg_quo_s
,cm_frq_ldg_quo_e
,cm_frq_ldg_quo_ms
,cm_frq_ldg_quo_me
,cm_frq_ldg_pre_s
,cm_frq_ldg_pre_e
,cm_frq_ldg_pre_ms
,cm_frq_ldg_pre_me
,cm_frq_ldg_npw_s
,cm_frq_ldg_npw_e
,cm_frq_ldg_npw_ms
,cm_frq_ldg_npw_me
,cm_frq_ldg_inf_s
,cm_frq_ldg_inf_e
,cm_frq_ldg_inf_ms
,cm_frq_ldg_inf_me
,cm_frq_ldg_oof_s
,cm_frq_ldg_oof_e
,cm_frq_ldg_oof_ms
,cm_frq_ldg_oof_me)
SELECT
ck_risk= 'EBX-' + CAST(ck_policy_id AS VARCHAR(50))
+ '-' + CAST(ck_id AS VARCHAR(50))
+ '-' + RTRIM(LTRIM(ck_type))
+ '-' + CAST(ck_seq AS VARCHAR(50))
+ '-' + RTRIM(LTRIM(ck_table_no))
+ '-' + RIGHT('00' + CAST(ck_sequence + 1 AS VARCHAR(50)),3)
,ck_policy
,ck_entity
,ck_relationship
,ck_product
,ck_product_series
,ck_benefit_config
,ck_benefit
,ck_cover
,ck_cover_inc
,ck_sdt_key
,ck_rei_key
,ck_rein_ukey
,ck_reia_ukey
,ck_risk_ukey
,ck_policy_id
,ck_id
,ck_type
,ck_seq
,ck_seqn
,ck_table_no
,ck_endl
,ck_sequence= ck_sequence + 1
,ck_sequence_temp= ck_sequence_temp + 1
,ck_current= 'C'
,ck_opt_acc_ben
,ck_opt_buy_bck
,ck_opt_wop
,ck_pf_key
,ck_ben_selection
,ck_occ_cls_cod
,ck_occ_tpd_dfn_cod
,ck_smk_cod
,ck_wai_per_cod
,ck_ben_per_cod
,ck_prm_bss_cod
,ck_state
,ck_composite_key
,ck_renewal_period
,ck_rate_table_key
,ck_rate_level_years
,ck_rate_age_curr
,ck_rate_age_next
,ck_table_rate_curr
,ck_table_rate_next
,ck_prm_clc_key
,ck_prm_frq_cod
,ck_rcd_policy
,ck_rcd_benefit
,ck_date_bal_s= ck_date_bal_e
,ck_date_bal_e= '3000-01-01'
,ck_date_bal_ds= 0
,ck_date_bal_de= 0
,ck_date_sys_s= ck_date_sys_e
,ck_date_sys_e= '3000-01-01'
,ck_date_sys_ds= 0
,ck_date_sys_de= 0
,ck_date_eff_s= ck_date_eff_e
,ck_date_eff_e= '3000-01-01'
,ck_date_eff_ds= 0
,ck_date_eff_de= 0
,ck_date_updated
,ck_sts_grp_s= ck_sts_grp_e
,ck_sts_grp_e
,ck_sts_sys_s= ck_sts_sys_e
,ck_sts_sys_e
,ck_sts_cod_s= ck_sts_cod_e
,ck_sts_cod_e
,ck_sts_rea_sys_s= ck_sts_rea_sys_e
,ck_sts_rea_sys_e
,ck_sts_rea_cod_s= ck_sts_rea_cod_e
,ck_sts_rea_cod_e
,ck_sys_sts_cod
,ck_sys_sts_dsc
,ck_sys_sts_dat
,ck_risk_pk= ck_risk_pk * -1
,ck_risk_pk_p= ck_risk_pk
,ck_risk_pk_n
,cm_si
,cm_si_sys_s= cm_si_sys_e
,cm_si_sys_e
,cm_si_sys_ms= 0
,cm_si_sys_me= 0
,cm_si_quo_s= cm_si_quo_e
,cm_si_quo_e
,cm_si_quo_ms= 0
,cm_si_quo_me= 0
,cm_si_pre_s= cm_si_pre_e
,cm_si_pre_e
,cm_si_pre_ms= 0
,cm_si_pre_me= 0
,cm_si_npw_s= cm_si_npw_e
,cm_si_npw_e
,cm_si_npw_ms= 0
,cm_si_npw_me= 0
,cm_si_inf_s= cm_si_inf_e
,cm_si_inf_e
,cm_si_inf_ms= 0
,cm_si_inf_me= 0
,cm_si_oof_s= cm_si_oof_e
,cm_si_oof_e
,cm_si_oof_ms= 0
,cm_si_oof_me= 0
,cm_ap
,cm_ap_sys_s= cm_ap_sys_e
,cm_ap_sys_e
,cm_ap_sys_ms= 0
,cm_ap_sys_me= 0
,cm_ap_quo_s= cm_ap_quo_e
,cm_ap_quo_e
,cm_ap_quo_ms= 0
,cm_ap_quo_me= 0
,cm_ap_pre_s= cm_ap_pre_e
,cm_ap_pre_e
,cm_ap_pre_ms= 0
,cm_ap_pre_me= 0
,cm_ap_npw_s= cm_ap_npw_e
,cm_ap_npw_e
,cm_ap_npw_ms= 0
,cm_ap_npw_me= 0
,cm_ap_inf_s= cm_ap_inf_e
,cm_ap_inf_e
,cm_ap_inf_ms= 0
,cm_ap_inf_me= 0
,cm_ap_oof_s= cm_ap_oof_e
,cm_ap_oof_e
,cm_ap_oof_ms= 0
,cm_ap_oof_me= 0
,cm_isd
,cm_isd_sys_s= cm_isd_sys_e
,cm_isd_sys_e
,cm_isd_sys_ms= 0
,cm_isd_sys_me= 0
,cm_isd_quo_s= cm_isd_quo_e
,cm_isd_quo_e
,cm_isd_quo_ms= 0
,cm_isd_quo_me= 0
,cm_isd_pre_s= cm_isd_pre_e
,cm_isd_pre_e
,cm_isd_pre_ms= 0
,cm_isd_pre_me= 0
,cm_isd_npw_s= cm_isd_npw_e
,cm_isd_npw_e
,cm_isd_npw_ms= 0
,cm_isd_npw_me= 0
,cm_isd_inf_s= cm_isd_inf_e
,cm_isd_inf_e
,cm_isd_inf_ms= 0
,cm_isd_inf_me= 0
,cm_isd_oof_s= cm_isd_oof_e
,cm_isd_oof_e
,cm_isd_oof_ms= 0
,cm_isd_oof_me= 0
,cm_esd
,cm_esd_sys_s= cm_esd_sys_e
,cm_esd_sys_e
,cm_esd_sys_ms= 0
,cm_esd_sys_me= 0
,cm_esd_quo_s= cm_esd_quo_e
,cm_esd_quo_e
,cm_esd_quo_ms= 0
,cm_esd_quo_me= 0
,cm_esd_pre_s= cm_esd_pre_e
,cm_esd_pre_e
,cm_esd_pre_ms= 0
,cm_esd_pre_me= 0
,cm_esd_npw_s= cm_esd_npw_e
,cm_esd_npw_e
,cm_esd_npw_ms= 0
,cm_esd_npw_me= 0
,cm_esd_inf_s= cm_esd_inf_e
,cm_esd_inf_e
,cm_esd_inf_ms= 0
,cm_esd_inf_me= 0
,cm_esd_oof_s= cm_esd_oof_e
,cm_esd_oof_e
,cm_esd_oof_ms= 0
,cm_esd_oof_me= 0
,cm_pf
,cm_pf_sys_s= cm_pf_sys_e
,cm_pf_sys_e
,cm_pf_sys_ms= 0
,cm_pf_sys_me= 0
,cm_pf_quo_s= cm_pf_quo_e
,cm_pf_quo_e
,cm_pf_quo_ms= 0
,cm_pf_quo_me= 0
,cm_pf_pre_s= cm_pf_pre_e
,cm_pf_pre_e
,cm_pf_pre_ms= 0
,cm_pf_pre_me= 0
,cm_pf_npw_s= cm_pf_npw_e
,cm_pf_npw_e
,cm_pf_npw_ms= 0
,cm_pf_npw_me= 0
,cm_pf_inf_s= cm_pf_inf_e
,cm_pf_inf_e
,cm_pf_inf_ms= 0
,cm_pf_inf_me= 0
,cm_pf_oof_s= cm_pf_oof_e
,cm_pf_oof_e
,cm_pf_oof_ms= 0
,cm_pf_oof_me= 0
,cm_frq_ldg
,cm_frq_ldg_sys_s= cm_frq_ldg_sys_e
,cm_frq_ldg_sys_e
,cm_frq_ldg_sys_ms= 0
,cm_frq_ldg_sys_me= 0
,cm_frq_ldg_quo_s= cm_frq_ldg_quo_e
,cm_frq_ldg_quo_e
,cm_frq_ldg_quo_ms= 0
,cm_frq_ldg_quo_me= 0
,cm_frq_ldg_pre_s= cm_frq_ldg_pre_e
,cm_frq_ldg_pre_e
,cm_frq_ldg_pre_ms= 0
,cm_frq_ldg_pre_me= 0
,cm_frq_ldg_npw_s= cm_frq_ldg_npw_e
,cm_frq_ldg_npw_e
,cm_frq_ldg_npw_ms= 0
,cm_frq_ldg_npw_me= 0
,cm_frq_ldg_inf_s= cm_frq_ldg_inf_e
,cm_frq_ldg_inf_e
,cm_frq_ldg_inf_ms= 0
,cm_frq_ldg_inf_me= 0
,cm_frq_ldg_oof_s= cm_frq_ldg_oof_e
,cm_frq_ldg_oof_e
,cm_frq_ldg_oof_ms= 0
,cm_frq_ldg_oof_me= 0
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_endl = 0
ANDck_current = 'H'
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_risk_pk_n= ck_risk_pk * -1
WHERE1=1
ANDck_risk_pk_n = 0
ANDck_current = 'H'
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_current= 'H'
WHERE1=1
ANDck_risk IN
(
SELECT
MIN(ck_risk)
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
WHERE1=1
ANDck_current = 'C'
GROUP BY
ck_cover_inc
HAVINGCOUNT(*) > 1
)
----------
-- Apply EDW Statuses
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sts_cod_s= s.status_code
,ck_sts_rea_cod_s= s.status_reason_code
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_INP.dbo.utb_ref_valn_statuss
ONrt.ck_sts_sys_s = s.stat
ANDrt.ck_sts_rea_sys_s = s.dres
WHERE1=1
ANDs.table_name = 'risk'
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sts_cod_s= s.status_code
,ck_sts_rea_cod_s= s.status_reason_code
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_INP.dbo.utb_ref_valn_statuss
ONrt.ck_sts_sys_s = s.stat
WHERE1=1
ANDs.table_name = 'risk'
ANDs.dres = 'ZZ'
ANDrt.ck_sts_cod_s = 'ZZZ'
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sts_cod_e= s.status_code
,ck_sts_rea_cod_e= s.status_reason_code
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_INP.dbo.utb_ref_valn_statuss
ONrt.ck_sts_sys_e = s.stat
ANDrt.ck_sts_rea_sys_e = s.dres
WHERE1=1
ANDs.table_name = 'risk'
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
ck_sts_cod_e= s.status_code
,ck_sts_rea_cod_e= s.status_reason_code
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagrt
JOIN
EDW_INP.dbo.utb_ref_valn_statuss
ONrt.ck_sts_sys_e = s.stat
WHERE1=1
ANDs.table_name = 'risk'
ANDs.dres = 'ZZ'
ANDrt.ck_sts_cod_e = 'ZZZ'
----------
-- Generate Measures
----------
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
cm_si_sys_s= cm_si
,cm_si_sys_e= cm_si
,cm_si_quo_s= CASE
WHENck_sts_grp_s = 'QUO'
THENcm_si
ELSE0
END
,cm_si_quo_e= CASE
WHENck_sts_grp_e = 'QUO'
THENcm_si
ELSE0
END
,cm_si_pre_s= CASE
WHENck_sts_grp_s = 'PRE'
THENcm_si
ELSE0
END
,cm_si_pre_e= CASE
WHENck_sts_grp_e = 'PRE'
THENcm_si
ELSE0
END
,cm_si_npw_s= CASE
WHENck_sts_grp_s = 'NPW'
THENcm_si
ELSE0
END
,cm_si_npw_e= CASE
WHENck_sts_grp_e = 'NPW'
THENcm_si
ELSE0
END
,cm_si_inf_s= CASE
WHENck_sts_grp_s = 'INF'
THENcm_si
ELSE0
END
,cm_si_inf_e= CASE
WHENck_sts_grp_e = 'INF'
THENcm_si
ELSE0
END
,cm_si_oof_s= CASE
WHENck_sts_grp_s = 'OOF'
THENcm_si
ELSE0
END
,cm_si_oof_e= CASE
WHENck_sts_grp_e = 'OOF'
THENcm_si
ELSE0
END
,cm_ap_sys_s= cm_ap
,cm_ap_sys_e= cm_ap
,cm_ap_quo_s= CASE
WHENck_sts_grp_s = 'QUO'
THENcm_ap
ELSE0
END
,cm_ap_quo_e= CASE
WHENck_sts_grp_e = 'QUO'
THENcm_ap
ELSE0
END
,cm_ap_pre_s= CASE
WHENck_sts_grp_s = 'PRE'
THENcm_ap
ELSE0
END
,cm_ap_pre_e= CASE
WHENck_sts_grp_e = 'PRE'
THENcm_ap
ELSE0
END
,cm_ap_npw_s= CASE
WHENck_sts_grp_s = 'NPW'
THENcm_ap
ELSE0
END
,cm_ap_npw_e= CASE
WHENck_sts_grp_e = 'NPW'
THENcm_ap
ELSE0
END
,cm_ap_inf_s= CASE
WHENck_sts_grp_s = 'INF'
THENcm_ap
ELSE0
END
,cm_ap_inf_e= CASE
WHENck_sts_grp_e = 'INF'
THENcm_ap
ELSE0
END
,cm_ap_oof_s= CASE
WHENck_sts_grp_s = 'OOF'
THENcm_ap
ELSE0
END
,cm_ap_oof_e= CASE
WHENck_sts_grp_e = 'OOF'
THENcm_ap
ELSE0
END
,cm_isd_sys_s= cm_isd
,cm_isd_sys_e= cm_isd
,cm_isd_quo_s= CASE
WHENck_sts_grp_s = 'QUO'
THENcm_isd
ELSE0
END
,cm_isd_quo_e= CASE
WHENck_sts_grp_e = 'QUO'
THENcm_isd
ELSE0
END
,cm_isd_pre_s= CASE
WHENck_sts_grp_s = 'PRE'
THENcm_isd
ELSE0
END
,cm_isd_pre_e= CASE
WHENck_sts_grp_e = 'PRE'
THENcm_isd
ELSE0
END
,cm_isd_npw_s= CASE
WHENck_sts_grp_s = 'NPW'
THENcm_isd
ELSE0
END
,cm_isd_npw_e= CASE
WHENck_sts_grp_e = 'NPW'
THENcm_isd
ELSE0
END
,cm_isd_inf_s= CASE
WHENck_sts_grp_s = 'INF'
THENcm_isd
ELSE0
END
,cm_isd_inf_e= CASE
WHENck_sts_grp_e = 'INF'
THENcm_isd
ELSE0
END
,cm_isd_oof_s= CASE
WHENck_sts_grp_s = 'OOF'
THENcm_isd
ELSE0
END
,cm_isd_oof_e= CASE
WHENck_sts_grp_e = 'OOF'
THENcm_isd
ELSE0
END
,cm_esd_sys_s= cm_esd
,cm_esd_sys_e= cm_esd
,cm_esd_quo_s= CASE
WHENck_sts_grp_s = 'QUO'
THENcm_esd
ELSE0
END
,cm_esd_quo_e= CASE
WHENck_sts_grp_e = 'QUO'
THENcm_esd
ELSE0
END
,cm_esd_pre_s= CASE
WHENck_sts_grp_s = 'PRE'
THENcm_esd
ELSE0
END
,cm_esd_pre_e= CASE
WHENck_sts_grp_e = 'PRE'
THENcm_esd
ELSE0
END
,cm_esd_npw_s= CASE
WHENck_sts_grp_s = 'NPW'
THENcm_esd
ELSE0
END
,cm_esd_npw_e= CASE
WHENck_sts_grp_e = 'NPW'
THENcm_esd
ELSE0
END
,cm_esd_inf_s= CASE
WHENck_sts_grp_s = 'INF'
THENcm_esd
ELSE0
END
,cm_esd_inf_e= CASE
WHENck_sts_grp_e = 'INF'
THENcm_esd
ELSE0
END
,cm_esd_oof_s= CASE
WHENck_sts_grp_s = 'OOF'
THENcm_esd
ELSE0
END
,cm_esd_oof_e= CASE
WHENck_sts_grp_e = 'OOF'
THENcm_esd
ELSE0
END
,cm_pf_sys_s= cm_pf
,cm_pf_sys_e= cm_pf
,cm_pf_quo_s= CASE
WHENck_sts_grp_s = 'QUO'
THENcm_pf
ELSE0
END
,cm_pf_quo_e= CASE
WHENck_sts_grp_e = 'QUO'
THENcm_pf
ELSE0
END
,cm_pf_pre_s= CASE
WHENck_sts_grp_s = 'PRE'
THENcm_pf
ELSE0
END
,cm_pf_pre_e= CASE
WHENck_sts_grp_e = 'PRE'
THENcm_pf
ELSE0
END
,cm_pf_npw_s= CASE
WHENck_sts_grp_s = 'NPW'
THENcm_pf
ELSE0
END
,cm_pf_npw_e= CASE
WHENck_sts_grp_e = 'NPW'
THENcm_pf
ELSE0
END
,cm_pf_inf_s= CASE
WHENck_sts_grp_s = 'INF'
THENcm_pf
ELSE0
END
,cm_pf_inf_e= CASE
WHENck_sts_grp_e = 'INF'
THENcm_pf
ELSE0
END
,cm_pf_oof_s= CASE
WHENck_sts_grp_s = 'OOF'
THENcm_pf
ELSE0
END
,cm_pf_oof_e= CASE
WHENck_sts_grp_e = 'OOF'
THENcm_pf
ELSE0
END
,cm_frq_ldg_sys_s= cm_frq_ldg
,cm_frq_ldg_sys_e= cm_frq_ldg
,cm_frq_ldg_quo_s= CASE
WHENck_sts_grp_s = 'QUO'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_quo_e= CASE
WHENck_sts_grp_e = 'QUO'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_pre_s= CASE
WHENck_sts_grp_s = 'PRE'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_pre_e= CASE
WHENck_sts_grp_e = 'PRE'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_npw_s= CASE
WHENck_sts_grp_s = 'NPW'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_npw_e= CASE
WHENck_sts_grp_e = 'NPW'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_inf_s= CASE
WHENck_sts_grp_s = 'INF'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_inf_e= CASE
WHENck_sts_grp_e = 'INF'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_oof_s= CASE
WHENck_sts_grp_s = 'OOF'
THENcm_frq_ldg
ELSE0
END
,cm_frq_ldg_oof_e= CASE
WHENck_sts_grp_e = 'OOF'
THENcm_frq_ldg
ELSE0
END
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
cm_si_sys_me= cm_si_sys_e - cm_si_sys_s
,cm_si_quo_me= cm_si_quo_e - cm_si_quo_s
,cm_si_pre_me= cm_si_pre_e - cm_si_pre_s
,cm_si_npw_me= cm_si_npw_e - cm_si_npw_s
,cm_si_inf_me= cm_si_inf_e - cm_si_inf_s
,cm_si_oof_me= cm_si_oof_e - cm_si_oof_s
,cm_ap_sys_me= cm_ap_sys_e - cm_ap_sys_s
,cm_ap_quo_me= cm_ap_quo_e - cm_ap_quo_s
,cm_ap_pre_me= cm_ap_pre_e - cm_ap_pre_s
,cm_ap_npw_me= cm_ap_npw_e - cm_ap_npw_s
,cm_ap_inf_me= cm_ap_inf_e - cm_ap_inf_s
,cm_ap_oof_me= cm_ap_oof_e - cm_ap_oof_s
,cm_isd_sys_me= cm_isd_sys_e - cm_isd_sys_s
,cm_isd_quo_me= cm_isd_quo_e - cm_isd_quo_s
,cm_isd_pre_me= cm_isd_pre_e - cm_isd_pre_s
,cm_isd_npw_me= cm_isd_npw_e - cm_isd_npw_s
,cm_isd_inf_me= cm_isd_inf_e - cm_isd_inf_s
,cm_isd_oof_me= cm_isd_oof_e - cm_isd_oof_s
,cm_esd_sys_me= cm_esd_sys_e - cm_esd_sys_s
,cm_esd_quo_me= cm_esd_quo_e - cm_esd_quo_s
,cm_esd_pre_me= cm_esd_pre_e - cm_esd_pre_s
,cm_esd_npw_me= cm_esd_npw_e - cm_esd_npw_s
,cm_esd_inf_me= cm_esd_inf_e - cm_esd_inf_s
,cm_esd_oof_me= cm_esd_oof_e - cm_esd_oof_s
,cm_pf_sys_me= cm_pf_sys_e - cm_pf_sys_s
,cm_pf_quo_me= cm_pf_quo_e - cm_pf_quo_s
,cm_pf_pre_me= cm_pf_pre_e - cm_pf_pre_s
,cm_pf_npw_me= cm_pf_npw_e - cm_pf_npw_s
,cm_pf_inf_me= cm_pf_inf_e - cm_pf_inf_s
,cm_pf_oof_me= cm_pf_oof_e - cm_pf_oof_s
,cm_frq_ldg_sys_me= cm_frq_ldg_sys_e - cm_frq_ldg_sys_s
,cm_frq_ldg_quo_me= cm_frq_ldg_quo_e - cm_frq_ldg_quo_s
,cm_frq_ldg_pre_me= cm_frq_ldg_pre_e - cm_frq_ldg_pre_s
,cm_frq_ldg_npw_me= cm_frq_ldg_npw_e - cm_frq_ldg_npw_s
,cm_frq_ldg_inf_me= cm_frq_ldg_inf_e - cm_frq_ldg_inf_s
,cm_frq_ldg_oof_me= cm_frq_ldg_oof_e - cm_frq_ldg_oof_s
-----
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag
SET
cm_si_sys_ms= cm_si_sys_s - p.cm_si_sys_e
,cm_si_quo_ms= cm_si_quo_s - p.cm_si_quo_e
,cm_si_pre_ms= cm_si_pre_s - p.cm_si_pre_e
,cm_si_npw_ms= cm_si_npw_s - p.cm_si_npw_e
,cm_si_inf_ms= cm_si_inf_s - p.cm_si_inf_e
,cm_si_oof_ms= cm_si_oof_s - p.cm_si_oof_e
,cm_ap_sys_ms= cm_ap_sys_s - p.cm_ap_sys_e
,cm_ap_quo_ms= cm_ap_quo_s - p.cm_ap_quo_e
,cm_ap_pre_ms= cm_ap_pre_s - p.cm_ap_pre_e
,cm_ap_npw_ms= cm_ap_npw_s - p.cm_ap_npw_e
,cm_ap_inf_ms= cm_ap_inf_s - p.cm_ap_inf_e
,cm_ap_oof_ms= cm_ap_oof_s - p.cm_ap_oof_e
,cm_isd_sys_ms= cm_isd_sys_s - p.cm_isd_sys_e
,cm_isd_quo_ms= cm_isd_quo_s - p.cm_isd_quo_e
,cm_isd_pre_ms= cm_isd_pre_s - p.cm_isd_pre_e
,cm_isd_npw_ms= cm_isd_npw_s - p.cm_isd_npw_e
,cm_isd_inf_ms= cm_isd_inf_s - p.cm_isd_inf_e
,cm_isd_oof_ms= cm_isd_oof_s - p.cm_isd_oof_e
,cm_esd_sys_ms= cm_esd_sys_s - p.cm_esd_sys_e
,cm_esd_quo_ms= cm_esd_quo_s - p.cm_esd_quo_e
,cm_esd_pre_ms= cm_esd_pre_s - p.cm_esd_pre_e
,cm_esd_npw_ms= cm_esd_npw_s - p.cm_esd_npw_e
,cm_esd_inf_ms= cm_esd_inf_s - p.cm_esd_inf_e
,cm_esd_oof_ms= cm_esd_oof_s - p.cm_esd_oof_e
,cm_pf_sys_ms= cm_pf_sys_s - p.cm_pf_sys_e
,cm_pf_quo_ms= cm_pf_quo_s - p.cm_pf_quo_e
,cm_pf_pre_ms= cm_pf_pre_s - p.cm_pf_pre_e
,cm_pf_npw_ms= cm_pf_npw_s - p.cm_pf_npw_e
,cm_pf_inf_ms= cm_pf_inf_s - p.cm_pf_inf_e
,cm_pf_oof_ms= cm_pf_oof_s - p.cm_pf_oof_e
,cm_frq_ldg_sys_ms= cm_frq_ldg_sys_s - p.cm_frq_ldg_sys_e
,cm_frq_ldg_quo_ms= cm_frq_ldg_quo_s - p.cm_frq_ldg_quo_e
,cm_frq_ldg_pre_ms= cm_frq_ldg_pre_s - p.cm_frq_ldg_pre_e
,cm_frq_ldg_npw_ms= cm_frq_ldg_npw_s - p.cm_frq_ldg_npw_e
,cm_frq_ldg_inf_ms= cm_frq_ldg_inf_s - p.cm_frq_ldg_inf_e
,cm_frq_ldg_oof_ms= cm_frq_ldg_oof_s - p.cm_frq_ldg_oof_e
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tagc
JOIN
(
SELECT
ck_risk_pk
,cm_si_sys_e
,cm_si_quo_e
,cm_si_pre_e
,cm_si_npw_e
,cm_si_inf_e
,cm_si_oof_e
,cm_ap_sys_e
,cm_ap_quo_e
,cm_ap_pre_e
,cm_ap_npw_e
,cm_ap_inf_e
,cm_ap_oof_e
,cm_isd_sys_e
,cm_isd_quo_e
,cm_isd_pre_e
,cm_isd_npw_e
,cm_isd_inf_e
,cm_isd_oof_e
,cm_esd_sys_e
,cm_esd_quo_e
,cm_esd_pre_e
,cm_esd_npw_e
,cm_esd_inf_e
,cm_esd_oof_e
,cm_pf_sys_e
,cm_pf_quo_e
,cm_pf_pre_e
,cm_pf_npw_e
,cm_pf_inf_e
,cm_pf_oof_e
,cm_frq_ldg_sys_e
,cm_frq_ldg_quo_e
,cm_frq_ldg_pre_e
,cm_frq_ldg_npw_e
,cm_frq_ldg_inf_e
,cm_frq_ldg_oof_e
FROM
EDW_SSA_EBX.dbo.utb_ebx_risk__tag
)p
ONc.ck_risk_pk_p = p.ck_risk_pk
-----
INSERT INTO EDW_TRN.[dbo].[ctz_prc_dur]
([proc_name]
,[datetime_start]
,[datetime_end]
,[duration_min])
SELECT
[proc_name]= @proc_name
,[datetime_start]= @datetime_start
,[datetime_end]= GETDATE()
,[duration_min]= DATEDIFF(MINUTE,@datetime_start,GETDATE())
-----
GO
My initial reaction when I first saw this proc was that it's probably doing way too much and should be broken up. There are also some other obvious issues such as functions in a lot of the WHERE clauses etc., but I thought I'd get some opinions on some obvious ways of tackling this. I've also included the sql plan for the proc.
Any thoughts on where I should start?
April 19, 2016 at 8:25 pm
1) What you have posted goes WAY WAY WAY WAY beyond free help from a forum.
2) You need to find out which of those steps is taking the longest time. Without that you are wasting effort. It could be that one of them takes 90+% of the time. Run a profiler trace to disk while this process is doing and you will quickly see if anything jumps out as the real problem query.
3) You also need to do differential file IO stall and wait stats analyses while the process is running. You can find references to those things online.
4) Given that you are newish, really tuning something this complex could be beyond your capabilities, or at least take you way longer than it should. I recommend getting a mentor in to help you tune this mess up and teach you how to fish along the way.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 19, 2016 at 8:49 pm
Hi Kevin,
I'm not new to being a DBA, just new to my current role and have been given this process to look at.
The INSERT statements are the biggest offenders as far as I can tell, mainly due to the NC indexes that are present in the table. I was considering breaking these out into separate SPs as a start.
My initial thought was that the SP is doing a lot and should be broken down into more manageable units. I just thought I'd see if this was something people agreed with.
April 19, 2016 at 9:39 pm
Chris-475469 (4/19/2016)
Hi Kevin,I'm not new to being a DBA, just new to my current role and have been given this process to look at.
The INSERT statements are the biggest offenders as far as I can tell, mainly due to the NC indexes that are present in the table. I was considering breaking these out into separate SPs as a start.
My initial thought was that the SP is doing a lot and should be broken down into more manageable units. I just thought I'd see if this was something people agreed with.
Forget about INSERTs.
They are not your problem.
How many times it says
UPDATE EDW_SSA_EBX.dbo.utb_ebx_risk__tag in the body of the procedure?
20? 30?
And each time it's updating something between 300k and 2+mil rows.
Your objective - to get rid of those UPDATEs.
Ideally - completely.
_____________
Code for TallyGenerator
April 20, 2016 at 7:36 am
Chris-475469 (4/19/2016)
Hi Kevin,I'm not new to being a DBA, just new to my current role and have been given this process to look at.
The INSERT statements are the biggest offenders as far as I can tell, mainly due to the NC indexes that are present in the table. I was considering breaking these out into separate SPs as a start.
My initial thought was that the SP is doing a lot and should be broken down into more manageable units. I just thought I'd see if this was something people agreed with.
Taking a bunch of single statements in one sproc and breaking them out into multiple sprocs can't possibly make anything faster, can it? You are still doing the same amount of work. You must either speed the work being done or reduce the work being done or both. Another poster could be on to something: can any of those myriad updates on the same table be combined in any way? If you update the same row 10 times when you could do it just 3 times could represent a big win.
But again I will say you need DOCUMENTED PROOF of the amount of work and duration of each statement to avoid wasting your time tuning something that isn't the main cause of slowness.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2016 at 8:31 am
check the query duration stats for this proc.
This will allow you to zero in to the batch that takes the longest time.
Also, try updating values on change only.
I once fixed a proc which would do the below multiple times
update table column = 0to
update table column = 0 WHERE isnull(column,-9999999) <>0This reduced the logging required to do the update and together wtih other enhancements, a 24 hour process ended up running for 40 minutes.
April 21, 2016 at 4:12 pm
Thanks for the suggestions.
I've already isolated a few of the UPDATE statements that are performing badly and made some changes which has had an impact. I think I'm going to have to go back to the drawing board on the end to end process as there is so much that needs to be changed.
April 22, 2016 at 8:30 am
THIS!! -->>
Chris-475469 (4/21/2016)
I've already isolated a few of the UPDATE statements that are performing badly and made some changes which has had an impact. I think I'm going to have to go back to the drawing board on the end to end process as there is so much that needs to be changed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply