View previous topic :: View next topic
|
Author |
Message |
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
Hi,
We have a COBOL-DB2 program, where below update query executed three times with different value of WS-SEC-TYPE.
From the package information after bind this COBOL-DB2 program, we get Cost*Rate of the update query is 4.5. So after executing this query three times when the program runs, what will be the total Cost*Rate of this update query?
Is this Cost*Rate = 4.5 * 3 = 13.5 ?
Similarly, if the query executed for 10 times, then will the Cost*Rate = 4.5 * 10 = 45.
Query
-------
UPDATE EMPLOYEE_TAB
SET STATUS_CODE = 'C'
WHERE SEC_NO = :WS-SEC-NO
AND SEC_CITY = :WS-SEC-TYPE
Thanks. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
If the cost for the 3 different values is about the same, why would the total cost not be consistent?
I don't really understand why there is a concern. |
|
Back to top |
|
|
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
Hi,
In table V1CXASST,
(i)cluster and non- unique index is build on combination of three columns ML_SC_NO, MLC_SC_FD_DST_CD and MLC_FD_STA_CD
(ii)non-cluster and non- unique index is build on combination of two columns MLC_SC_FD_DST_CD and MLC_FD_STA_CD
(a)In our cobol db2 program PMGSST, below UPDATE query in Para 2100-UPDATE-SSTTAB executed 3 times as below:
2000-PROCESS-FEED.
MOVE 'SMFFEED' TO SST-MLC-SC-FD-DST-CD
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT
MOVE 'FCTFEED' TO SST-MLC-SC-FD-DST-CD
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT
MOVE 'DACFEED' TO SST-MLC-SC-FD-DST-CD
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT.
2000-EXIT.
EXIT.
2100-UPDATE-SSTTAB.
EXEC SQL
UPDATE V1CXASST
SET MLC_FD_STA_CD = :IN-STATUS-TO
,ROW_LUP_PGM_NM = :WS-PROG-NAME
,ROW_LUP_TS = CURRENT TIMESTAMP
WHERE MLC_SC_FD_DST_CD = :SST-MLC-SC-FD-DST-CD
AND ML_SC_NO = :SST-ML-SC-NO
AND MLC_FD_STA_CD = :SST-MLC-FD-STA-CD
END-EXEC.
2100-EXIT.
EXIT.
When we checked the COST*RATE of the above UPDATE query from the EXPLAIN results for Package (in CATALOG MANAGER), we got as below:
COST*RATE = 4.038248
COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME
4.038248 1 1 0 UPDATE 0 I 3 Y TBCXASST
IXNAME
X1CXASST
(b) Now in program PGMSST, we made below changes in UPDATE query so that instead of executing the UPDATE query 3 times, it executes only once.
2000-PROCESS-FEED.
MOVE 'SMFFEED' TO WS-SC-FD-DST-CD-SMF
MOVE 'FCTFEED' TO WS-SC-FD-DST-CD-FCT
MOVE 'DACFEED' TO WS-SC-FD-DST-CD-DAC
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT.
2000-EXIT.
EXIT.
2100-UPDATE-SSTTAB.
EXEC SQL
UPDATE V1CXASST
SET MLC_FD_STA_CD = :IN-STATUS-TO
,ROW_LUP_PGM_NM =:WS-PROG-NAME
,ROW_LUP_TS = CURRENT TIMESTAMP
WHERE MLC_SC_FD_DST_CD IN ( : WS-SC-FD-DST-CD-SMF,
:WS-SC-FD-DST-CD-FCT,
: WS-SC-FD-DST-CD-DAC)
AND ML_SC_NO = :SST-ML-SC-NO
AND MLC_FD_STA_CD = :SST-MLC-FD-STA-CD
END-EXEC.
2100-EXIT.
EXIT.
When we checked the COST*RATE of the above UPDATE query from the EXPLAIN results for Package, we got as below:
COST*RATE = 4.720154
COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME
4.720154 1 1 0 UPDATE 0 N 3 Y TBCXASST
IXNAME
X1CXASST
Please let us know which UPDATE query (query in (a) or query in (b)) is better as performance wise and as cost wise when we run program PGMSST.
Please suggest which UPDATE query do we need to implement in program PGMSST to get better performance.
Thanks. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
If you have not done so already, run an explain on each approach you are considering and look at the info provided.
Make sure the queries run on data that is as close to production as possible.
If you have any doubt about the result of the explains, post your doubt here and maybe someone will have a suggestion. |
|
Back to top |
|
|
|