|
View previous topic :: View next topic
|
| Author |
Message |
sreejeshcs
New User
Joined: 28 May 2007
Posts: 31
Location: Pune
|
|
|
|
Hi Below SQL is taking huge CPU time. Can some help me to fine tune it?
| Code: |
SELECT A.INTCMNBR
FROM TABLE1 A
WHERE A.CID = 12345
AND A.CTRY = 263
AND A.CMNBR NOT IN
( SELECT B.CM_NO
FROM TABLE2 B
WHERE B.CTRY_CD='263'
AND B.CORP=12345
AND B.CM_NO=A.CMNBR) |
|
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
what have you tried so far?
Did you try executing an EXPLAIN?? |
|
| Back to top |
|
|
sreejeshcs
New User
Joined: 28 May 2007
Posts: 31
Location: Pune
|
|
|
|
| No... |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Please try that
In order to do that first create a plan_table |
|
| Back to top |
|
|
sreejeshcs
New User
Joined: 28 May 2007
Posts: 31
Location: Pune
|
|
|
|
| I dint get. can u explain in details reg plan_table? |
|
| Back to top |
|
|
Prakash J
New User
Joined: 23 May 2012
Posts: 14
Location: India
|
|
|
|
Sree,
1. Create the PLAN_TABLE with your User ID.
CREATE TABLE Userid.PLAN_TABLE LIKE DEFAULT.PLAN_TABLE;
2. EXPLAIN PLAN SET QUERYNO = 1 FOR [SQL Query];
3. Retrieve the information from PLAN_TABLE using
SELECT * FROM PLAN_TABLE WHERE QUERYNO = 1 and check for the information ACCESSTYPE, MATCHCOLS, ACCESSNAME PREFETCH etc.
For more information about these columns, please check out DB2 for z/OS Performance Monitoring and Tuning Guide.
- Prakash |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
try this for both tables and show us the output:
| Code: |
SELECT
I.TBCREATOR, I.TBNAME, I.CREATOR, I.NAME
, I.UNIQUERULE, I.CLUSTERRATIO
, I.FIRSTKEYCARD, I.FULLKEYCARD, K.COLSEQ,
K.COLNAME,k.ordering
FROM SYSIBM.SYSINDEXES I
INNER JOIN SYSIBM.SYSKEYS K
ON I.CREATOR = K.IXCREATOR AND I.NAME = K.IXNAME
WHERE (I.TBCREATOR = &creator) AND (I.TBNAME like &tblike)
ORDER BY i.tbcreator, i.tbname, i.name, k.colseq |
|
|
| Back to top |
|
|
Santhosh Menon
New User
Joined: 19 Jun 2012
Posts: 4
Location: USA
|
|
|
|
Sree,
Explain would be the best option.....
Also for a quick hit... try the below 2 options if u see a diff.
1) Changing Not IN to Not EXISTS
2) Changing the Query to a Left join. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you notice that Sree has not provided the requested info and most likely has this running or moved on to something else.
Also, notice that Explain was mentioned above and the way to get started.
Lastly, if he cannot get an Explain, running the query provided by GuyC and posting the result would be helpful. |
|
| Back to top |
|
|
Santhosh Menon
New User
Joined: 19 Jun 2012
Posts: 4
Location: USA
|
|
|
|
| My bad. Just trying to help ! Will be more careful the next time |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Menon,
Nah, not so bad - no foul 
It is good to want to help - we appreciate this!
Just wanted to mention that some topics are better candidates for a reply than others. |
|
| Back to top |
|
|
|
|
