|
View previous topic :: View next topic
|
| Author |
Message |
vinuk2009
New User
Joined: 30 Apr 2009
Posts: 19
Location: chennai
|
|
|
|
Hi,
Below query is running fast in SPUFI. While using same in COBOL program it is taking 30 mins to complete. Could some one pls suggest the tuning technique on below query.
| Code: |
SELECT LNP.ML_SC_NO
,LNP.RPO_ORI_CSH_AM
,LNP.TD_QY
,SCP.SC_PX_DVS_FCR_RT
,LNP.RPO_INT_RT
FROM V1GCMLNP LNP, V1GCMSFN SFN, V1GCMSCP SCP
WHERE LNP.AT_VL_DT = :BGL-COB-DT
AND LNP.ML_SC_NO = :BGL-ML-SC-NO
AND LNP.ML_SC_NO = SCP.ML_SC_NO
AND LNP.DTC_BR_NO = SFN.EXT_ML_AC_NO
AND LNP.TD_STA_CD = 'L'
AND LNP.TD_QY <> 0
AND LNP.RPO_ORI_CSH_AM <> 0
AND SFN.CLI_COPER_ID = 8074
WITH UR |
Thanks in advance. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Run the cobol program again bypassing this sql. . .
How long does it run? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Why not run an EXPALIN and get in touch with your DBA? |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Quote: |
AND LNP.TD_QY <> 0
AND LNP.RPO_ORI_CSH_AM <> 0
|
1. In stead of using "not =" , it will be better if you can include an IN clause if the number of distinct values are less.
2. Check the index columns on the table, and make sure u are using the index columns also in the where clause. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
Since you are not selecting any columns from V1GCMSFN SFN. You can only do existence checking on SFN.
Also, if your cobol program is not compiled in a recent time, see if relinking helps you with the original query.
Check to see if below works for you.
| Code: |
SELECT LNP.ML_SC_NO
,LNP.RPO_ORI_CSH_AM
,LNP.TD_QY
,SCP.SC_PX_DVS_FCR_RT
,LNP.RPO_INT_RT
FROM V1GCMLNP LNP, V1GCMSCP SCP
WHERE LNP.AT_VL_DT = :BGL-COB-DT
AND LNP.ML_SC_NO = :BGL-ML-SC-NO
AND LNP.ML_SC_NO = SCP.ML_SC_NO
AND LNP.TD_STA_CD = 'L'
AND LNP.TD_QY <> 0
AND LNP.RPO_ORI_CSH_AM <> 0
AND EXISTS (SELECT 1
FROM V1GCMSFN SFN
WHERE SFN.CLI_COPER_ID = 8074
AND SFN.EXT_ML_AC_NO = LNP.DTC_BR_NO)
WITH UR |
Thanks, |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| I will suggest you rebind your plan to the current stats to se if this improves you Cobol execution. If not, then you should look into XPLAINs of the dynamic access vs. the application plan access. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
To repeat - are you sure that the slowness of the cobol process is this sql?
Has the data volume changed since this was originally implemented? |
|
| Back to top |
|
|
k_v_mahesh
New User
Joined: 25 May 2010
Posts: 15
Location: chennai
|
|
|
|
| Did you try to check the compatibility of the data types in the where clause host variables. |
|
| Back to top |
|
|
|
|
