View previous topic :: View next topic
|
Author |
Message |
nileshyp
New User
Joined: 22 Jun 2005 Posts: 65 Location: Mumbai
|
|
|
|
Hi
I am running a with a join which is taking too long to execute whereas When I give separate queries I get below result instantly
Code: |
---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) FROM GCMTEBT.PH01T19_PROD
WHERE PRD_BUS_STC_CD NOT IN ('1');
---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+
21987
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) FROM GCMTEBT.PH01T05_COST
WHERE PRD_STA_CD NOT IN ('CLOSED');
---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+
241071
|
Where as when I give below query it takes tremendous time to execute.
Code: |
SELECT A.PRD_LNG_NAM
,A.PRD_NO
,A.PRD_BUS_STC_CD
,A.PRD_TX_NO
,B.PRD_STA_CD
FROM GCMTEBT.PH01T19_PROD A, GCMTEBT.PH01T05_COST B
WHERE A.PRD_BUS_STC_CD NOT IN ('1')
AND B.PRD_STA_CD NOT IN ('CLOSED')
AND A.PFT_CTR_CD = B.PFT_CTR_CD;
|
Could some let me know the way out to fine tune this query so that it will not take such a long time to execute.
Thanks & Regards,
Neil[/code] |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi,
Which columns are primaray key for both tables and also which columns are having indexes?
I am assuming that you does not want to select the rows which satisfies the below criteria
Code: |
A.PRD_BUS_STC_CD = 1 or B.PRD_STA_CD = 'CLOSED' |
I have not tested this,but try with below query and see if it works for you
Code: |
SELECT A.PRD_LNG_NAM
,A.PRD_NO
,A.PRD_BUS_STC_CD
,A.PRD_TX_NO
,B.PRD_STA_CD
FROM GCMTEBT.PH01T19_PROD A,
GCMTEBT.PH01T05_COST B
WHERE A.PFT_CTR_CD = B.PFT_CTR_CD
AND NOT EXISTS
( SELECT A1.PFT_CTR_CD
FROM GCMTEBT.PH01T19_PROD A1
,GCMTEBT.PH01T05_COST B1
WHERE A1.PFT_CTR_CD = A.PFT_CTR_CD
AND B1.PFT_CTR_CD = A1.PFT_CTR_CD
AND (A.PRD_BUS_STC_CD = 1 OR
B1.PRD_STA_CD = 'CLOSED')) |
Regards,
Chandan |
|
Back to top |
|
|
nileshyp
New User
Joined: 22 Jun 2005 Posts: 65 Location: Mumbai
|
|
|
|
Hi Chandan,
Thanks for the help. However I tried running query you suggested above but I am getting result as follows
Code: |
DSNE626I MAXIMUM OUTPUT LINES FOR SELECT STATEMENT REACHED (999999), PROCESSING FOR CURRENT SELECT STATEMENT TERMINATED
DSNE610I NUMBER OF ROWS DISPLAYED IS 999999
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
|
I guess result is not proper considering individual queries return 21987 & 241071 respectively.
I would appreciate if someone could help me on the above issue.
Thanks & Regards,
Neil |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
What are different values possible for the below columns.
A.PRD_BUS_STC_CD
B.PRD_STA_CD |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Neil,
Sorry I guess I did a typo error in above query
it needs to be A1.PRD_BUS_STC_CD = 1
corrected query is below
Code: |
SELECT A.PRD_LNG_NAM
,A.PRD_NO
,A.PRD_BUS_STC_CD
,A.PRD_TX_NO
,B.PRD_STA_CD
FROM GCMTEBT.PH01T19_PROD A,
GCMTEBT.PH01T05_COST B
WHERE A.PFT_CTR_CD = B.PFT_CTR_CD
AND NOT EXISTS
( SELECT A1.PFT_CTR_CD
FROM GCMTEBT.PH01T19_PROD A1
,GCMTEBT.PH01T05_COST B1
WHERE A1.PFT_CTR_CD = A.PFT_CTR_CD
AND B1.PFT_CTR_CD = A1.PFT_CTR_CD
AND (A1.PRD_BUS_STC_CD = 1 OR
B1.PRD_STA_CD = 'CLOSED')) |
Also for a given PFT_CTR_CD how many rows will be present in with
Code: |
A.PFT_CTR_CD = B.PFT_CTR_CD |
Regards,
Chandan |
|
Back to top |
|
|
nileshyp
New User
Joined: 22 Jun 2005 Posts: 65 Location: Mumbai
|
|
|
|
Hi Chandan,
I tried given solution as well. It returned 0 rows
Code: |
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
|
How come separate queries gives rows but join does not work?
Regards,
Neil |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Neil,
Can you provide sample data from your both tables showing which rows are expected in output.
Regards,
Chandan |
|
Back to top |
|
|
Parthiban DS
New User
Joined: 07 Aug 2011 Posts: 5 Location: India
|
|
|
|
My understanding is, for every row in PH01T19_PROD, multiple records will be present in PH01T05_COST and I think your requirement is to extract matching records with below condition.
A.PFT_CTR_CD = B.PFT_CTR_CD
AND
B.PRD_STA_CD NOT IN ('CLOSED')
AND
A.PRD_BUS_STC_CD NOT IN ('1')
Please try below Query.
Code: |
SELECT COUNT(*)
FROM GCMTEBT.PH01T19_PROD A
WHERE EXISTS
(SELECT 1
FROM GCMTEBT.PH01T05_COST B
WHERE A.PFT_CTR_CD = B.PFT_CTR_CD
AND B.PRD_STA_CD NOT IN ('CLOSED')
)
AND A.PRD_BUS_STC_CD NOT IN ('1')
WITH UR;
|
Let me know the result. |
|
Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010 Posts: 168 Location: Pune
|
|
|
|
nileshyp wrote: |
Hi
I am running a with a join which is taking too long to execute whereas When I give separate queries I get below result instantly
Code: |
---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) FROM GCMTEBT.PH01T19_PROD
WHERE PRD_BUS_STC_CD NOT IN ('1');
---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+
21987
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) FROM GCMTEBT.PH01T05_COST
WHERE PRD_STA_CD NOT IN ('CLOSED');
---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+
241071
|
Where as when I give below query it takes tremendous time to execute.
Code: |
SELECT A.PRD_LNG_NAM
,A.PRD_NO
,A.PRD_BUS_STC_CD
,A.PRD_TX_NO
,B.PRD_STA_CD
FROM GCMTEBT.PH01T19_PROD A, GCMTEBT.PH01T05_COST B
WHERE A.PRD_BUS_STC_CD NOT IN ('1')
AND B.PRD_STA_CD NOT IN ('CLOSED')
AND A.PFT_CTR_CD = B.PFT_CTR_CD;
|
Could some let me know the way out to fine tune this query so that it will not take such a long time to execute.
Thanks & Regards,
Neil[/code] |
Try this
Code: |
SELECT A.PRD_LNG_NAM
,A.PRD_NO
,A.PRD_BUS_STC_CD
,A.PRD_TX_NO
,B.PRD_STA_CD
FROM GCMTEBT.PH01T19_PROD A, GCMTEBT.PH01T05_COST B
where A.PFT_CTR_CD = B.PFT_CTR_CD
AND A.PRD_BUS_STC_CD <> '1'
AND B.PRD_STA_CD <> 'CLOSED'
WITH UR;
|
Check few things:
1. Is the index defined on the columns used in the join clause
A.PFT_CTR_CD = B.PFT_CTR_CD for the tables A and B
2. Why so you want to use IN clause when you have only 1 value in it. IN clause is one of the slowest in SQL.
Try if you get any difference le us know. |
|
Back to top |
|
|
bauer
New User
Joined: 03 Mar 2009 Posts: 28 Location: germany
|
|
|
|
What are the results of Explain Command ? |
|
Back to top |
|
|
|