topic Re: Select Query time taking in Application Development and Automation Discussions

Select Query time taking

Former Member — Thu, 16 Jan 2014 08:31:05 GMT

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

Re: Select Query time taking

former_member187748 — Thu, 16 Jan 2014 08:37:37 GMT

Hi Sai,

can you provide the code, you have written.

Let people see, whats wrong with it.

Re: Select Query time taking

hendrik_brandes — Thu, 16 Jan 2014 08:38:18 GMT

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

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 09:22:40 GMT

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.

Re: Select Query time taking

nabheetscn — Thu, 16 Jan 2014 09:25:05 GMT

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

Nabheet

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 09:29:46 GMT

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.

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 09:31:00 GMT

Hi Use the secondary index for the MSEG table!

for MKPF : use the below fields in where condition

MBLNR

MJAHR

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 09:35:09 GMT

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.

Re: Select Query time taking

former_member187748 — Thu, 16 Jan 2014 09:37:06 GMT

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.

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 10:09:36 GMT

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

Re: Select Query time taking

former_member202771 — Thu, 16 Jan 2014 10:12:07 GMT

Hi Sai,

Try using PACKAGE SIZE n along with the select query.

Thanks,

Anil

Re: Select Query time taking

former_member43585 — Thu, 16 Jan 2014 10:32:52 GMT

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.

Re: Select Query time taking

RaymondGiuseppi — Thu, 16 Jan 2014 10:34:37 GMT

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

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 11:27:52 GMT

Hi Anil,

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

Thanka

Re: Select Query time taking

Former Member — Thu, 16 Jan 2014 11:32:08 GMT

Hi Raymond,

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