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 (40) times.
Corrupt Connectsions Non functional history triggers can be disabled while executing dayend to optimize the process