CHROMIUM

Welcome to CHROMIUM's very own blog!

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 (51) times.

Leave a Reply

Your email address will not be published. Required fields are marked *