Performance tuning..

[ambilileela]

Hi all,

I have a db2 query in the cobol program. Because of this query the job is taking around 75cpu min to run. I need to reduce the cpu time to 35 cpu mins minimum. I am attaching the query in the post. Could anyone please suggest me a method to improve the performance.

SELECT DISTINCT
J.SLS_ORGN_OWN_ID
, E.CLS_OF_TRD_DE
, D.CLS_OF_TRD_DE
, D.CLS_OF_TRD_DE
FROM WKLY_CUST_SLS_SUM B
, COT_HIERARCHY D
, COT_HIERARCHY E
, SLS_PROD_RPTG_PR H
, SALES_ORGN_PAIRS J
WHERE B.CUST_NO = :WS-CSR-CUST-NO
AND B.END_DT > '1999-11-01'
AND B.CLS_OF_TRD_ID = D.CLS_OF_TRD_ID
AND D.CLS_OF_TRD_OWN_ID = E.CLS_OF_TRD_ID
AND B.PROD_GRP_ID = H.PROD_GRP_ID
AND B.HIER_ID = H.HIER_ID
AND H.PROD_GRP_OWN_ID = '12'
AND H.PROD_OWN_HIER_ID = 'USGP'
AND (H.END_DT IS NULL
OR H.END_DT > CURRENT DATE)
AND B.SLS_ORGN_ID = J.SLS_ORGN_ID
AND J.SLS_ORGN_OWN_ID IN ('17','228','30','50')
AND (J.END_DT IS NULL
OR J.END_DT > CURRENT DATE)


The customer no is fetched using the query

SELECT DISTINCT CUST_NO
FROM CUST_SLS_CHNL_CV
WHERE SLS_ORGN_ID IN ('17','30','50')

UNION

SELECT DISTINCT A.CUST_NO
FROM CUST_SLS_CHNL_CV A
, SLS_CREDIT_CV B
, SALES_ORG_PAIRS_CV C
WHERE A.SLS_ORGN_ID = '21'
AND A.CUST_NO = B.CUST_NO
AND B.REP_SLS_ORGN_ID = C.SLS_ORGN_ID
AND C.SLS_ORGN_OWN_ID = '228'

ORDER BY CUST_NO


Thanks in advance.

Regards,
Ambili.

[Krishna]

If you used working storage variables in the query.
---------------------------------------------------------

Make sure that variable used in the query has declared with proper
length/size.

If these variable sizes not compatible with DCL variable
length/size query does table scan and it takes more time to complete.



Thanks,
Krishna.

[ambilileela]

Hi,

Both are having the same size only.

Thanks,
Ambili.

[Kalicharan]

Hi Ambili,

Check the indexes for WKLY_CUST_SLS_SUM.CUST_NO

Thinking that CUST_NO column is the most filtering one in the query.
If the column is not indexed then query might take more CPU time.
So verify the indexes of WKLY_CUST_SLS_SUM.CUST_NO.

Also the second query which is fetching the customer number, no need to use DISTINCT keyword in this case.
Union (in DB2 and Oracle) itself returns distinct result set.So no need to use the DISTINCT keyword in customer number selection query.
However distinct will not impact performance much in this case

Also check indexes for CUST_SLS_CHNL_CV. SLS_ORGN_ID as well.

Also you need to confirm whether the whole process (including the first and second queries) is taking time or first one alone taking such a long time.

Thanks
Kali.

[ambilileela]

Hi

Sorry for the late reply. The outer query alone takes much time. Could some one please provide any method to tune this query.

Thanks in advance
Ambili

[popeye]