MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

How to refine the query to make faster

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
shyamsaravan
Beginner


Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
PostPosted: Mon Feb 06, 2012 8:42 am    Post subject: How to refine the query to make faster Reply with quote
Hi;

Facing the performance issue..it takes more time to execute the below query on real system and got time expired...error
Code:


SELECT t1.p_la
     , t1.p1_nn
     , t1.p2_nn
     , t1.p_dd
     , t2.t_no
     , t2.t_cd
     , t3.t_sr
     , t3.t_ds   as t_ds1
     , t3_1.t_ds as t_ds2
 FROM
       table1 t1
left outer JOIN
       table2 t2

ON                                               
      t2  .t_no   =  t1.p_la
 AND  t2.p_no =               
      t1.p1_nn || '/'   
   || t1.p2_nn   
     

inner join
  table3 t3


ON  (t3.t_it =  t2.t_no
     or
     t3.t_it =  t1.p_la)
 
 LEFT  OUTER JOIN
       table3 t3_1
   ON  t3_1.t_it =  t2.t_cd
 ORDER BY
       t1.p_la
      ,t1.p1_nn
      ,t1.p2_nn
     


Code:


Table1

p_la   p1_Nn  p2_nn          p_d
A11     12       34          aaa
A11     3        334         aaa

Table2

t_no     p_no    t_cd
A11      12/34    A11
A11      3/334   

Table3

t_IT           t_sr      t_ds
A11             xxx        sout

Result set

p_La         p1_nn   p2_nn         p-d      t_no     t-cd         t_sr      t_ds1       t-ds2
A11           12       34          aaa       A11      A11         xxx       SOUT       SOUT
A11           3       334          aaa       A11                  xxx       SOUT       

Table1 has 50000 reocrds Table2 has 15000 records and Table3 has 600000 records..
Based on the condition in the Where clause the maximum number of rows will be the 5000 records as a output resultset..for even getting of 2000 rows in a result set also getting time expired error...
How to resolve it ?Pl help..
Back to top
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
PostPosted: Mon Feb 06, 2012 9:34 am    Post subject: Reply with quote
what does the output of EXPLAIN tell you.

We have no idea what the column definitions of the tables look like,
infact, we know nothing about your db2 version,
the activity on the tables at the time you run your selects,
nor do we know the reason code provided with the "time expired error".
_________________
Dick Brenholtz
American living in Varel, Germany
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Mon Feb 06, 2012 11:32 am    Post subject: Reply with quote
shyamsaravan,

Please search before posting.

Check this link

http://mvsforums.com/helpboards/viewtopic.php?t=587&highlight=explain

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group