Page 1 of 1

Performace tuning on db2 query

PostPosted: Fri Sep 20, 2013 8:37 pm
by anjireddy4u
HI,

could u someone give the idea to improve performace for below query.

WHERE A.BAT_PROMO_NBR = B.BAT_PROMO_NBR
AND A.BAT_PROMO_NBR = C.BAT_PROMO_NBR
AND A.BAT_SRC_VNDR_NO = :WS-TAG-ITEM-VENDOR
AND A.BAT_SRC_ITEM_NO = :WS-TAG-ITEM-CODE
AND A.BAT_UPC_CODE = :WS-TAG-REDEF-12
AND A.BAT_STORE IN (:WS-TAG-STORE-NUM,0)
AND A.BAT_ZONE = :WS-TAG-STORE-NUM
AND A.BAT_SRC_CODE = 2
AND ((A.BAT_PULL_IND = 'Y')
OR (A.BAT_EFF_DATE = :WS-SELL-START-DATE
AND A.BAT_PULL_IND IN ('R','S'))
AND A.BAT_CNFLT_FLAG = ' ')
AND A.BAT_CNFLT_PROMO = 0
AND B.BAT_PROMO_CLASS <> 'T'
AND C.BAT_CLU_LEVEL =
(SELECT MAX(BAT_CLU_LEVEL) FROM BATCH_TIERDTL D
WHERE D.BAT_PROMO_NBR = B.BAT_PROMO_NBR)
WITH UR
END-EXEC. we are fetching the data from 3 tables , its around 10lacs records were fething; its tooks almost 20 to 30 mints.

so someone should provide the better way to reduce cpu consumtion///

thanks,
ram

Re: Performace tuning on db2 query

PostPosted: Fri Sep 20, 2013 8:43 pm
by dick scherrer
Hello and welcome to the forum,

The first part of the query is missing . . .

From the description of your problem it sounds like you have caused the creation of multiple cartesian products.

What does an Explain show?

Re: Performace tuning on db2 query

PostPosted: Fri Sep 20, 2013 8:57 pm
by anjireddy4u
yes your correct ; its like multiple cartesian products.

SELECT
A.BAT_PROMO_NBR
,A.BAT_ITEM_NO
,A.BAT_SRC_VNDR_NO
,A.BAT_SRC_ITEM_NO
,A.BAT_SRC_DEAL_CODE
,A.BAT_ZONE
,A.BAT_STORE
,A.BAT_SRC_CODE
,A.BAT_END_DATE
,A.BAT_CNFLT_FLAG
,A.BAT_CNFLT_PROMO
,A.BAT_ITEM_DESC
,A.BAT_CLASS
,A.BAT_SIZE
,A.BAT_MEAS
,A.BAT_UM
,A.BAT_FIN_RETAIL
,A.BAT_BEG_RETAIL
,A.BAT_BEG_TWO_FOR
,A.BAT_CALC_SAVE_AMT
,A.BAT_FIN_TWO_FOR
,A.BAT_TAG_SEND_FLAG
,A.BAT_UPC_CODE
,A.BAT_PACK
,A.BAT_REPL_FLAG
,A.BAT_QTY_ON
,A.BAT_SAVE_ON
,A.BAT_CHAIN_ID
,B.BAT_TYPE_THRESHLD
,B.BAT_TYPE_DISCOUNT
,B.BAT_PRIMARY_PROMO
,B.BAT_SPECIAL_OFFER
,B.BAT_DESCRIPTION
,B.BAT_PROMO_TYPE
,B.BAT_PROMO_CLASS
,B.BAT_PULL_IND
,B.BAT_REDEEM_FLAG
,B.BAT_BEST_DEAL
,B.BAT_WGT_REQ
,C.BAT_THRESHLD_AMT
,C.BAT_THRESHLD_PTS
,C.BAT_DISC_AMT
,C.BAT_DISC_PERCENT
,C.BAT_DISC_POINTS
,C.BAT_DISC_RETAIL
,C.BAT_DISC_TWO_FOR
,C.BAT_FREE_FLAG
,C.BAT_BOGO_FLAG
,C.BAT_QTY_LIMIT
,C.BAT_QTY_REQ
,C.BAT_INCR
,C.BAT_EXCL
INTO
:BATITEM.BAT-PROMO-NBR
,:BAT-ITEM-NO
,:BAT-SRC-VNDR-NO
,:BAT-SRC-ITEM-NO
,:BAT-SRC-DEAL-CODE
,:BAT-ZONE
,:BAT-STORE
,:BAT-SRC-CODE
,:BATITEM.BAT-END-DATE
,:BAT-CNFLT-FLAG
,:BAT-CNFLT-PROMO
,:BAT-ITEM-DESC
,:BAT-CLASS
,:BAT-SIZE
,:BAT-MEAS
,:BAT-UM
,:BAT-FIN-RETAIL
,:BAT-BEG-RETAIL
,:BAT-BEG-TWO-FOR
,:BAT-CALC-SAVE-AMT
,:BAT-FIN-TWO-FOR
,:BAT-TAG-SEND-FLAG
,:BAT-UPC-CODE
,:BAT-PACK
,:BAT-REPL-FLAG
,:BAT-QTY-ON
,:BAT-SAVE-ON
,:BAT-CHAIN-ID
,:BAT-TYPE-THRESHLD
,:BAT-TYPE-DISCOUNT
,:BAT-PRIMARY-PROMO
,:BAT-SPECIAL-OFFER
,:BAT-DESCRIPTION
,:BAT-PROMO-TYPE
,:BAT-PROMO-CLASS
,:BATITEM.BAT-PULL-IND
,:BAT-REDEEM-FLAG
,:BAT-BEST-DEAL
,:BAT-WGT-REQ
,:BAT-THRESHLD-AMT
,:BAT-THRESHLD-PTS
,:BAT-DISC-AMT
,:BAT-DISC-PERCENT
,:BAT-DISC-POINTS
,:BAT-DISC-RETAIL
,:BAT-DISC-TWO-FOR
,:BAT-FREE-FLAG
,:BAT-BOGO-FLAG
,:BAT-QTY-LIMIT
,:BAT-QTY-REQ
,:BAT-INCR
,:BAT-EXCL
FROM BATCH_TIERITEM A,
BATCH_TIERPROM B,
BATCH_TIERDTL C

how we can optimize the query with minimal cpu perfromance

Re: Performace tuning on db2 query

PostPosted: Fri Sep 20, 2013 9:50 pm
by anjireddy4u
i ran the same query in dev region; actually it fetching 1000 records for every 1 minute.
in daily batch around we have to process 10 lacs records ,so it took more time consumption..
and also trying to reduce io operations in that pgm,
waiting for someone response to get a better suggestion

Re: Performace tuning on db2 query

PostPosted: Fri Sep 20, 2013 11:31 pm
by dick scherrer
Hello,

Unload the data into sequential files and join them using your sort product.

Not everything needs to be done by SQL alone.

Re: Performace tuning on db2 query

PostPosted: Sat Sep 21, 2013 2:28 am
by anjireddy4u
thanks for the response..

Do we have any other solution instead of loading into sequntial files.

is there any possiblity to tune the DB2 query?

Regards,
Ram

Re: Performace tuning on db2 query

PostPosted: Sat Sep 21, 2013 2:40 am
by Akatsukami
anjireddy4u wrote:thanks for the response..

Do we have any other solution instead of loading into sequntial files.

is there any possiblity to tune the DB2 query?

No.

Why do people write hideously complex and abysmally non-performant SQL queries when an ETL could be done with a few lines of COBOL or PL/I, or a few *Sort control statements?

Re: Performace tuning on db2 query

PostPosted: Fri Sep 27, 2013 3:12 pm
by anjireddy4u
its existing logic..i am agreed your process.

i have to splitcode 2 to 3 programs as per your process..

before we are fetching the records from table,based oncertain certiera , if the condition is statisfied we are updating table itself in same program..

so please let knoe your suggestion

Re: Performace tuning on db2 query

PostPosted: Fri Sep 27, 2013 9:18 pm
by dick scherrer
After matching the unloaded entriies, read the new file and update the database info with that data.