topic Re: Performance Issues in Application Development and Automation Discussions

Performance Issues

Former Member — Fri, 21 Apr 2006 19:04:09 GMT

Hi all,

I have a program which when ran in development takes time for about 7 mins. It accesss mara table and reads material value from that. Total no of recorsd are about 150,000. When I run the same program in Production it takes 6.5 hrs where as the no of recorsds are 300,000. The program has nested select statements. I know that nested select statements are a barrier to performance to the program but how come such a huge difference ? Is there any thing to be taken care of indexes ? The functioanl member says they had a similar problem earlier too, and rebuilding the indexes has resolved the issue? Is it true ? are indexes a concern in this regard ?

Regards,

Vijay.

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:08:09 GMT

Vijay,

what we need to remember the more no. of records it process the time taken increases exponentially. Yes, Index will defintely will give a big boos to you to the performance.

however, I suggest that you relook at the coding, and avoid nested SELECT statements, make use of SORTED, HASED internal tables, which will definitely help you.

Regards,

Ravi

Note : Please mark the helpful answers

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:09:14 GMT

It is not advisable simply to add new indexes without analysing the code. Please post the code so that the forum can suggest improvements. Also do a runtime analysis on it and see where it spends most of its time.

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:22:31 GMT

Hi srinivas,

What you have suggested is good idea. I have already performed runtime analysis using se30 and the result is as follows :

ABAP 2.6%

Database 97.3%

R/3 System 0.1%

Obviously nested select's are the culprits.

Ravi, you said that runtime increases exponentially ? Can you elobrate a little plz ?

Regards,

Vijay.

Message was edited by: vijay kalava

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:25:52 GMT

It's not generally a good idea to add an index to an SAP table without analyzing the code. It's definitely not a good idea to add an index to speed up a single select (or nested selects). Adding indexes also adds overhead in execution time of update programs and table space usage.

Nested selects if done correctly are not going to cause the performance issue you describe. It's more likely that you are selecting on one or more table without using an index.

If you post your code, you'll probably get a lot if ideas of how to improve its performance.

Rob

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:26:53 GMT

So that is what we need to concentrate on improving.

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:30:52 GMT

Hi all,

Here is my code :

*********************************************************

REPORT z_vijay NO STANDARD PAGE HEADING

LINE-SIZE 140

LINE-COUNT 65

MESSAGE-ID zehs.

----

T A B L E S *

----

TABLES : mara , " General Material Data

knmt , " Customer-Material Info Record Data Table

makt , " Material Descriptions

estrh . " EHS: Substance header

----

I N T E R N A L T A B L E S

----

DATA : BEGIN OF t_outtab OCCURS 0 ,

matnr(18) TYPE c , " Material number

bismt(18) TYPE c , " Old material number

subid(12) TYPE c , " Specification

kunnr(10) TYPE c , " Customer number

kdmat(35) TYPE c , " Material number used by customer

spras(1) TYPE c , " Language key

postx(70) TYPE c , " Customer description of material

delim(1) TYPE c . " Delimiter character

DATA : END OF t_outtab .

----

C O N S T A N T S *

----

CONSTANTS: c_tdid(4) TYPE c VALUE 'GRUN',

c_tdobject LIKE thead-tdobject VALUE 'MATERIAL'.

----

D A T A *

----

DATA : w_langu LIKE kna1-spras ,

w_recnroot TYPE estrh-recnroot,

w_subcat type estrh-subcat,

w_value type i.

----

P A R A M E T E R S / S E L E C T - O P T I O N S *

----

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001 .

SELECT-OPTIONS : s_lvorm FOR mara-lvorm ,

s_kzumw FOR mara-kzumw DEFAULT 'X' ,

s_mtart FOR mara-mtart ,

s_subcat FOR estrh-subcat.

SELECTION-SCREEN END OF BLOCK b1 .

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002 .

PARAMETERS : p_fname(132) TYPE c OBLIGATORY LOWER CASE .

SELECTION-SCREEN END OF BLOCK b2 .

----

INITIALIZATION *

----

INITIALIZATION .

PERFORM initialize_selection_values .

----

AT SELECTION SCREEN *

----

AT SELECTION-SCREEN .

PERFORM check_file_access .

PERFORM check_material_type .

----

TOP OF PAGE *

----

TOP-OF-PAGE.

PERFORM print_header .

----

START OF SELECTION *

----

START-OF-SELECTION .

PERFORM get_values_to_itab .

----

END OF SELECTION *

----

END-OF-SELECTION .

PERFORM move_itab_to_file .

&----

*& Form initialize_selection_values

&----

Initialize the selection scree variable values

----

FORM initialize_selection_values.

CONCATENATE '/' syst-sysid '/interfaces/msds/out/msds_out.txt'

INTO p_fname .

s_lvorm-low = 'X' .

s_lvorm-option = 'NE' .

s_lvorm-sign = 'I' .

APPEND s_lvorm .

s_mtart-low = 'FERT' .

s_mtart-option = 'EQ' .

s_mtart-sign = 'I' .

APPEND s_mtart .

s_mtart-low = 'HALB' . APPEND s_mtart .

s_mtart-low = 'HAWA' . APPEND s_mtart .

s_mtart-low = 'ROH' . APPEND s_mtart .

s_mtart-low = 'ZOFG' . APPEND s_mtart .

s_mtart-low = 'ZWST' . APPEND s_mtart .

s_mtart-low = 'ZUNB' . APPEND s_mtart .

s_subcat-low = 'REAL_SUB'.

s_subcat-sign = 'I'.

s_subcat-option = 'EQ'.

APPEND s_subcat.

s_subcat-low = 'ZPURCH_SUB'.

s_subcat-sign = 'I'.

s_subcat-option = 'EQ'.

APPEND s_subcat.

ENDFORM. " initialize_selection_values

&----

*& Form CHECK_FILE_ACCESS

&----

Check whether the given directory has enough permission for access

----

FORM check_file_access.

OPEN DATASET p_fname FOR OUTPUT IN TEXT MODE .

IF NOT syst-subrc IS INITIAL .

MESSAGE e398(00) WITH text-m01 .

ENDIF .

ENDFORM. " CHECK_FILE_ACCESS

&----

*& Form CHECK_MATERIAL_TYPE

&----

Check material type

----

FORM check_material_type.

SELECT mtart INTO s_mtart-low UP TO 1 ROWS FROM t134

WHERE mtart IN s_mtart .

ENDSELECT .

IF syst-subrc NE 0 .

MESSAGE e398(00) WITH text-m02 .

ENDIF .

ENDFORM. " CHECK_MATERIAL_TYPE

&----

*& Form GET_VALUES_TO_ITAB

&----

text

----

FORM get_values_to_itab.

set run time analyzer on.

SELECT matnr bismt

INTO t_outtab FROM mara

WHERE lvorm IN s_lvorm

AND kzumw IN s_kzumw

AND mtart IN s_mtart .

CLEAR : w_recnroot.

SELECT recnroot

INTO w_recnroot

FROM estmj

WHERE matnr = t_outtab-matnr

AND delflg = space.

*---- Retrieve the Specification number(SUBID) for only

Specification type (SUBCAT) 'REAL_SUB' or 'ZPURCH_SUB'.

SELECT single subid

INTO t_outtab-subid

FROM estrh

WHERE recnroot = w_recnroot

AND subcat in s_subcat

AND delflg = space.

ENDSELECT .

CLEAR : w_value.

SELECT kunnr kdmat postx INTO

(t_outtab-kunnr , t_outtab-kdmat , t_outtab-postx) FROM knmt

WHERE matnr EQ t_outtab-matnr

AND postx NE space .

t_outtab-delim = '/' .

t_outtab-spras = 'E' .

APPEND t_outtab .

CLEAR : t_outtab-kunnr ,t_outtab-kdmat , t_outtab-postx .

ENDSELECT .

PERFORM get_long_text .

IF t_outtab-postx EQ space.

SELECT * FROM makt WHERE matnr EQ t_outtab-matnr .

t_outtab-postx = makt-maktx .

t_outtab-spras = makt-spras .

t_outtab-delim = '/' .

APPEND t_outtab .

ENDSELECT .

ENDIF .

CLEAR t_outtab .

ENDSELECT .

IF syst-subrc NE 0 .

WRITE : text-l01 .

ENDIF .

set run time analyzer off.

ENDFORM. " GET_VALUES_TO_ITAB

&----

*& Form GET_LONG_TEXT

&----

text

----

FORM get_long_text.

DATA: w_tdname LIKE thead-tdname .

DATA: BEGIN OF t_text OCCURS 0.

INCLUDE STRUCTURE tline.

DATA: END OF t_text.

w_tdname = t_outtab-matnr.

SELECT tdspras INTO w_langu FROM stxh

WHERE tdobject = 'MATERIAL'

AND tdname = t_outtab-matnr

AND tdid = 'GRUN' .

CLEAR: t_text.

REFRESH: t_text.

CALL FUNCTION 'READ_TEXT'

EXPORTING

id = c_tdid

language = w_langu

name = w_tdname

object = c_tdobject

TABLES

lines = t_text

EXCEPTIONS

id = 1

language = 2

name = 3

not_found = 4

object = 5

reference_check = 6

wrong_access_to_archive = 7

OTHERS = 8.

IF sy-subrc EQ 0.

LOOP AT t_text WHERE tdline NE space.

CLEAR t_outtab-postx .

MOVE: t_text-tdline+0(70) TO t_outtab-postx.

EXIT .

ENDLOOP.

ENDIF.

t_outtab-spras = w_langu .

t_outtab-delim = '/' .

APPEND t_outtab .

ENDSELECT .

ENDFORM. " GET_LONG_TEXT

&----

*& Form MOVE_ITAB_TO_FILE

&----

Move Internal table contents to the Unix file

----

FORM move_itab_to_file.

DATA : w_count LIKE syst-tabix .

LOOP AT t_outtab .

TRANSFER t_outtab TO p_fname.

w_count = w_count + 1 .

ENDLOOP .

CLOSE DATASET p_fname.

WRITE : / w_count, text-l02 .

ENDFORM. " MOVE_ITAB_TO_FILE

&----

*& Form PRINT_HEADER

&----

Print header

----

FORM print_header.

CALL FUNCTION 'Z_ZS01_REPORT_HEADER'

EXPORTING

ip_pagewidth = sy-linsz.

IF sy-subrc <> 0.

MESSAGE i006. "The standard header could not be printed.

ENDIF.

ENDFORM. " PRINT_HEADER

*********************************************************

Regards,

Vijay.

Re: Performance Issues

ferry_lianto — Fri, 21 Apr 2006 19:38:57 GMT

Hi Vijay,

You need to supply key fields when select records from table MARA.

Hope this will help.

Regards,

Ferry Lianto

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 19:46:28 GMT

HI Ferry Lianto,

I am doing that right ? IN the first select I am selecting all materials form mara table where as in further queries I am using matnr .

<b>I think using internal tables may likely minimises the problem.</b>

Regards,

Vijay.

Message was edited by: vijay kalava

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 20:05:18 GMT

Hi,

One of our Ferry Lianto suggested that if the data increases the ruintime would increase in exponential time. Can anyone please explain what would be the reason behind that.

Regards,

Vijay.

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 20:18:38 GMT

So long as s_mtart is not empty, you should use a secondary index when selecting from MARA; however, I think some of the nested selects do not use indexes. But to start with, you should ensure that s_mtart is not empty.

Rob

Re: Performance Issues

ferry_lianto — Fri, 21 Apr 2006 20:19:51 GMT

Hi Viay,

There is standard secondary index <b>T - Material Type</b>.

Please try to change your code like this.


SELECT matnr bismt
INTO t_outtab FROM mara
WHERE <b>mtart IN s_mtart</b> 
  AND lvorm IN s_lvorm
  AND kzumw IN s_kzumw.

This should improve your performance running.

Regards,

Ferry Lianto

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 20:24:57 GMT

Hi,

Mtart is never empty and as Ferry Lianto suggests the select statement is already in the way he mentioned.

Regards,

Vijay.

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 20:30:27 GMT

Neither:


      SELECT SINGLE subid
      INTO t_outtab-subid
      FROM estrh
      WHERE recnroot = w_recnroot
      AND subcat IN s_subcat
      AND delflg = space.

nor:


    SELECT kunnr kdmat postx INTO
    (t_outtab-kunnr , t_outtab-kdmat , t_outtab-postx) FROM knmt
    WHERE matnr EQ t_outtab-matnr
    AND postx NE space .

appear to be using indexes. So long as s_mtart is not empty. That's where I'd look next.

You also should run a performance trace (ST05) on this program. When you look at the results, you can look at the EXPLAIN on all of the selects and see which are the ones causing the most problem.

Rob

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 20:35:29 GMT

Hi,

Thanks Rob,Srinivas Adavi and Ferry Lianto for sparing your time and efforts to help me. I really appreciate your helping nature. I am awarding points to you all but I will close the post only when I complete analysing the trace.

You all have a great week end. Catch you all on monday.

Regards,

Vijya.

Re: Performance Issues

Former Member — Fri, 21 Apr 2006 20:49:19 GMT

Try these changes.


FORM get_values_to_itab.

  DATA: BEGIN OF itab_of_matnrs OCCURS 0,
          matnr LIKE mara-matnr,
          bismt LIKE mara-bismt.
  DATA: END OF itab_matnrs.

  DATA: BEGIN OF itab_of_kdmats OCCURS 0,
          matnr LIKE knmt-matnr,
          kunnr LIKE knmt-kunnr,
          kdmat LIKE knmt-kdmat,
          postx LIKE knmt-postx.
  DATA: END OF itab_of_kdmats.

  SELECT matnr bismt INTO TABLE itab_of_matnrs
                     FROM mara
                    WHERE mtart IN s_mtart.

  DELETE itab_of_matnrs WHERE NOT ( lvorm IN s_lvorm AND
                                    kzumw IN s_kzumw ).

  SELECT matnr kunnr
         kdmat postx INTO TABLE itab_of_kdmats
                     FROM knmt FOR ALL ENTRIES IN itab_of_matnrs
                    WHERE matnr EQ itab_of_matnrs-matnr.

  DELETE itab_of_kdmats WHERE postx EQ space.

  LOOP AT itab_of_matnrs.

    t_outtab-matnr = itab_of_matnrs-matnr.
    t_outtab-bismt = itab_of_matnrs-bismt.

    CLEAR : w_recnroot.
    SELECT recnroot INTO w_recnroot
                    FROM estmj UP TO 1 ROWS
                   WHERE matnr = t_outtab-matnr
                     AND delflg = space.
*---- Retrieve the Specification number(SUBID) for only
* Specification type (SUBCAT) 'REAL_SUB' or 'ZPURCH_SUB'.
      SELECT SINGLE subid INTO t_outtab-subid
                          FROM estrh
                         WHERE recnroot = w_recnroot
                           AND subcat IN s_subcat
                           AND delflg = space.
      EXIT.
    ENDSELECT .

    CLEAR: w_value.
    LOOP AT itab_of_kdmats WHERE matnr = itab_of_matnrs-matnr.
      t_outtab-kunnr = itab_of_kdmats-kunnr.
      t_outtab-kdmat = itab_of_kdmats-kdmat.
      t_outtab-postx = itab_of_kdmats-postx.
      t_outtab-delim = '/' .
      t_outtab-spras = 'E' .
      APPEND t_outtab .
      CLEAR: t_outtab-kunnr ,t_outtab-kdmat , t_outtab-postx .
    ENDLOOP.

    PERFORM get_long_text .

    IF t_outtab-postx EQ space.
      SELECT * FROM makt
              WHERE matnr EQ t_outtab-matnr.
        t_outtab-postx = makt-maktx.
        t_outtab-spras = makt-spras.
        t_outtab-delim = '/'.
        APPEND t_outtab.
      ENDSELECT.
    ENDIF.
    CLEAR t_outtab.
  ENDLOOP.

  IF syst-subrc NE 0.
    WRITE : text-l01.
  ENDIF .

ENDFORM. " GET_VALUES_TO_ITAB