|
View previous topic :: View next topic
|
| Author |
Message |
harish5002
New User
Joined: 21 Jul 2010
Posts: 23
Location: chennai
|
|
|
|
Hi All,
Please anyone help me in my requirement. I have a query as
| Code: |
EXEC SQL DECLARE CLR_SZ_ALLOC_CSR CURSOR FOR
SELECT A.PACK_MULTIPLE,
A.SZ_SCALE_CD,
B.SIZE_NBR,
A.PACK_PO_LINE_ITM,
D.LOC_NBR,
A.SZ_GRP_SCALE_PACKS,
(CASE WHEN B.PO_SZ_SCALE_QTY = 0 THEN
( A.SZ_GRP_SCALE_PACKS * B.PO_SZ_SCALE_PCT )
ELSE
( A.SZ_GRP_SCALE_PACKS * B.PO_SZ_SCALE_QTY )
END),
D.SCALE_LOC_PACKS,
(CASE WHEN A.PO_SZ_SCL_PCT_USED = 0 THEN
0
ELSE
(CASE WHEN B.PO_SZ_SCALE_QTY = 0 THEN
(CASE WHEN B.PO_SZ_SCALE_PCT = 0 THEN
0
ELSE
INTEGER((D.SCALE_LOC_UNITS
/ (INTEGER((A.PO_SZ_SCL_PCT_USED / 100)+.9))
* DECIMAL(( B.PO_SZ_SCALE_PCT / 100 ),5,2)))
END)
ELSE
INTEGER((D.SCALE_LOC_UNITS
/ (CASE WHEN A.PO_SZ_SCL_PCT_USED = 0 THEN
1
ELSE
( A.PO_SZ_SCL_PCT_USED * .01)
END)
* ( B.PO_SZ_SCALE_QTY * .01)) + .50)
END)
END),
B.PO_SZ_SCALE_QTY,
B.PO_SZ_SCALE_PCT,
A.LAST_UPD_TS
FROM PO_SZ_GRP_SCALE A,
PO_SZ_SCALE B
LEFT OUTER JOIN (SELECT E.LOC_NBR,
E.SCALE_LOC_PACKS,
E.SCALE_LOC_UNITS,
E.SZ_SCALE_CD,
E.PACK_MULTIPLE,
E.CLR_PO_LINE_ITM,
E.PID_PO_LINE_ITM,
E.SHPG_NBR,
E.PO_NBR,
E.PARTITION_KEY
FROM PO_SGRP_SCALE_LOC E
WHERE E.LOC_NBR = :BUYSSCL-LOC-NBR
AND E.PO_NBR = :BUYSGSC-PO-NBR
AND E.PARTITION_KEY
= :BUYSGSC-PARTITION-KEY
AND E.SHPG_NBR
= :BUYSGSC-SHPG-NBR
AND E.PID_PO_LINE_ITM
= :BUYSGSC-PID-PO-LINE-ITM
AND E.CLR_PO_LINE_ITM
= :BUYSGSC-CLR-PO-LINE-ITM
) AS D
ON D.PARTITION_KEY = B.PARTITION_KEY
AND D.PO_NBR = B.PO_NBR
AND D.SHPG_NBR = B.SHPG_NBR
AND D.PID_PO_LINE_ITM = B.PID_PO_LINE_ITM
AND D.CLR_PO_LINE_ITM = B.CLR_PO_LINE_ITM
AND D.PACK_MULTIPLE = B.PACK_MULTIPLE
AND D.SZ_SCALE_CD = B.SZ_SCALE_CD
WHERE A.PARTITION_KEY = :BUYSGSC-PARTITION-KEY
AND A.PO_NBR = :BUYSGSC-PO-NBR
AND A.SHPG_NBR = :BUYSGSC-SHPG-NBR
AND A.PID_PO_LINE_ITM = :BUYSGSC-PID-PO-LINE-ITM
AND A.CLR_PO_LINE_ITM = :BUYSGSC-CLR-PO-LINE-ITM
AND A.SZ_GRP_SCALE_UNITS > 0
AND B.PARTITION_KEY = A.PARTITION_KEY
AND B.PO_NBR = A.PO_NBR
AND B.SHPG_NBR = A.SHPG_NBR
AND B.PID_PO_LINE_ITM = A.PID_PO_LINE_ITM
AND B.CLR_PO_LINE_ITM = A.CLR_PO_LINE_ITM
AND B.PO_LINE_ITM = :BUYSZSC-PO-LINE-ITM
AND B.PACK_MULTIPLE = A.PACK_MULTIPLE
AND B.SZ_SCALE_CD = A.SZ_SCALE_CD
ORDER BY D.LOC_NBR,
A.PACK_PO_LINE_ITM DESC,
A.PACK_MULTIPLE,
A.SZ_SCALE_CD,
B.SIZE_NBR
FOR FETCH ONLY WITH UR
END-EXEC.
|
This is the query we are using in our mainframe program. It is talking long time to execute. But according to our requirement we need to alter this query and make to execute faster with same result. Please help me. Thanks in advance........ |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| It is talking long time to execute. |
define in proper IT terms long time please ...
You just whined about something not behaving according to Your/Somebody's expectations
and providing nothing to work on
the only reasonable advice You might get is ...
do not drink too much coffee while waiting 
| Quote: |
| But according to our requirement we need to alter this query and make to execute faster with same result. |
more productive to get rid the idiot who set the requirement ...
what if it is correct for the <thing> to take for execution what You call a long time ?  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| And it is in the DFSORT forum because....? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
that' s probably the reason for the long <time> 
moved where it belongs
and edited for readability adding the code tags |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| But according to our requirement we need to alter this query and make to execute faster with same result |
There is no reason to believe that any "monster query" will run acceptably.
Suggest you experiment with breaking this into more than one big sql statement and make sure the individual components all perform acceptably. It is in a program anyway, so having something that runs faster and is easier to work with will be better for everyone.
There is neither business nor technical reason to jam everything into one statement as this is. And NO, it is not "the requirement". The requirement is to correctly process the data using an acceptable amount of resources. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
harish5002,
This things will help you get started,
Check whether index exists for all the columns in the WHERE clause
Does the Clustering index exists on the columns in the order by
What is the cardinality of the columns in the WHERE clause ?
How many number of rows exist in the table ?
How much rows does the query retreive ?
Finally, did you do an explain ?
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
