IBM Mainframe Forum

Mainframe Technical Support Forums

Skip to content


Hoe to reduce the Query running time



IBM's flagship relational database management system
Post a reply

Hoe to reduce the Query running time

Postby shyamsaravan » Fri Jul 01, 2011 7:05 pm

Hi;

I have pasted the codes below is the Stored Procedure and the values feed from the Front-end screen to the DB2 StroedProc and fetching the values from the DB2 tables,.The question is why do this query taking more than 30 minutes to display the result...the datas are also somewhat 50k records

Select all
SELECT                   
     DISTINCT             
       sam.Place 
     , sam.NO_mode_name
     , sam.NO_mob_name 
     , sam.NO_disp_name
     , sam.NO_rate_type
     , sam.ran_date
     , sam.NO_pers_CNTL   
FROM                                               
    cus_frt_tab   sam,                           
    Pen_tab       ben                           
WHERE
                                             

    (sam.NO_mob_name   BETWEEN :WS-ALL-mob-LOW   
                            AND  :WS-ALL-mob-HIGH)
AND (sam.NO_mode_name BETWEEN :WS-ALL-mode-LOW   
                            AND  :WS-ALL-mode-HIGH)
AND (sam.NO_disp_name BETWEEN :WS-ALL-disp-LOW   
                            AND  :WS-ALL-disp-HIGH)

AND sam.NO_rate_type      IN ( :LT-DEL ,     
                                  :LT-INS ,       
                                  :LT-UPD  )     

 AND DATE (sam.ran_date)                       
                          BETWEEN :WS-START-DT
                             AND  :WS-END-DT

 AND                                                 
     (                                               
     (:WS-ALL-DISTR  =  :LT-ALL)    OR           
     ( sam.PLACE           IN  (:DISTR01,         
                                :DISTR02,         
                                :DISTR03,         
                                :DISTR04,         
                                :DISTR05)  )                                         
             )                                         

         AND                                           
             (                                         
             (:WS-ALL-CUS   =  :LT-ALL)  OR         
             (BEN.CD_CONSUMER IN    (:CONS01,         
                                      :CONS02,         
                                      :CONS03,         
                                      :CONS04,         
                                      :CONS05)  )             
             )                                                 

         AND  sam.NO_mob_name   =  ben.NO_mob_name     
         AND  sam.NO_mode_name  =  ben.NO_mode_name       
         AND  sam.NO_disp_name  =  ben.NO_disp_name     
         AND  sam.NO_pers_CNTL  =  ben.sam.NO_pers_CNTL   

         ORDER BY                                             
              sam.Place                           
             ,sam.NO_mode_name                             
             ,sam.NO_mob_name                             
             ,sam.NO_disp_name                           
             ,sam.NO_pers_CNTL                              
             ,sam.Phy_TYPE                             
             ,sam.DT_IN      DESC             


Cobol Declaration

Select all
05  WS-ALL-mob-LOW          PIC X(09)  VALUE SPACES.
05  WS-ALL-mob-HIGH         PIC X(09)  VALUE SPACES.
05  WS-ALL-mode-LOW          PIC X(07)  VALUE SPACES.
05  WS-ALL-mode-HIGH         PIC X(07)  VALUE SPACES.
05  WS-ALL-disp-LOW          PIC X(08)  VALUE SPACES.
05  WS-ALL-disp-HIGH         PIC X(08)  VALUE SPACES.

 05  LT-DEL               PIC X(06) VALUE "DELETE".
 05  LT-INS               PIC X(06) VALUE "INSERT".
 05  LT-UPD               PIC X(06) VALUE "UPDATE".

 05  LT-ALL                  PIC X(01) VALUE "#".

05  WS-START-DT       PIC X(10)  VALUE SPACES.
05  WS-END-DT         PIC X(10)  VALUE SPACES.

05  WS-ALL-DISTR         PIC X(01)  VALUE SPACES.
05  WS-ALL-CUS         PIC X(01)  VALUE SPACES.

 05  WS-DISTR-LIST.                                 
     10  DISTR01           PIC X(05)  VALUE SPACES.
     10  FILLER               PIC X(01)  VALUE SPACES.
     10  DISTR02           PIC X(05)  VALUE SPACES.
     10  FILLER               PIC X(01)  VALUE SPACES.
     10  DISTR03           PIC X(05)  VALUE SPACES.
     10  FILLER               PIC X(01)  VALUE SPACES.
     10  DISTR04           PIC X(05)  VALUE SPACES.
     10  FILLER               PIC X(01)  VALUE SPACES.
     10  DISTR05           PIC X(05)  VALUE SPACES.

05  WS-CONS-CODES.                                 
    10  CONS01              PIC X(04)  VALUE SPACES.
    10  FILLER               PIC X(01)  VALUE SPACES.
    10  CONS02              PIC X(04)  VALUE SPACES.
    10  FILLER               PIC X(01)  VALUE SPACES.
    10  CONS03              PIC X(04)  VALUE SPACES.
    10  FILLER               PIC X(01)  VALUE SPACES.
    10  CONS04              PIC X(04)  VALUE SPACES.
    10  FILLER               PIC X(01)  VALUE SPACES.
    10  CONS05              PIC X(04)  VALUE SPACES.


iN PROCEDURE DIVISION
;
;
Select all
MOVE  LINK-DISTR-TEXT TO  WS-DISTR-LIST
                                WS-ALL-DISTR
                                               
MOVE  LINK-CONS-LIST        TO  WS-ALL-CONS   
                                WS-CONS-CODES 
                                               
IF  WS-ALL-CONS  =  LT-ALL                   
    MOVE  SPACES  TO  WS-CONS-CODES           
ELSE                                           
    UNSTRING                                   
        LINK-CONS-LIST                         
                DELIMITED BY  ','                 
        INTO                                   
          CONS01,                             
          CONS02,                             
          CONS03,                             
          CONS04,
          CONS05
      END-UNSTRING
 END-IF     


Please help me what are possibilites to reduce the Query running time....and let me know that, is delaying the running time if we used the BETWEEN and OR in the Query
shyamsaravan
 
Posts: 40
Joined: Tue May 11, 2010 7:56 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Hoe to reduce the Query running time

Postby GuyC » Fri Jul 01, 2011 7:32 pm

info we need require:

the minimum :
* available indexes ? primary keys ?

extra :
* explain info ?
* column cardinalities
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times
Top

Re: Hoe to reduce the Query running time

Postby BillyBoyo » Fri Jul 01, 2011 8:22 pm

I've made a little change to part of your code. I don't know if this is part of your problem, but if you can have a variable number of items in the LINK-CONS-LIST then you might be using values from the previous execution of the UNSTRING when there are fewer items this time.

Select all
                   
* Initialise all conscodes to space to ensure that any prior values are not used by accident, or for the LT-ALL request, where they should all be space.

MOVE  SPACES  TO  WS-CONS-CODES           
     
IF  WS-ALL-CONS  NOT EQUAL TO  LT-ALL                                     
    UNSTRING                                   
        LINK-CONS-LIST                         
                DELIMITED BY  ','                 
        INTO                                   
          CONS01,                             
          CONS02,                             
          CONS03,                             
          CONS04,
          CONS05
      END-UNSTRING
END-IF
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times
Top

Re: Hoe to reduce the Query running time

Postby shyamsaravan » Fri Jul 08, 2011 5:26 pm

Thanks for the reply;

The above changes does not reflect big difference in the query running time.We used 'place' as a primary key..
Can you please suggest another way.?
If the LINK-DISTR-TEXT value is # means it will show all the records which are related to the given LINK-CONS-LIST.
If the LINK-CONS-LIST value is # means it will show all the records which are related to the given LINK-DISTR-TEXT.
shyamsaravan
 
Posts: 40
Joined: Tue May 11, 2010 7:56 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Hoe to reduce the Query running time

Postby BillyBoyo » Fri Jul 08, 2011 10:53 pm

info we need require:

the minimum :
* available indexes ? primary keys ?

extra :
* explain info ?
* column cardinalities


GuyC asked you for the above information. Mine was just a suggestion to get the Cobol code correct. If you do five, then four, you'll have one left over from the five, with your original code.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times
Top

Re: Hoe to reduce the Query running time

Postby shyamsaravan » Mon Jul 25, 2011 3:36 pm

How to view the Table has Primary keys and indexes in QMF?
shyamsaravan
 
Posts: 40
Joined: Tue May 11, 2010 7:56 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Hoe to reduce the Query running time

Postby dick scherrer » Mon Jul 25, 2011 11:33 pm

Hello,

Suggest you talk with your dba or some co-worker.

You can get what you want with simple queries using spufi and have no need for qmf to just see the key/index definitions.

You might also look at the CREATE statements for these. . . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times
Top

Re: Hoe to reduce the Query running time

Postby rahuldba » Tue Oct 11, 2011 1:01 am

Ask ur DBA to do an explain of the query in the region.Before running explain make sure that runstats are done for the following tables.when the explain is done DBA can come up with suggestions like creating indexes or changing the query..or other reasons.Since ur joining tables so data in talbles as well as indexes be there for joining columns.basically we need explain output to determine what to do next.Aslo all indexes created on tables would be needed.
rahuldba
 
Posts: 5
Joined: Wed Aug 17, 2011 12:08 am
Has thanked: 0 time
Been thanked: 0 time
Top
Post a reply

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post
Forum Rules|FAQ|Subscriptions|Bookmarks