We are using one cursor in the program TIBAPSUM , I am assuming that because of this cursor the job taking much time to get completed.I would like to improve the performance of that query. Could you please find the below query and suggest me if is there any way to improve the performance of that query.
I got an idea on this that is this should be rewritten as an extract from aplin and apmst, a 2-file match and a load but i am not getting how to rewrite the query for this requirement..
Please give me ur suggestions on this to rewrite this query like extract, a 2-file match and load.
Code:
EXEC SQL DECLARE TIVAPSUM CURSOR FOR
SELECT
:BLANK-SUMMARY-IND,
APMST.REMIT_VEND_CODE,
APMST.REMIT_VEND_SUFFIX,
APMST.COMPANY,
APMST.EMPLOYEE_NUMBER,
APMST.BANK_ID,
APMST.BANK_ACCOUNT,
APMST.PAYMENT_STATUS,
APMST.INVOICE_STATUS,
APMST.PAYMENT_DATE,
APMST.PAYMT_REF_NO,
:PAYMENT-ALL-LINE-GLIT,
APMST.PAYMENT_AMT,
APMST.PAYMENT_AMT_FX,
APMST.INV_AMT_WITHHELD,
APMST.CURRENCY_CODE,
APMST.CURRENCY_CODE_FX,
APMST.CURRENCY_RATE_FROM,
APMST.PAYMENT_METHOD
FROM TIDAPMST APMST
WHERE
(APMST.INVOICE_TYPE NOT IN
(
:MODEL-7-LITRL,
:DEPOSIT-6-LITRL,
:WITHDRAW-8-LITRL))
AND ( ( (APMST.PAYMENT_STATUS IN
(
:APPROVED-28-LITRL,
:HOLD-29-LITRL,
:PAID-30-LITRL))
AND (APMST.INVOICE_STATUS IN
(
:ACCEPTED-2-LITRL,
:MISMATCH-4-LITRL,
:COMPLETE-3-LITRL)))
OR ( (APMST.INVOICE_STATUS = :MISMATCH-4-LITRL)
AND (APMST.PAYMENT_STATUS =
:APPROVED-28-LITRL))
OR ( (APMST.PAYMENT_STATUS IN
(
:PENDING-26-LITRL,
:REJECT-31-LITRL,
:REVIEW-32-LITRL))
AND (APMST.INVOICE_STATUS =
:ACCEPTED-2-LITRL)))
AND NOT EXISTS
(
SELECT
APLIN.PAYMT_REF_NO
FROM TIDAPLIN APLIN
WHERE
(APLIN.PAYMT_REF_NO =
APMST.PAYMT_REF_NO)
AND (APLIN.SUBLINE_LINE ?> :BLANK-SUBLINE-APCOM)
AND (((APLIN.LINE_PAY_STATUS ?=
APMST.PAYMENT_STATUS) AND
((APLIN.INVOICE_LINE ?=
:WITHHELD-LINE-APCOM ) OR
(APLIN.LINE_PAY_STATUS ?= :HOLD-10-LITRL )))
OR (APLIN.INVOICE_LINE_STAT ?=
APMST.INVOICE_STATUS))
)
UNION
SELECT
:BLANK-SUMMARY-IND,
APMST.REMIT_VEND_CODE,
APMST.REMIT_VEND_SUFFIX,
APMST.COMPANY,
APMST.EMPLOYEE_NUMBER,
APMST.BANK_ID,
APMST.BANK_ACCOUNT,
APLIN.LINE_PAY_STATUS,
APLIN.INVOICE_LINE_STAT,
APMST.PAYMENT_DATE,
APMST.PAYMT_REF_NO,
APLIN.INVOICE_LINE,
APLIN.PAYMENT_AMT,
APLIN.PAYMENT_AMT_FX,
:ZVAL-INV-AMT-WITHHELD,
APLIN.CURRENCY_CODE,
APLIN.CURRENCY_CODE_FX,
APLIN.CURRENCY_RATE_FROM,
APMST.PAYMENT_METHOD
FROM TIDAPMST APMST,
TIDAPLIN APLIN
WHERE
(APMST.PAYMT_REF_NO =
APLIN.PAYMT_REF_NO) AND
(APMST.INVOICE_TYPE NOT IN
(
:MODEL-7-LITRL,
:DEPOSIT-6-LITRL,
:WITHDRAW-8-LITRL)) AND
(APLIN.SUBLINE_LINE ?> :BLANK-SUBLINE-APCOM) AND
(((APLIN.LINE_PAY_STATUS IN
(
:APPROVED-9-LITRL,
:HOLD-10-LITRL,
:PAID-11-LITRL)) AND
(APLIN.INVOICE_LINE_STAT IN
(
:ACCEPTED-23-LITRL,
:MISMATCH-25-LITRL,
:COMPLETE-24-LITRL))) OR
((APLIN.INVOICE_LINE_STAT =
:MISMATCH-25-LITRL) AND
(APLIN.LINE_PAY_STATUS =
:APPROVED-9-LITRL)) OR
((APLIN.LINE_PAY_STATUS IN
(
:PENDING-27-LITRL,
:REJECT-12-LITRL,
:REVIEW-13-LITRL)) AND
(APLIN.INVOICE_LINE_STAT = :ACCEPTED-23-LITRL)))
AND EXISTS
(
SELECT
APLI1.PAYMT_REF_NO
FROM TIDAPLIN APLI1
WHERE
(APLI1.PAYMT_REF_NO =
APMST.PAYMT_REF_NO)
AND (APLI1.SUBLINE_LINE ?> :BLANK-SUBLINE-APCOM)
AND (((APLI1.LINE_PAY_STATUS ?=
APMST.PAYMENT_STATUS) AND
((APLI1.INVOICE_LINE ?=
:WITHHELD-LINE-APCOM ) OR
(APLI1.LINE_PAY_STATUS ?= :HOLD-10-LITRL )))
OR (APLI1.INVOICE_LINE_STAT ?=
APMST.INVOICE_STATUS))
)
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
Suresh,
you are corrcet that sometimes, doing an unload and processing that can be faster, sometimes much faster than a cursor declared in the program, sometimes not.
How many total rows are in the tables you are extracting from, and how many rows does the cursor select from the tables?
What other processing is going on in the program that might affect the performance?
When you say the program is running slow, what do you mean? How much clock time does the program take and how much CPU time? To process how many rows?
The job fetched and inserted around 3340321 records(rows). I think day by day the records count will be increase. Rest of the program process is looks fine. Totally job taking around 1.45 min CPU time to get completed that's why i would like to improve the performance of this.
COuld you please give me an idea to rewrite the query to 2 parts.
