performance tuning

[Former Member]

Hi experts

I have done the following code, its performance as follows

ABAP 195,256 = 12.4%

Database 1,317,229 = 83.4%

System 67,092 = 4.2%

how to improve the database performance, pls give me any corrections needed in codeing.

Thanks in advance

Regards

Rajaram

SELECT mseg~mblnr

mkpf~budat

mseg~kdauf

mseg~kdpos

mseg~matnr

mseg~werks

mseg~erfmg

mseg~erfme

mseg~BWART

from mseg

inner join

mkpf on mkpf_{mblnr EQ mseg}mblnr

into corresponding fields of table itab

where mkpf~budat in zdate and

mseg~werks in zpl and

( mseg~BWART = '601' or

mseg~bwart = '602').

loop at itab.

if itab-kdauf is initial.

delete itab.

endif.

endloop.

sort itab.

select a~mblnr

a~kdauf

a~kdpos

a~matnr

a~werks

a~erfmg

a~erfme

a~BWART

b~budat

from mseg as a

inner join mkpf as b on

a_{mblnr = b}mblnr

into corresponding fields of table itab1

for all entries in itab

where a~kdauf = itab-kdauf

and a~kdpos = itab-kdpos and

b~budat le itab-budat.

loop at itab1.

if itab1-bwart <> '601'.

if itab1-bwart <> '602'.

delete itab1.

endif.

endif.

endloop.

loop at itab1.

select single maktx

from makt into (itab1-maktx)

where matnr = itab1-matnr.

modify itab1.

endloop.

loop at itab1.

move-corresponding itab1 to itab2.

collect itab2.

endloop.

sort itab2 by kdauf kdpos bwart.

loop at itab2.

read table itab2 into e_itab2 with key kdauf = itab2-kdauf kdpos = itab2-kdpos bwart = '602'.

if sy-subrc eq 0.

delete itab2 index sy-tabix.

endif.

itab2-erfmg = itab2-erfmg - e_itab2-erfmg.

modify itab2.

endloop.

loop at itab2.

select single WMENG

from vbep into (itab2-WMENG)

where vbeln = itab2-kdauf and

posnr = itab2-kdpos.

select single ABGRU

from vbap into (itab2-ABGRU)

where vbeln = itab2-kdauf and

posnr = itab2-kdpos.

modify itab2.

endloop.

loop at itab2.

if itab2-erfmg ge itab2-wmeng.

itab2-status = 'COMPLETED'.

else.

itab2-status = 'OPEN'.

endif.

modify itab2.

endloop.

loop at itab2.

if itab2-ABGRU is not initial.

itab2-status ='COMPLETED'.

endif.

MODIFY ITAB2.

endloop.

loop at itab2.

move-corresponding itab2 to it_final.

append it_final.

endloop.

sort itab by budat descending.

loop at it_final.

read table itab with key kdauf = it_final-kdauf kdpos = it_final-kdpos.

if sy-subrc = 0.

it_final-budat = itab-budat.

modify it_final.

endif.

endloop.

loop at it_final.

SELECT SINGLE ktx

ltx

INTO (it_final-ktx , it_final-ltx)

FROM t247

WHERE spras = sy-langu AND

mnr EQ it_final-budat+4(2).

translate it_final-ktx to lower case.

translate it_final-ktx(1) to upper case.

modify it_final.

endloop.

[Former Member]

Hi,

Replace the into corresponding with into table.

reward points if helpful,

regards,

jinesh.

[Former Member]

Select queries should not be inside LOOP. Thats the main problem.

Use FOR ALL ENTRIES & remove them outside loop.

[Former Member]

Hi,

Please avoid writing select statement inside the loop., this will increase your database performance..

Use for all entries get the ddata into internal table and thendo what ever you need.

rewards if useful,

regards,

nazeer

[Former Member]

hi all

Can you pls tell me, how to collect the data into a single internal table after using for all entries(many). give me example program.

should i use as many for all entries as many select single stmt used in loop.

Thanks

Regards

Rajaram

[Former Member]

Hi Raja

try using 'COLLECT' statement and passing that field to final internal table field.

COLLECT <itab-field> into <final_itab-field>.

Reward if helpfull

Regards

Pavan

[Former Member]

Hi,

select fields

from database table

into table itab

for all entries in itab01

where database-field1 = itab01-field01

and database-field2 = itab01-field02.........

Just use like this.

And try to avoid in to corresponding fields addition. Because it will badly impact on the performance. To avoid this create internal table fields in the same order of the database table fields.

reward points if helpfull.

with regards,

Srinivasu Reddy.

[kiran_k8]

Raja,

See to that you use only primary keys or index field in the where clause of the select statement and then filter the internal table w.r.t the requirement.for ex

select matnr spras maktx maktg from makt into table itab1

where matnr = '12345'.(here matrn is primary key).

But you want it to validate the above select statement with MAKTX too,which is not a primary key field.In such a case filter the internal table w.r.t to maktx after the above select statement.

loop at itab1.

if itab1-maktx NE 'TEST'.

delete itab.

endif.

endloop.

This way you can do the same even the selection screen fields.Definitely this will enhance the performance.

K.Kiran.

[Former Member]

Hi Raja

As i told u many times previously and i'm repeating the same. Please specify a particular field to which it have to be transported like u have used a statement <b>Modify Itab</b> which was not recommended which will consume more performance u perform as <b>modify itab transporting flag where flag is initial.</b>

and avoid nested loops also

Check these links for more information

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_Introduction.asp

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

http://www.erpgenie.com/abap/performance.htm#Modifying%20a%20set%20of%20lines

http://ezinearticles.com/?SAP-ABAP-Performance-Tuning-Tips-and-Tricks&id=294417

SELECT mseg~mblnr
mkpf~budat
mseg~kdauf
mseg~kdpos
mseg~matnr
mseg~werks
mseg~erfmg
mseg~erfme
mseg~BWART
from mseg
inner join
mkpf on mkpf~mblnr EQ mseg~mblnr
into corresponding fields of table itab
where mkpf~budat in zdate and
mseg~werks in zpl and
( mseg~BWART = '601' or
mseg~bwart = '602').


loop at itab.

if itab-kdauf is initial.
delete itab.
endif.

endloop.

sort itab.

select a~mblnr
a~kdauf
a~kdpos
a~matnr
a~werks
a~erfmg
a~erfme
a~BWART
b~budat
from mseg as a
inner join mkpf as b on
a~mblnr = b~mblnr
into corresponding fields of table itab1
for all entries in itab
where a~kdauf = itab-kdauf
and a~kdpos = itab-kdpos and
b~budat le itab-budat.

loop at itab1.
if itab1-bwart <> '601'.
if itab1-bwart <> '602'.
delete itab1.
endif.
endif.
endloop.


loop at itab1.
select single maktx
from makt into (itab1-maktx)
where matnr = itab1-matnr.
modify itab1.
endloop.


loop at itab1.
move-corresponding itab1 to itab2.
collect itab2.
endloop.



sort itab2 by kdauf kdpos bwart.

loop at itab2.
read table itab2 into e_itab2 with key kdauf = itab2-kdauf kdpos = itab2-kdpos bwart = '602'.
if sy-subrc eq 0.
delete itab2 index sy-tabix.
endif.

itab2-erfmg = itab2-erfmg - e_itab2-erfmg.
modify itab2.
endloop.


loop at itab2.
select single WMENG
from vbep into (itab2-WMENG)
where vbeln = itab2-kdauf and
posnr = itab2-kdpos.

select single ABGRU
from vbap into (itab2-ABGRU)
where vbeln = itab2-kdauf and
posnr = itab2-kdpos.
modify itab2.

endloop.


loop at itab2.
if itab2-erfmg ge itab2-wmeng.
itab2-status = 'COMPLETED'.
else.
itab2-status = 'OPEN'.
endif.
modify itab2.
endloop.

loop at itab2.

if itab2-ABGRU is not initial.
itab2-status ='COMPLETED'.
endif.
MODIFY ITAB2.
endloop.



loop at itab2.
move-corresponding itab2 to it_final.
append it_final.
endloop.


sort itab by budat descending.

loop at it_final.
read table itab with key kdauf = it_final-kdauf kdpos = it_final-kdpos.
if sy-subrc = 0.
it_final-budat = itab-budat.
modify it_final.
endif.
endloop.


loop at it_final.
SELECT SINGLE ktx
ltx
INTO (it_final-ktx , it_final-ltx)
FROM t247
WHERE spras = sy-langu AND
mnr EQ it_final-budat+4(2).


translate it_final-ktx to lower case.
translate it_final-ktx(1) to upper case.

modify it_final.
endloop. 

Reward all helpfull answers

Regards

Pavan

[messier31]

1. when r deleting rec with initial kdauf then why is select such rec..

<i>loop at itab.

if itab-kdauf is initial.

delete itab.

endif.</i>

add kdauf is initial condition in first select query..

2. Before using for all entires in itab chekc that itab is not initial.

3. similarly in second select query

instead of

<i>

loop at itab1.

if itab1-bwart <> '601'.

if itab1-bwart <> '602'.

delete itab1.

endif.

endif.

endloop.</i> just add these conditions in query..

4. instead of reading makt inside loop ..it is better to add one more inner join in second query itself to get that single field..

5. combine these two loop into one. there is no need of 2 loops.

<i>loop at itab2.

if itab2-erfmg ge itab2-wmeng.

itab2-status = 'COMPLETED'.

else.

itab2-status = 'OPEN'.

endif.

modify itab2.

endloop.

loop at itab2.

if itab2-ABGRU is not initial.

itab2-status ='COMPLETED'.

endif.

MODIFY ITAB2.

endloop.</i>

Enjoy SAP.

Pankaj Singh.

[Former Member]

Hi Raja,

About performance issue in abap these are the guide lines

<b>SAP ABAP Performance Tuning

Tips & Tricks

Performance Analysis Tools</b>

Following are the different tools provided by SAP for performance analysis of an ABAP object

Run time analysis transaction SE30

This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing.

SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

<b>these are suggested links to go thru</b>

http://www.erpgenie.com/abap/performance.htm

http://www.sapdevelopment.co.uk/perform/performhome.htm

Regards,

Kumar.