Select Query time taking

[Former Member]

Hi,

I have developed an ALV report,in that Select query based on MSEG_MKPF is taking more time.I have tuned it with different ways,but still it is taking more time.Can anyone tell me?

Thanks

[former_member187748]

Hi Sai,

can you provide the code, you have written.

Let people see, whats wrong with it.

[Former Member]

Hi Sanjeev,

Here is my code..

*&---------------------------------------------------------------------*

*& Report  ZMM_STOCK_RECEIPT

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT  ZMM_STOCK_RECEIPT.

type-pools:slis.

TABLES: T001W,S032.

types:begin of ty_s032,

      werks type s032-werks,

      lgort type s032-lgort,

      matnr type s032-matnr,

      LETZTZUG type s032-LETZTZUG,

      end of ty_s032.

types:begin of ty_mkpf,

      mblnr type mkpf-mblnr,

      budat type mkpf-budat,

      end of ty_mkpf.

types:begin of ty_mseg,

      mblnr type mseg-mblnr,

      bwart type mseg-bwart,

      matnr type mseg-matnr,

      werks type mseg-werks,

      lgort type mseg-lgort,

      end of ty_mseg.

types:begin of ty_t001w,

      WERKS type t001w-WERKS,

      NAME1 type t001w-NAME1,

      end of ty_t001w.

types:begin of ty_mbew,

      MATNR type mbew-MATNR,

      BWKEY type mbew-BWKEY,

      LBKUM type mbew-LBKUM,

      end of ty_mbew.

types:begin of ty_makt,

      MATNR type makt-MATNR,

      MAKTX type makt-MAKTX,

      end of ty_makt.

types:begin of ty_final,

      werks type t001w-werks,

      name1 type t001w-name1,

      matnr type s032-matnr,

      maktx type makt-maktx,

      budat type WB2_V_MKPF_MSEG2-budat,

      LETZTZUG type s032-LETZTZUG,

      lbkum type mbew-lbkum,

      end of ty_final.

data:it_s032 type STANDARD TABLE OF ty_s032,

     wa_s032 type ty_s032.

data:it_mkpf type STANDARD TABLE OF ty_mkpf,

     wa_mkpf type ty_mkpf.

data:it_mseg type STANDARD TABLE OF ty_mseg,

     wa_mseg type ty_mseg.

data:it_t001w type STANDARD TABLE OF ty_t001w,

     wa_t001w type ty_t001w.

data:it_mbew type STANDARD TABLE OF ty_mbew,

     wa_mbew type ty_mbew.

data:it_makt type STANDARD TABLE OF ty_makt,

     wa_makt type ty_makt.

data:it_final type STANDARD TABLE OF ty_final,

     wa_final type ty_final.

DATA: it_fcat TYPE  slis_t_fieldcat_alv,

      wa_fcat TYPE slis_fieldcat_alv,

      wa_layout TYPE slis_layout_alv,

      it_sort TYPE slis_t_sortinfo_alv,

      wa_sort TYPE slis_sortinfo_alv.

SELECTION-SCREEN begin of block b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_site for t001w-werks OBLIGATORY,

                s_matnr for s032-matnr.

SELECTION-SCREEN end of block b1.

START-OF-SELECTION.

select werks

       lgort

       matnr

       LETZTZUG

       from s032

       into table it_s032

       where werks in s_site

       and matnr in s_matnr

       and lgort = 'SL01'

       and LETZTZUG ne '00000000'.

  if it_s032 is initial.

    MESSAGE 'No data is available' type 'I'.

    STOP.

  endif.

  if it_s032 is not INITIAL.

    select mblnr

           bwart

           matnr

           werks

           lgort

           from mseg

           into TABLE it_mseg

           FOR ALL ENTRIES IN it_s032

           where bwart = '101'

           and matnr = it_s032-matnr

           and werks = it_s032-werks

           and lgort = 'SL01'.

   endif.

   if it_mseg is not initial.

     select mblnr

            budat

            from mkpf

            into table it_mkpf

            FOR ALL ENTRIES IN it_mseg

            where mblnr = it_mseg-mblnr.

   endif.

  if it_s032 is not INITIAL.

    select MATNR

           BWKEY

           LBKUM

           from mbew

           into TABLE it_mbew

           FOR ALL ENTRIES IN it_s032

           where matnr = it_s032-matnr

           and bwkey = it_s032-werks.

  endif.

  SELECT werks

         name1

         from t001w

         into TABLE it_t001w.

select matnr

        maktx

        from makt

        into table it_makt.

sort it_mkpf by mblnr budat.

loop at it_s032 into wa_s032.

  wa_final-matnr = wa_s032-matnr.

  wa_final-LETZTZUG = wa_s032-LETZTZUG.

   CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

        EXPORTING

          input  = wa_final-matnr

        IMPORTING

          output = wa_final-matnr.

  READ TABLE it_mseg into wa_mseg with key matnr = wa_s032-matnr

                                           werks = wa_s032-werks.

read table it_mkpf into wa_mkpf with key mblnr = wa_mseg-mblnr.

  if sy-subrc = 0.

    wa_final-budat = wa_mkpf-budat.

  endif.

  READ TABLE it_mbew into wa_mbew with key matnr = wa_s032-matnr

                                           bwkey = wa_s032-werks.

  if sy-subrc = 0.

    wa_final-lbkum = wa_mbew-lbkum.

  endif.

  READ TABLE it_t001w into wa_t001w with key werks = wa_s032-werks.

  if sy-subrc = 0.

    wa_final-werks = wa_t001w-werks.

    wa_final-name1 = wa_t001w-name1.

  endif.

  read TABLE it_makt into wa_makt with key matnr = wa_s032-matnr.

  if sy-subrc = 0.

    wa_final-maktx = wa_makt-maktx.

  endif.

  append wa_final to it_final.

  clear:wa_final.

  clear:wa_s032,wa_mseg,wa_mkpf,wa_t001w,wa_makt,wa_mbew.

endloop.

  wa_fcat-fieldname = 'WERKS'.

  wa_fcat-col_pos = '1'.

  wa_fcat-seltext_l = 'Site'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_fcat-fieldname = 'NAME1'.

  wa_fcat-col_pos = '2'.

  wa_fcat-seltext_l = 'Site Name'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_fcat-fieldname = 'MATNR'.

  wa_fcat-col_pos = '3'.

  wa_fcat-seltext_l = 'Article'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_fcat-fieldname = 'MAKTX'.

  wa_fcat-col_pos = '4'.

  wa_fcat-seltext_l = 'Description'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_fcat-fieldname = 'BUDAT'.

  wa_fcat-col_pos = '5'.

  wa_fcat-seltext_l = 'First Receipt'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_fcat-fieldname = 'LETZTZUG'.

  wa_fcat-col_pos = '6'.

  wa_fcat-seltext_l = 'Last Receipt'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_fcat-fieldname = 'LBKUM'.

  wa_fcat-col_pos = '7'.

  wa_fcat-seltext_l = 'SOH Qty'.

  wa_fcat-tabname = 'IT_FINAL'.

  APPEND wa_fcat TO it_fcat.

  CLEAR wa_fcat.

  wa_sort-fieldname = 'WERKS'.

  wa_sort-tabname = 'IT_FINAL'.

  wa_sort-up = 'X'.

  APPEND wa_sort TO it_sort .

  CLEAR wa_sort.

  wa_sort-fieldname = 'NAME1'.

  wa_sort-tabname = 'IT_FINAL'.

  wa_sort-up = 'X'.

  APPEND wa_sort TO it_sort .

  CLEAR wa_sort.

  CLEAR wa_layout.

  wa_layout-colwidth_optimize = 'X'." Optimization of Col width

  wa_layout-no_totalline = 'X'.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

   EXPORTING

*     I_INTERFACE_CHECK                 = ' '

*     I_BYPASSING_BUFFER                = ' '

*     I_BUFFER_ACTIVE                   = ' '

     I_CALLBACK_PROGRAM                = sy-cprog

*     I_CALLBACK_PF_STATUS_SET          = ' '

*     I_CALLBACK_USER_COMMAND           = ' '

*     I_CALLBACK_TOP_OF_PAGE            = ' '

*     I_CALLBACK_HTML_TOP_OF_PAGE       = ' '

*     I_CALLBACK_HTML_END_OF_LIST       = ' '

*     I_STRUCTURE_NAME                  =

*     I_BACKGROUND_ID                   = ' '

*     I_GRID_TITLE                      =

*     I_GRID_SETTINGS                   =

     IS_LAYOUT                         = wa_layout

     IT_FIELDCAT                       = it_fcat

*     IT_EXCLUDING                      =

*     IT_SPECIAL_GROUPS                 =

     IT_SORT                           = it_sort

*     IT_FILTER                         =

*     IS_SEL_HIDE                       =

*     I_DEFAULT                         = 'X'

*     I_SAVE                            = ' '

*     IS_VARIANT                        =

*     IT_EVENTS                         =

*     IT_EVENT_EXIT                     =

*     IS_PRINT                          =

*     IS_REPREP_ID                      =

*     I_SCREEN_START_COLUMN             = 0

*     I_SCREEN_START_LINE               = 0

*     I_SCREEN_END_COLUMN               = 0

*     I_SCREEN_END_LINE                 = 0

*     I_HTML_HEIGHT_TOP                 = 0

*     I_HTML_HEIGHT_END                 = 0

*     IT_ALV_GRAPHICS                   =

*     IT_HYPERLINK                      =

*     IT_ADD_FIELDCAT                   =

*     IT_EXCEPT_QINFO                   =

*     IR_SALV_FULLSCREEN_ADAPTER        =

*   IMPORTING

*     E_EXIT_CAUSED_BY_CALLER           =

*     ES_EXIT_CAUSED_BY_USER            =

    TABLES

      t_outtab                          = it_final.

*   EXCEPTIONS

*     PROGRAM_ERROR                     = 1

*     OTHERS                            = 2

            .

  IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

  ENDIF.

[nabheetscn]

first of all a simple change like why are you not passing MJAHR in select on MSEG when it is available from MKPF..?

Nabheet

[Former Member]

step thru each select and see which one is the hog.

in the first instance, I would try this, before doing something else:

you are using 'for all entries' - ensure that your table your are using for the 'for all entries' is not initial before undertaking the for all entries.

better still - implement joins or find a view and see if that improves the run time.

[Former Member]

Hi Use the secondary index for the MSEG table!

for MKPF : use the below fields in where condition

MBLNR

MJAHR

[former_member187748]

Hi Sai,

please see this code and change it accordingly, and see is it improves or not, if yes, then do it for

mkpf table too.

START-OF-SELECTION.

select werks

        lgort

        matnr

        LETZTZUG

        from s032

        into CORRESPONDING FIELDS OF TABLE  it_s032

        where werks in s_site

        and matnr in s_matnr

        and lgort = 'SL01'

        and LETZTZUG ne '00000000'.

   if it_s032 is initial.

     MESSAGE 'No data is available' type 'I'.

     STOP.

   endif.

   if it_s032 is not INITIAL.

     select mblnr

            bwart

            matnr

            werks

            lgort

            from mseg

            into CORRESPONDING FIELDS OF TABLE  it_mseg

            FOR ALL ENTRIES IN it_s032

*           where bwart = '101'

            where matnr = it_s032-matnr

            and werks = it_s032-werks

            and lgort = 'SL01'

            and bwart = '101' .

    endif.

[hendrik_brandes]

Hello Sai,

I would suggest, to give us some more informations:

* What is your SQL-Statement on MSEG_MKPF

* What does it mean "taking more time"? How long do you expect and how long does it take?

* Did you make a SQL-Explain and see what execution plan is taken?

Kind regards,

Hendrik

[Former Member]

Fetching from a cluster table is usually slow. If you want a better performance, try to select it from its corresponding transparent tables. Like BSID, BSAD, etc.

You might end up having more select clauses, because all the fields you are interested in, may not be in one table. But the performance will be better.

[Former Member]

Hi Sai,

     Check the SAP note 821722 - Performance of the join on MSEG and MKPF on performance issues related to these 2 tables. these are tables with huge volumes so if possible try to retrict the amount of data you are trying to retrieve.

Regards,

Umar

[former_member202771]

Hi Sai,

Try using PACKAGE SIZE n along with the select query.

Thanks,

Anil

[Former Member]

Hi Anil,

How to use package size in select query,I don't have idea on that.Can you please tell me?

Thanka

[former_member43585]

for fetching data from MKPF table try below given code.

if it_mseg is not initial.

     select mblnr

            budat

            from mkpf

            into table it_mkpf

            FOR ALL ENTRIES IN it_mseg

            where mblnr = it_mseg-mblnr

               AND mjahr = it_mseg-mjahr.

   endif.

[RaymondGiuseppi]

First "more time" than what ?

Then FOR ALL ENTRIES gives often (very) wrong performance for big volume of data.Try to use JOIN and sub-query in your code and compare performance with SAT and ST05 (Abap and SQL trace) -> use search tool

Sample (syntax help)

SELECT mseg~mblnr mseg~bwart mseg~matnr mseg~werks mseg~lgort mkpf~budat
   INTO CORRESPONDING FIELDS OF TABLE it_mseg_mkpf
   FROM mseg
   JOIN mkpf
     ON  mkpf~mblnr EQ mseg~mblnr
     AND mkpf~mjahr EQ mseg~mjahr
   WHERE bwart EQ '101'
     AND werks IN s_site
     AND matnr IN s_matnr
     AND lgort = 'SL01'
     AND EXISTS ( SELECT * FROM s032
                   WHERE matnr EQ mseg~matnr
                     AND werks EQ mseg~werks
                     AND lgort EQ mseg~lgort " or 'SL01'
                     AND letztzug NE '00000000' ) .

Regards,

Raymond

[Former Member]

Hi Raymond,

Even i have tried inner joins also...but it is taking more time.