CHROMIUM

Welcome to CHROMIUM's very own blog!

Cursor impact on Performance

Cursor usage should be avoided in procedures, functions and triggers. But if in some situations can’t be avoided then there is one important tip is for maximum performance. This is that cursor definition query should be kept simple as possible. The multiple tables joins and filters should not be used in cursor query. In below example before and after picure is shown as example. In below example procedure execution time is reduced from 25 minute to 4 minutes by keeping the cursor definition query simple to one temp table.

==================
Before
==================


DECLARE MAINT_CUR CURSOR
FOR
SELECT ISNULL(RP.MAINT_VAT_INC_AMT, 0) ,
RP.CONTRACT_ID ,
RP.DUE_DTE ,
RPP.VALUE_DTE ,
rp.rental_id ,
rt.receipt_id ,
rp.maint_vat_amt ,
maint_vat_excl_amt
FROM DBO.REPAYMENT_PLAN RP
JOIN RENTAL_TRANS RT ON RP.CONTRACT_ID = RT.CONTRACT_ID
AND RP.AGREEMENT_SEQ = RT.AGREEMENT_SEQ
AND RP.RENTAL_ID = RT.RENTAL_ID
JOIN DBO.RECEIVABLE_PAID RPP ON RT.RECEIPT_ID = RPP.RECEIPT_ID
JOIN dbo.CONTRACT_RECEIVABLE_PAID crp ON RPP.receipt_id = crp.receipt_id
JOIN CONTRACT c ON crp.contract_id = c.contract_id
WHERE CMP_RECEIVABLE_EXCL_RV_AMT = 0
AND ISNULL(RP.MAINT_VAT_INC_AMT, 0)
– ISNULL(RP.MAINT_SETTLED_AMT, 0) = 0
AND ISNULL(MAINT_VAT_INC_AMT, 0) > 0
AND RP.DUE_DTE <= @PROCESS_DTE
AND ISNULL(maint_payable_ind, ‘X’) = ‘F’
AND c.request_status_cde <> ‘125’

 

==================

After Performance

==================
SELECT ISNULL(RP.MAINT_VAT_INC_AMT, 0) maint_vat_inc_amt,

RP.CONTRACT_ID ,

RP.DUE_DTE ,

RPP.VALUE_DTE ,

rp.rental_id ,

rt.receipt_id ,

rp.maint_vat_amt ,

maint_vat_excl_amt

into #temp_Cur

FROM DBO.REPAYMENT_PLAN RP

JOIN RENTAL_TRANS RT ON RP.CONTRACT_ID = RT.CONTRACT_ID

AND RP.AGREEMENT_SEQ = RT.AGREEMENT_SEQ

AND RP.RENTAL_ID = RT.RENTAL_ID

JOIN DBO.RECEIVABLE_PAID RPP ON RT.RECEIPT_ID = RPP.RECEIPT_ID

JOIN dbo.CONTRACT_RECEIVABLE_PAID crp ON RPP.receipt_id = crp.receipt_id

JOIN CONTRACT c ON crp.contract_id = c.contract_id

WHERE CMP_RECEIVABLE_EXCL_RV_AMT = 0

AND ISNULL(RP.MAINT_VAT_INC_AMT, 0)

– ISNULL(RP.MAINT_SETTLED_AMT, 0) = 0

AND ISNULL(MAINT_VAT_INC_AMT, 0) > 0

AND RP.DUE_DTE

AND ISNULL(maint_payable_ind, ‘X’) = ‘F’

AND c.request_status_cde <> ‘125’

DECLARE MAINT_CUR CURSOR

FOR select * from #temp_cur

This post was viewed (37) times.

Leave a Reply

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