Removing array loading between fetch & update takes more

kanha · New User Joined: 05 Nov 2005 Posts: 28

My program fetch rows from 2 tables using below cursor & updates one of the the above tables from which the rows are fetched.

Cursor:
DECLARE SLS_DATE_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT MANIFEST_NBR , MNFST_ACK_TMSTMP
FROM TABLE1 , TABLE2
WHERE MNFST_NBR = CAST ( MANIFEST_NBR AS INTEGER ) AND PO_STATUS_FLG =
4 AND DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'

Update query:
UPDATE TABLE1
SET
SALESFLOOR_DATE = :WS-SALESFLOOR-DATE,
PO_STATUS_FLG = 5,
LAST_UPD_DATE = CURRENT TIMESTAMP
WHERE
MANIFEST_NBR = :WS-MANIFEST-NBR

Existing Process: After fetch data is loaded into an array & then after the fetch & array loading is over, it updates table TABLE1.

New process:
Rows are fetched & table is updated one by one without the involvement of the array.
Concern:
Even if the array handling is removed, new process consumes more time.

Please help

dbzTHEdinosauer · Posted: Wed Nov 16, 2011 8:23 pm

a possible answer to you question is: are you performing any commits?
when you use the COBOL Internal Table to store the results of your cursor
(you would use an Array to multi-row fetch)
you close the cursor, which essentially functions as a commit
and then you do singleton updates.

without storing values in a COBOL Internal Table,
your unit-of-work is ever increasing - fetches from cursor and updates.

the FETCH's issue low/level locks that must be dealt with if you do not commit occaisionally.

If this is a CICS program, then more power to you performing Batch activities online.

ideas how things may go faster:
why are you selecting MNFST_ACK_TMSTMP? you are not using it.

i would change this:
DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'
to this:
MNFST_ACK_TMSTMP > timestamp('1900-01-01.00.00.00.000000')

kanha · New User Joined: 05 Nov 2005 Posts: 28

1. SALESFLOOR_DATE is a timestamp field which is loaded MNFST_ACK_TMSTMP from TABLE2. So this is required.

2. Also, I tried changing the below but in visual explain the query cost is increasing.
DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'
to this:
MNFST_ACK_TMSTMP > timestamp('1900-01-01.00.00.00.000000')

3. Also, I changed the cursor query to have WITH UR to avoid locking issue, even then I could observe no change in the performance.

I want to tune this program so removed the array logic. Is there any way, i can improve performance without the array logic? or, the array logic is the most efficient?

GuyC · Posted: Thu Nov 17, 2011 3:01 pm

kanha · New User Joined: 05 Nov 2005 Posts: 28

No,

The table correlations are missing in the query because all the columns having different name in the tables. Below is the query with correlation.

DECLARE SLS_DATE_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT T1.MANIFEST_NBR , T2.MNFST_ACK_TMSTMP
FROM TABLE1 T1, TABLE2 T2
WHERE T2.MNFST_NBR = CAST ( T1.MANIFEST_NBR AS INTEGER )
AND T1.PO_STATUS_FLG = 4
AND DATE ( T2.MNFST_ACK_TMSTMP ) > '01/01/1900'

GuyC · Posted: Thu Nov 17, 2011 4:17 pm

Do you have any information on unique/duplicate indexes, explain, column types ?

kanha · New User Joined: 05 Nov 2005 Posts: 28

Explain on Select query shows non unique index scan on table1 & clustering index scan on table2 joined by nested loop join & then workfile sort.
Index X9TABLE1:
PO_STATUS_FLG ASC CHAR 4

Index X1TABLE2:
MNFST_NBR ASC NUM 8 CLS

Update query explain shows a non-unique index scan, sort, fetch & then update.
Index X7TABLE1
MANIFEST_NBR ASC CHAR 8

GuyC · Posted: Thu Nov 17, 2011 7:43 pm

what percentage of rows on T1 have PO_STATUS_FLG = 4 ?
Is T2.MNFST_NBR , T2.MNFST_ACK_TMSTMP unique on Table2 ?

If unique you could try :

kanha · New User Joined: 05 Nov 2005 Posts: 28

Both are non-unique.