Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need help on query tuning

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nileshyp

New User


Joined: 22 Jun 2005
Posts: 65
Location: Mumbai

PostPosted: Fri Mar 16, 2012 8:22 am    Post subject: Need help on query tuning
Reply with quote

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
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Mar 16, 2012 9:27 am    Post subject:
Reply with quote

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
View user's profile Send private message
nileshyp

New User


Joined: 22 Jun 2005
Posts: 65
Location: Mumbai

PostPosted: Fri Mar 16, 2012 12:09 pm    Post subject: Reply to: Need help on query tuning
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Mar 16, 2012 3:21 pm    Post subject:
Reply with quote

What are different values possible for the below columns.

A.PRD_BUS_STC_CD
B.PRD_STA_CD
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Mar 16, 2012 3:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
nileshyp

New User


Joined: 22 Jun 2005
Posts: 65
Location: Mumbai

PostPosted: Fri Mar 16, 2012 3:43 pm    Post subject: Reply to: Need help on query tuning
Reply with quote

Hi Chandan,

I tried given solution as well. It returned 0 rows icon_sad.gif

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Mar 16, 2012 5:05 pm    Post subject:
Reply with quote

Hi Neil,

Can you provide sample data from your both tables showing which rows are expected in output.

Regards,
Chandan
Back to top
View user's profile Send private message
Parthiban DS

New User


Joined: 07 Aug 2011
Posts: 5
Location: India

PostPosted: Wed Mar 21, 2012 8:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Thu Mar 22, 2012 12:28 am    Post subject: Re: Need help on query tuning
Reply with quote

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
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Thu Mar 22, 2012 12:56 pm    Post subject:
Reply with quote

What are the results of Explain Command ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 3 Mon Dec 05, 2016 11:57 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us