I've written PL/SQL code for updating columns of table, based on passed input from front end application e.g. if INSTTABLE = 8
then following block will run. I've 90+ applications like this and running update inside procedure.During testing it in dev environment
, Although code is working fine and doing intended job which is to set columns values to zero first and then call few functions to do financial calculations, it is taking too long (more than an hour). Initially I thought it is because of many millions of rows/data but I am posting it here to check whether there is any further improvement is possible in code to reduce execution time.
Thanks in advance for kind help!
PS: I can't make changes to functions, such as BAS2_RWA_CALC
internal calc/return value because it is being used by many other procs in prod, hence I did set columns to 0, in seperate update statement, before doing main update. Reason for setting it to zero is that production is following same logic of setting columns to 0 first and then doing calculations. If I don't do so, there is difference in comparison query.
IF INSTTABLE = 8 THEN
--Block to set columns to zero value
UPDATE MORT_BACK_SEC SET
BAS_EB_RWA = 0, BAS_AB_RWA = 0, BAS_EB_RWA_COMMT = 0, BAS_AB_RWA_COMMT = 0, IMP_BAS_EB_RWA = 0, IMP_BAS_AB_RWA = 0, IDS_BAS_EB_RWA = 0,
IDS_BAS_AB_RWA = 0, DIS_BAS_EB_RWA = 0, DIS_BAS_AB_RWA = 0, PRE_BAS_EB_RWA = 0, PRE_BAS_AB_RWA = 0, BAS_AIR_EB_RWA = 0, BAS_AIR_AB_RWA = 0,
BAS_DELAY_REC_EB_RWA = 0, BAS_DELAY_REC_AB_RWA = 0, BAS_TPR_EB_RWA = 0, BAS_TPR_AB_RWA = 0, BAS_UNR_EB_RWA = 0, BAS_UNR_AB_RWA = 0,
BAS_ICAAP_EB_RWA = 0, BAS_ICAAP_AIR_EB_RWA = 0, BAS_ICAAP_DELAY_REC_EB_RWA = 0, BAS_ICAAP_DIS_EB_RWA = 0, BAS_ICAAP_IDS_EB_RWA = 0,
BAS_ICAAP_IMP_EB_RWA = 0, BAS_ICAAP_PRE_EB_RWA = 0, BAS_ICAAP_TPR_EB_RWA = 0, BAS_ICAAP_UNR_EB_RWA = 0, IMP_BAS_EB_TOTAL_CAPITAL = 0,
IMP_BAS_AB_TOTAL_CAPITAL = 0, IDS_BAS_EB_TOTAL_CAPITAL = 0, IDS_BAS_AB_TOTAL_CAPITAL = 0, DIS_BAS_EB_TOTAL_CAPITAL = 0, DIS_BAS_AB_TOTAL_CAPITAL = 0,
PRE_BAS_EB_TOTAL_CAPITAL = 0, PRE_BAS_AB_TOTAL_CAPITAL = 0, BAS_AIR_EB_TOTAL_CAPITAL = 0, BAS_AIR_AB_TOTAL_CAPITAL = 0,
BAS_DELAY_REC_EB_TOTAL_CAPITAL = 0, BAS_DELAY_REC_AB_TOTAL_CAPITAL = 0, BAS_TPR_EB_TOTAL_CAPITAL = 0, BAS_TPR_AB_TOTAL_CAPITAL = 0,
BAS_UNR_EB_TOTAL_CAPITAL = 0, BAS_UNR_AB_TOTAL_CAPITAL = 0, IMP_BAS_EB_EXPECTED_LOSS = 0, IMP_BAS_AB_EXPECTED_LOSS = 0, IDS_BAS_EB_EXPECTED_LOSS = 0,
IDS_BAS_AB_EXPECTED_LOSS = 0, DIS_BAS_EB_EXPECTED_LOSS = 0, DIS_BAS_AB_EXPECTED_LOSS = 0, PRE_BAS_EB_EXPECTED_LOSS = 0, PRE_BAS_AB_EXPECTED_LOSS = 0,
BAS_AIR_EB_EXPECTED_LOSS = 0, BAS_AIR_AB_EXPECTED_LOSS = 0, BAS_DELAY_REC_EB_EXPECTED_LOSS = 0, BAS_DELAY_REC_AB_EXPECTED_LOSS = 0,
BAS_TPR_EB_EXPECTED_LOSS = 0, BAS_TPR_AB_EXPECTED_LOSS = 0, BAS_UNR_EB_EXPECTED_LOSS = 0, BAS_UNR_AB_EXPECTED_LOSS = 0
WHERE AS_OF_DATE = V_DATE;
COMMIT;
--using merge into inside procedure to update values
--I'm passing columns such as BAS_CAPITAL_CALC_CD inside function BAS2_RWA_CALC to get final calculated value which is being assigned afterwards in SET statement
MERGE INTO ( SELECT D.*,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) END AS V_BAS_EB_RWA,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) END AS V_BAS_AB_RWA,
0 AS V_BAS_EB_RWA_COMMT,
0 AS V_BAS_AB_RWA_COMMT,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_IMP_BAS_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_IMP_BAS_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_IDS_BAS_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_IDS_BAS_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_DIS_BAS_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_DIS_BAS_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_PRE_BAS_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_PRE_BAS_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_AIR_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_AIR_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_DELAY_REC_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_DELAY_REC_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_TPR_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_TPR_AB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_UNR_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_UNR_AB_RWA,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) END AS V_BAS_ICAAP_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_AIR_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_DELAY_REC_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_DIS_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_IDS_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_IMP_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_PRE_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_TPR_EB_RWA,
BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), V_BASEL_MIN, V_BAS_RWA_RATE) AS V_BAS_ICAAP_UNR_EB_RWA
FROM MORT_BACK_SEC D ) A
USING (SELECT * FROM rpt_acct_hier) B
ON (a.gl_account_id = b.acct_member and a.as_of_date = v_date and b.acct_gen2 = 'a1000')
WHEN MATCHED THEN UPDATE SET
BAS_EB_RWA = V_BAS_EB_RWA,
BAS_AB_RWA = V_BAS_AB_RWA,
BAS_EB_RWA_COMMT = V_BAS_EB_RWA_COMMT,
BAS_AB_RWA_COMMT = V_BAS_AB_RWA_COMMT,
IMP_BAS_EB_RWA = V_IMP_BAS_EB_RWA,
IMP_BAS_AB_RWA = V_IMP_BAS_AB_RWA,
IDS_BAS_EB_RWA = V_IDS_BAS_EB_RWA,
IDS_BAS_AB_RWA = V_IDS_BAS_AB_RWA,
DIS_BAS_EB_RWA = V_DIS_BAS_EB_RWA,
DIS_BAS_AB_RWA = V_DIS_BAS_AB_RWA,
PRE_BAS_EB_RWA = V_PRE_BAS_EB_RWA,
PRE_BAS_AB_RWA = V_PRE_BAS_AB_RWA,
BAS_AIR_EB_RWA = V_BAS_AIR_EB_RWA,
BAS_AIR_AB_RWA = V_BAS_AIR_AB_RWA,
BAS_DELAY_REC_EB_RWA = V_BAS_DELAY_REC_EB_RWA,
BAS_DELAY_REC_AB_RWA = V_BAS_DELAY_REC_AB_RWA,
BAS_TPR_EB_RWA = V_BAS_TPR_EB_RWA,
BAS_TPR_AB_RWA = V_BAS_TPR_AB_RWA,
BAS_UNR_EB_RWA = V_BAS_UNR_EB_RWA,
BAS_UNR_AB_RWA = V_BAS_UNR_AB_RWA,
BAS_ICAAP_EB_RWA = V_BAS_ICAAP_EB_RWA,
BAS_ICAAP_AIR_EB_RWA = V_BAS_ICAAP_AIR_EB_RWA,
BAS_ICAAP_DELAY_REC_EB_RWA = V_BAS_ICAAP_DELAY_REC_EB_RWA,
BAS_ICAAP_DIS_EB_RWA = V_BAS_ICAAP_DIS_EB_RWA,
BAS_ICAAP_IDS_EB_RWA = V_BAS_ICAAP_IDS_EB_RWA,
BAS_ICAAP_IMP_EB_RWA = V_BAS_ICAAP_IMP_EB_RWA,
BAS_ICAAP_PRE_EB_RWA = V_BAS_ICAAP_PRE_EB_RWA,
BAS_ICAAP_TPR_EB_RWA = V_BAS_ICAAP_TPR_EB_RWA,
BAS_ICAAP_UNR_EB_RWA = V_BAS_ICAAP_UNR_EB_RWA,
IMP_BAS_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_IMP_BAS_EB_RWA, 0), 2),
IMP_BAS_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_IMP_BAS_AB_RWA, 0), 2),
IDS_BAS_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_IDS_BAS_EB_RWA, 0), 2),
IDS_BAS_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_IDS_BAS_AB_RWA, 0), 2),
DIS_BAS_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_DIS_BAS_EB_RWA, 0), 2),
DIS_BAS_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_DIS_BAS_AB_RWA, 0), 2),
PRE_BAS_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_PRE_BAS_EB_RWA, 0), 2),
PRE_BAS_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_PRE_BAS_AB_RWA, 0), 2),
BAS_AIR_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_AIR_EB_RWA, 0), 2),
BAS_AIR_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_AIR_AB_RWA, 0), 2),
BAS_DELAY_REC_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_DELAY_REC_EB_RWA, 0), 2),
BAS_DELAY_REC_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_DELAY_REC_AB_RWA, 0), 2),
BAS_TPR_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_TPR_EB_RWA, 0), 2),
BAS_TPR_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_TPR_AB_RWA, 0), 2),
BAS_UNR_EB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_UNR_EB_RWA, 0), 2),
BAS_UNR_AB_TOTAL_CAPITAL = ROUND (BAS2_MGRL_CAPITAL (V_DATE, V_BAS_UNR_AB_RWA, 0), 2),
-- CALCULATE BAS_EB_EXPECTED_LOSS AND BAS_AB_EXPECTED_LOSS
IMP_BAS_EB_EXPECTED_LOSS = BAS2_EL_CALC (IMP_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IMP_BAS_AB_EXPECTED_LOSS = BAS2_EL_CALC (IMP_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IDS_BAS_EB_EXPECTED_LOSS = BAS2_EL_CALC (IDS_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IDS_BAS_AB_EXPECTED_LOSS = BAS2_EL_CALC (IDS_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
DIS_BAS_EB_EXPECTED_LOSS = BAS2_EL_CALC (DIS_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
DIS_BAS_AB_EXPECTED_LOSS = BAS2_EL_CALC (DIS_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
PRE_BAS_EB_EXPECTED_LOSS = BAS2_EL_CALC (PRE_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
PRE_BAS_AB_EXPECTED_LOSS = BAS2_EL_CALC (PRE_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_AIR_EB_EXPECTED_LOSS = BAS2_EL_CALC (AIR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_AIR_AB_EXPECTED_LOSS = BAS2_EL_CALC (AIR_AVG_BOOK_BAL, 0,BAS_PD, BAS_LGD, 0),
BAS_DELAY_REC_EB_EXPECTED_LOSS = BAS2_EL_CALC (DELAY_REC_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_DELAY_REC_AB_EXPECTED_LOSS = BAS2_EL_CALC (DELAY_REC_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_TPR_EB_EXPECTED_LOSS = BAS2_EL_CALC (TPR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_TPR_AB_EXPECTED_LOSS = BAS2_EL_CALC (TPR_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_UNR_EB_EXPECTED_LOSS = BAS2_EL_CALC (UNR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_UNR_AB_EXPECTED_LOSS = BAS2_EL_CALC (UNR_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0)
WHERE
(AS_OF_DATE = V_DATE);
COMMIT;
END IF;