Ensure fields index exist when Joining
The query should be carefully written and getting sure that on joining fields index must exist otherwise serious performance issue can raise as data volume will increase. The recent example of such case is found in MBFSJ. The dayend was executing in 3 hours and after monitoring the process below query found that was consuming major processor time. This query has joins on receipt_id column between rental_Trans and receivable_paid tabled without index. After creating below indexes the time of day end reduced to 1hr 40Min.
CREATE NONCLUSTERED INDEX [IDX_RT_rct_id] ON [dbo].[RENTAL_TRANS]
([receipt_id] ASC)
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_CRP_rect_id]
ON [dbo].[CONTRACT_RECEIVABLE_PAID] ([receipt_id])
GO
INSERT INTO CMS_ACTIVITY_LOG
( event_code ,
contract_id ,
execution_dte ,
SAP_ind ,
external_contract_nbr ,
reference_id ,
SAP_FP_cde ,
SAP_asset_cde ,
cost_centre_cde ,
column1_nme ,
column2_nme ,
column3_nme ,
column4_nme ,
column5_nme ,
column6_nme ,
column7_nme ,
column8_nme ,
column9_nme ,
payee_id ,
receipt_id ,
charge_cde ,
payment_mode_cde ,
bp_primary_id ,
bp_secondary_id ,
tax_base_amt3 — added on 25/5/2012 against fault # 2770
)
SELECT ‘00895’ “event_cde” ,
c.contract_id “contract_id” ,
@process_dte “execution_dte” ,
‘F’ “SAP_ind” ,
c.external_contract_nbr “external_contract_nbr” ,
c.contract_id “reference_id” ,
dbo.Fn_com_SAP_FP_code(c.financial_product_id) “SAP_FP_cde” ,
dbo.Fn_Sap_Asset_Code(c.contract_id) “SAP_asset_cde” ,
dbo.Fn_Sap_Cost_Centre(c.contract_id) ,
CASE WHEN cc.lease_typ NOT IN ( ‘I’, ‘P’ )
THEN ISNULL(rt.receipt_settled_amt, 0)
ELSE ISNULL(rt.receipt_amt, 0)
END AS column1_nme ,
CASE WHEN cc.lease_typ = ‘O’ THEN 0
ELSE ISNULL(rt.receipt_amt, 0)
– ISNULL(rt.GST_settle_amt, 0)
END AS “column2_nme” ,
CASE WHEN cc.lease_typ = ‘O’
THEN ISNULL(rt.maint_trust_Vat_settled_amt, 0)
WHEN cc.lease_typ = ‘F’
THEN ( ISNULL(rt.GST_settle_amt, 0)
+ ISNULL(rt.maint_trust_Vat_settled_amt, 0) )
WHEN cc.lease_typ IN ( ‘I’, ‘P’ )
THEN ISNULL(rt.GST_settle_amt, 0)
ELSE 0
END AS “column3_nme” ,
CASE WHEN cc.lease_typ IN ( ‘O’, ‘F’ )
THEN ISNULL(rt.maint_trust_settled_amt, 0)
ELSE 0
END AS “column4_nme” ,
CASE WHEN cc.lease_typ IN ( ‘O’, ‘F’ )
THEN ISNULL(rt.maint_settled_amt, 0)
ELSE 0
END AS “column5_nme” ,
/****************************Added By Muhammad Umar on 26/06/2012 ****************************************/
CASE WHEN cc.lease_typ = ‘O’
AND ISNULL(@Bal_Receivable_Amt, 0) > ( SELECT
( ( ISNULL(rtt.receipt_settled_amt,
0)
+ ( CASE
WHEN DATEPART(mm,
rp.due_dte) IN (
fa.bonus_mnth1,
fa.bonus_mnth2 )
THEN ISNULL(fa.bonus_amt,
0)
ELSE 0
END ) )
– ( ISNULL(rtt.maint_trust_settled_amt,
0)
+ ISNULL(rtt.maint_trust_Vat_settled_amt,
0)
+ ISNULL(rtt.maint_settled_amt,
0) )
– ISNULL(rp.monthly_payment_vat_amt,
0)
– ISNULL(rtt.hd_chrg_vat_settled_amt,
0) )
FROM
dbo.RENTAL_TRANS rtt
JOIN dbo.REPAYMENT_PLAN rp ON rtt.contract_id = rp.contract_id
AND rtt.rental_id = rp.rental_id
JOIN dbo.FINANCIAL_AGREEMENT fa ON fa.contract_id = rp.contract_id
JOIN dbo.CONTRACT_RECEIVABLE_PAID crp ON crp.receipt_id = rtt.receipt_id
JOIN contract c ON c.contract_id = crp.contract_id
WHERE
rtt.contract_id = c.contract_id
AND rtt.receipt_id = r.[receipt_id]
AND rtt.transaction_seq = rt.transaction_seq
AND rtt.rental_id = rt.rental_id
)
THEN ( SELECT ( ( ISNULL(rtt.receipt_settled_amt,
0)
+ ( CASE WHEN DATEPART(mm,
rp.due_dte) IN (
fa.bonus_mnth1,
fa.bonus_mnth2 )
THEN ISNULL(fa.bonus_amt,
0)
ELSE 0
END ) )
– ( ISNULL(rtt.maint_trust_settled_amt,
0)
+ ISNULL(rtt.maint_trust_Vat_settled_amt,
0)
+ ISNULL(rtt.maint_settled_amt,
0) )
– ISNULL(rp.monthly_payment_vat_amt,
0)
– ISNULL(rtt.hd_chrg_vat_settled_amt,
0) )
FROM   dbo.RENTAL_TRANS rtt
JOIN dbo.REPAYMENT_PLAN rp ON rtt.contract_id = rp.contract_id
AND rtt.rental_id = rp.rental_id
JOIN dbo.FINANCIAL_AGREEMENT fa ON fa.contract_id = rp.contract_id
JOIN dbo.CONTRACT_RECEIVABLE_PAID crp ON crp.receipt_id = rtt.receipt_id
JOIN contract c ON c.contract_id = crp.contract_id
WHERE  rtt.contract_id = c.contract_id
AND rtt.receipt_id = r.[receipt_id]
AND rtt.transaction_seq = rt.transaction_seq
AND rtt.rental_id = rt.rental_id
)
ELSE ISNULL(@Bal_Receivable_Amt, 0)
END AS “column6_nme” ,–Lease Receivables
/****************************END of Added By Muhammad Umar on 26/06/2012 ****************************************/
CASE WHEN cc.lease_typ = ‘O’
AND ( SELECT ( ISNULL(rtt.receipt_settled_amt,
0)
– ( ISNULL(rtt.maint_trust_settled_amt,
0)
+ ISNULL(rtt.maint_trust_Vat_settled_amt,
0)
+ ISNULL(rtt.maint_settled_amt,
0) )
– ISNULL(rp.monthly_payment_vat_amt,
0)
– ISNULL(rtt.hd_chrg_vat_settled_amt,
0) )
— ISNULL(tbl.receivable_bal_amt, 0) payable_bal_amt
– ISNULL(@Bal_Receivable_Amt,
0)
FROM   dbo.RENTAL_TRANS rtt
JOIN dbo.REPAYMENT_PLAN rp ON rtt.contract_id = rp.contract_id
AND rtt.rental_id = rp.rental_id
JOIN dbo.CONTRACT_RECEIVABLE_PAID crp ON crp.receipt_id = rtt.receipt_id
JOIN contract c ON c.contract_id = crp.contract_id
WHERE  rtt.contract_id = c.contract_id
AND rtt.receipt_id = r.[receipt_id]
AND rtt.transaction_seq = rt.transaction_seq
AND rtt.rental_id = rt.rental_id
) > 0
THEN ( SELECT ( ISNULL(rtt.receipt_settled_amt,
0)
– ( ISNULL(rtt.maint_trust_settled_amt,
0)
+ ISNULL(rtt.maint_trust_Vat_settled_amt,
0)
+ ISNULL(rtt.maint_settled_amt,
0) )
– ISNULL(rp.monthly_payment_vat_amt,
0)
– ISNULL(rtt.hd_chrg_vat_settled_amt,
0) )
— ISNULL(tbl.receivable_bal_amt, 0) payable_bal_amt
– ISNULL(@Bal_Receivable_Amt, 0)
FROM   dbo.RENTAL_TRANS rtt
JOIN dbo.REPAYMENT_PLAN rp ON rtt.contract_id = rp.contract_id
AND rtt.rental_id = rp.rental_id
JOIN dbo.CONTRACT_RECEIVABLE_PAID crp ON crp.receipt_id = rtt.receipt_id
JOIN contract c ON c.contract_id = crp.contract_id
WHERE  rtt.contract_id = c.contract_id
AND rtt.receipt_id = r.[receipt_id]
AND rtt.transaction_seq = rt.transaction_seq
AND rtt.rental_id = rt.rental_id
)
ELSE 0
END AS “column7_nme” ,–Prepaid Lease
CASE WHEN cc.lease_typ = ‘O’
AND ISNULL(@Bal_Receivable_vat_Amt, 0) > ( ISNULL(rp.monthly_payment_vat_amt,
0)
+ ISNULL(rt.hd_chrg_vat_settled_amt,
0)
–+ @Bonus_VAT_Amt
)
THEN ISNULL(rp.monthly_payment_vat_amt, 0)
+ ISNULL(rt.hd_chrg_vat_settled_amt, 0)
–+ @Bonus_VAT_Amt
ELSE ISNULL(@Bal_Receivable_vat_Amt, 0)
END AS “column8_nme” –Output VAT Paid not Received
,
CASE WHEN cc.lease_typ = ‘O’
AND ( ISNULL(rp.monthly_payment_vat_amt, 0)
+ ISNULL(rt.hd_chrg_vat_settled_amt, 0)
— + @Bonus_VAT_Amt
) – ISNULL(@Bal_Receivable_vat_Amt, 0) > 0
THEN ( ISNULL(rp.monthly_payment_vat_amt, 0)
+ ISNULL(rt.hd_chrg_vat_settled_amt, 0)
— + @Bonus_VAT_Amt
) – ISNULL(@Bal_Receivable_vat_Amt, 0)
ELSE 0
END AS “column9_nme” ,
NULL “payee_id” ,
r.receipt_id “receipt_id” ,
NULL “charge_cde” ,
ISNULL(c.payment_mode_cde, 0) ,
NULL “bp_primary_id” ,
NULL “bp_secondary_id” ,
CASE WHEN cc.lease_typ = ‘F’
THEN ( ( ISNULL(rt.receipt_amt, 0)
– ISNULL(rt.GST_settle_amt, 0) )
+ ( ISNULL(rt.maint_trust_settled_amt, 0) ) )
WHEN cc.lease_typ IN ( ‘I’, ‘P’ )
THEN ( ISNULL(rt.receipt_amt, 0)
– ISNULL(rt.GST_settle_amt, 0) )
ELSE 0
END AS tax_base_amt3 — added on 25/5/2012 against fault # 2770
FROM   [RENTAL_TRANS] rt
JOIN dbo.REPAYMENT_PLAN rp ON rt.contract_id = rp.contract_id
AND rp.rental_id = rt.rental_id
JOIN [Contract_RECEIVABLE_PAID] r ON r.[receipt_id] = rt.[receipt_id]
JOIN CONTRACT c ON r.contract_id = c.contract_id
JOIN dbo.CONTRACT_CONFIGURATION cc ON cc.contract_id = c.contract_id
WHERE  c.contract_id = @contract_id
AND r.[receipt_id] = @receipt_id
AND c.[payment_mode_cde] = ‘00003’ –Direct Debit
AND r.inserted_by = ‘Day-End’ — added on 07-05-2012
AND c.[financial_product_id] IN (
SELECT [financial_product_id]
FROM   fp_event_account
WHERE  [event_cde] = ‘00895’ )
This post was viewed (62) times.
Checklist – The Last Ten Nights and Days of Ramadan… Select Composite key colums from same table in VIEWs