topic performance issue in select query in Application Development and Automation Discussions

performance issue in select query

Former Member — Fri, 29 Jan 2010 05:08:06 GMT

Hi gurus.....

this is my code......have performance issue......in following steps...

1. there are 6 select query with tables,

VBAK

VBAP

VBRP

VBEP

zsd_lips_vttk

2. LOOP AT T_VBAP AND FILL ALL DATA TO T_FINAL.

TIME CONSUMING STEPS ARE....

DATA COMING IN T_VBRP TABLE.

DATA COMING IN T_zsd_lips_vttk TABLE.

DATA COMING IN it_MKPF_MSEG TABLE.

TIME CONSUMING IN LOOP ALSO.

Moderator message - Please respect the 2,500 character maximum when posting. Post only the relevant portions of code

regards

rahul

Edited by: Rob Burbank on Jan 29, 2010 8:57 AM

Re: performance issue in select query

Former Member — Fri, 29 Jan 2010 05:18:38 GMT

1.Avoid CORRESPONDING FIELDS OF TABLE .

2.Before READ u sort the internal table with key.

3.Joining two tables use Views

Re: performance issue in select query

Former Member — Fri, 29 Jan 2010 09:54:36 GMT

Learn how to use Code !!!


SELECT mblnr budat 
          FROM mkpf 
          INTO TABLE t_mkpf 
          WHERE budat IN s_budat. * * * * 

IF NOT t_mkpf[] IS INITIAL. * 
     SELECT mblnr MJAHR mat_kdauf mat_kdpos menge 
              FROM mseg 
              INTO TABLE t_mseg * 
             FOR ALL ENTRIES IN t_mkpf[] * 
             WHERE mblnr = t_mkpf-mblnr 
          and * MJAHR in s_budat. * 
ENDIF. 
        select mkpf_mblnr budat MJAHR mat_kdauf mat_kdpos menge 
        from Z_MKPF_MSEG 
         into corresponding fields of table it_MKPF_MSEG 
       where budat in s_budat and MJAHR in s_budat. 

delete ADJACENT DUPLICATES FROM it_MKPF_MSEG 
             COMPARING ALL FIELDS. * 

IF NOT s_budat IS INITIAL. * 
LOOP AT t_vbap INTO w_vbap. * 
      READ TABLE t_mseg WITH KEY mat_kdauf = w_vbap-vbeln mat_kdpos = w_vbap-posnr. 

 IF sy-subrc EQ 0. * 
     DELETE t_vbap WHERE vbeln = w_vbap-vbeln AND posnr = w_vbap-posnr. * 
ENDIF. * 
ENDLOOP. * ENDIF. 

IF NOT s_budat IS INITIAL. 
 LOOP AT t_vbap INTO w_vbap. 
      READ TABLE it_MKPF_MSEG WITH KEY mat_kdauf = w_vbap-vbeln mat_kdpos = w_vbap-posnr. 
  IF sy-subrc EQ 0.  
   DELETE t_vbap WHERE vbeln = w_vbap-vbeln AND posnr = w_vbap-posnr. 
  ENDIF. 
 ENDLOOP. 
ENDIF.

The INTO CORRESPONDING is o.k., no problem

A JOIN and a view are identical, no performance effect.

Use sorted tables or sort standard tables and use binary search !!!

But your problems are different:

1. The DELETE ADJACENT DUPLICATES requires the table to be sorted in the same order !!!

2. The 'IF NOT s_budat IS INITIAL. should could once around the whole coding, already the first SELECT does not make sense! 3. Performance is spent in the DELETE WHERE use DELETE with index.

4. Usually I would recommend the positive logic:

Re: performance issue in select query

Former Member — Fri, 29 Jan 2010 09:55:59 GMT


 IF sy-subrc EQ 0. * 
     sort t_vbap by vbeln posnr.
     sort t_mseg by kdauf kdpos.
     sort t_mkpf_mseg by kdauf kdpos.
     DELETE t_vbap WHERE vbeln = w_vbap-vbeln AND posnr = w_vbap-posnr. * 

     LOOP AT t_vbap INTO w_vbap. * 
          READ TABLE t_mseg 
                    WITH KEY mat_kdauf = w_vbap-vbeln mat_kdpos = w_vbap-posnr
                    BINARY SEARCH. 

          IF sy-subrc <> 0.  
             READ TABLE it_MKPF_MSEG 
                       WITH KEY mat_kdauf = w_vbap-vbeln mat_kdpos = w_vbap-posnr
                        BINARY SEARCH. 
             IF sy-subrc <> 0.  
               APPEND w_vbap TO t_vbap_remain.
            ENDIF. 
         ENDIF. 
 ENDLOOP.