Performance problem?

[Former Member]

I have a program. about 400 lines. it takes 30 mins to get the result. The most serious proplem is when it is runing, others can not do anything and the system turns to be very slow............

Help!!

Can anyone give some advice???

[Former Member]

Hi,

Please let us know the process of the program and the Select queries that you are using.

If you believe your queries are correct then it could just be a one-off case in which case you need to contact your Basis people to check load on the system.

Regards

Nishant

[aris_hidalgo]

Hi Amanda,

If your system starts to slow whenever you run your program then it is your programs fault. You might want to check your select statements in your program. This is one of the major culprits on why programs are very slow.

Hope it helps...

P.S. Please award points if it helps...

[Former Member]

check your all data fetching <b>select</b> statements and do the <b>Extended program check</b>

SELECT

• In a SELECT statement, only the fields that are needed are selected in the order that they reside on the database, thus network load is considerably less. The number of fields can be restricted in two ways using a field list in the SELECT clause of the statement or by using a view defined in ABAP Dictionary. The usage of view has the advantage of better reusability. Besides this the use of INTO CORRESPONDING must not be used as it is degrades the performance

SELECT SINGLE

• SELECT SINGLE is used instead of SELECT-ENDSELECT loop if full primary key is known. Otherwise, use Up to 1 Rows. SELECT SINGLE requires one communication with the database system.

SELECT UP TO .. ROWS

• SELECT UP TO 1 ROWS is faster than SELECT SINGLE as the SELECT Single takes more time in the absence of all the primary keys specified in the queries. Where ever applicable try to limit the database result set to a fixed number of selections by specifying the number of records in the UP TO <n> ROWS part.

WHERE-clause

• Always specify the conditions in the WHERE-clause instead of checking them with check-statements, the database system can then use an index (if possible) and the network load is considerably less. You should not check the conditions with the CHECK statement because the contents of the whole table must be read from the database files into DBMS cache and transferred over the network. If the conditions are specified in the where clause DBMS reads exactly the needed data.

SELECT using an aggregate function

• Use a select list with aggregate functions instead of checking and computing, when try to find the maximum, minimum, sum and average value or the count of a database column, thus network load is considerably less. SELECT + INTO TABLE

• Always select into an internal table, except when the table will be very large (i.e., when the internal tables will be greater than 500,000 records). Use “Up to N Rows” when the number of records needed is known

ARRAY INSERT

• Whenever possible, use array operations instead of single-row operations to modify your database tables. The frequent communication between the application program and database system produces considerable overhead ORDER BY & SORT

• ORDER BY statements is used in SELECT only if it can use an index else sorting is effective by reading into an internal table and use the SORT statement in the program.

Rewards if useful...............

Minal

[Former Member]

can you paste your code here ?

[Former Member]

SELECT VBAK~AUDAT VBAK~KUNNR VBAK~VBELN

VBAP~POSNR VBAP~MATNR VBAP~WERKS

VBAP~KWMENG VBAP~UEPOS

INTO CORRESPONDING FIELDS OF TABLE LT_LIST

FROM VBAK

INNER JOIN VBAP ON

VBAK~VBELN = VBAP~VBELN

WHERE VBAK~VBELN IN S_VBELN

AND VBAK~AUART IN S_AUART

AND VBAK~VKORG = P_VKORG

AND VBAK~VTWEG IN S_VTWEG

AND VBAK~SPART IN S_SPART

AND VBAK~AUDAT IN S_AUDAT

AND VBAP~WERKS IN S_WERKS

AND VBAK~KUNNR IN S_KUNNR

AND VBAP~MATNR IN S_MATNR

AND VBAP~ABGRU = ''.

IF SY-SUBRC = 0.

SELECT * INTO TABLE LT_VBKD

FROM VBKD

FOR ALL ENTRIES IN LT_LIST

WHERE VBELN = LT_LIST-VBELN

AND POSNR = '000000'

AND BSTKD IN S_BSTKD.

SORT LT_VBKD BY VBELN POSNR.

• CHECK BSTKD

LOOP AT LT_LIST.

READ TABLE LT_VBKD WITH KEY

VBELN = LT_LIST-VBELN BINARY SEARCH.

IF SY-SUBRC <> 0.

DELETE LT_LIST.

ELSE.

MOVE LT_VBKD-BSTKD TO LT_LIST-BSTKD.

MODIFY LT_LIST.

CLEAR LT_VBKD.

ENDIF.

ENDLOOP.

ENDIF.

***GET OTHER DATA

IF NOT LT_LIST[] IS INITIAL.

SELECT * INTO TABLE LT_VBPA

FROM VBPA

FOR ALL ENTRIES IN LT_LIST

WHERE VBELN = LT_LIST-VBELN

AND POSNR = '000000'

AND PARVW = 'WE'.

SORT LT_VBPA BY VBELN.

SELECT * INTO TABLE LT_VBFA

FROM VBFA

FOR ALL ENTRIES IN LT_LIST

WHERE VBELV = LT_LIST-VBELN

AND POSNV = LT_LIST-POSNR

AND ( VBTYP_N = 'T' "Returns delivery for order

OR VBTYP_N = 'J' ). "Delivery

IF SY-SUBRC = 0.

SORT LT_VBFA BY VBELN POSNN.

SELECT * INTO TABLE LT_LIPS

FROM LIPS

FOR ALL ENTRIES IN LT_VBFA

WHERE VBELN = LT_VBFA-VBELN

AND POSNR = LT_VBFA-POSNN.

SORT LT_LIPS BY VBELN POSNR.

ENDIF.

• GET MATERIAL MASTER

SELECT * INTO TABLE LT_MARA

FROM MARA

FOR ALL ENTRIES IN LT_LIST

WHERE MATNR = LT_LIST-MATNR.

SORT LT_MARA BY MATNR.

• GET MATERIAL DESC.

SELECT * INTO TABLE LT_MAKT

FROM MAKT

FOR ALL ENTRIES IN LT_MARA

WHERE MATNR = LT_MARA-MATNR

AND SPRAS = '1'.

SORT LT_MAKT BY MATNR.

• GET CUSTOMER DESC.

• SELECT * INTO TABLE LT_KNA1

• FROM KNA1

• FOR ALL ENTRIES IN LT_LIST

• WHERE KUNNR = LT_LIST-KUNNR.

• SORT LT_KNA1 BY KUNNR.

ENDIF.

**PROCESS QUANTITY WEIGHT VOLUME

LOOP AT LT_LIST.

• MATERIAL DESC

READ TABLE LT_MAKT WITH KEY

MATNR = LT_LIST-MATNR BINARY SEARCH.

IF SY-SUBRC = 0.

MOVE LT_MAKT-MAKTX TO LT_LIST-MAKTX.

ENDIF.

• CUSTOMER NAME

• READ TABLE LT_KNA1 WITH KEY

• KUNNR = LT_LIST-KUNNR BINARY SEARCH.

• IF SY-SUBRC = 0.

• MOVE: LT_KNA1-NAME1 TO LT_LIST-NAME1,

• LT_KNA1-SORTL TO LT_LIST-SORTL.

• ENDIF.

• GET SHIP-TO PART

READ TABLE LT_VBPA WITH KEY

VBELN = LT_LIST-VBELN BINARY SEARCH.

IF SY-SUBRC = 0.

MOVE LT_VBPA-KUNNR TO LT_LIST-SKUNNR.

• GET SHIP-TO PART NAME

CLEAR: L_REGIO,L_LAND1.

SELECT SINGLE NAME1 SORTL REGIO LAND1

INTO (LT_LIST-NAME1,LT_LIST-SORTL,

L_REGIO,L_LAND1)

FROM KNA1

WHERE KUNNR = LT_LIST-SKUNNR.

SELECT SINGLE BEZEI INTO LT_LIST-BEZEI

FROM T005U

WHERE BLAND = L_REGIO

AND SPRAS = '1'

AND LAND1 = L_LAND1.

ENDIF.

• BOM ITEM NO QUANTITY

IF NOT LT_LIST-UEPOS IS INITIAL.

CLEAR LT_LIST-KWMENG.

MODIFY LT_LIST.

CONTINUE.

ENDIF.

• GET DN QUANTITY

LOOP AT LT_VBFA WHERE

VBELV = LT_LIST-VBELN AND

POSNV = LT_LIST-POSNR.

READ TABLE LT_LIPS WITH KEY

VBELN = LT_VBFA-VBELN

POSNR = LT_VBFA-POSNN BINARY SEARCH.

IF SY-SUBRC = 0.

LT_LIST-LFIMG = LT_LIST-LFIMG + LT_LIPS-LFIMG.

ENDIF.

ENDLOOP.

• GET OPEN SO QUANTITY WEIGHT VOLUME

LT_LIST-OKWMENG = LT_LIST-KWMENG - LT_LIST-LFIMG.

READ TABLE LT_MARA WITH KEY

MATNR = LT_LIST-MATNR BINARY SEARCH.

IF SY-SUBRC = 0.

LT_LIST-OBRGEW = LT_LIST-OKWMENG * LT_MARA-BRGEW.

LT_LIST-OVOLUM = LT_LIST-OKWMENG * LT_MARA-VOLUM.

ENDIF.

MODIFY LT_LIST.

ENDLOOP.

*****GET GT_LIST

LOOP AT LT_LIST.

MOVE LT_LIST TO GT_LIST.

IF P_CHECK = 'X' AND LT_LIST-OKWMENG = 0.

CONTINUE.

ENDIF.

APPEND GT_LIST.

CLEAR GT_LIST.

ENDLOOP.

[Former Member]

Hi Amanda,

Some points:

Don't use Select corresponding.

If possible either select all the fields or change the structure . This is b'coz Select into corresponding is very bad performaning code piece.

Second If you know that the data records being read are not more than 200 records then don't sort and use binary search. This is because for internal tables of smaller size Sorted table is actually not good.

SELECT * INTO TABLE LT_VBFA

FROM VBFA

FOR ALL ENTRIES IN LT_LIST

WHERE VBELV = LT_LIST-VBELN

AND POSNV = LT_LIST-POSNR

AND ( VBTYP_N = 'T' "Returns delivery for order

OR VBTYP_N = 'J' ). "Delivery

Don't use 'OR' in the select. You can use IN (T,J).

Remove

SELECT SINGLE NAME1 SORTL REGIO LAND1

INTO (LT_LIST-NAME1,LT_LIST-SORTL,

L_REGIO,L_LAND1)

FROM KNA1

WHERE KUNNR = LT_LIST-SKUNNR.

SELECT SINGLE BEZEI INTO LT_LIST-BEZEI

FROM T005U

WHERE BLAND = L_REGIO

AND SPRAS = '1'

AND LAND1 = L_LAND1.

these from inside the loop and put it outside since this is going to fetch data in each loop which will increase data base hits.

Hope these things will help you

Regards

Nishant