Performance Tuning -To find the execution time for Select Statement

[Former Member]

Hi,

There is a program that takes 10 hrs to execute. I need tune its performance. The program is basically reading few tables like KNA1,ANLA,ANLU,ADRC etc and updates to Custom table. I did my analysis and found few performance techniques for ABAP coding.

Now my problem is, to get this object approved I need to submit the execution statistics to client.I checked both ST05 and SE30. I heard of a Tcode where we can execute a select statement and note its time, then modify and find its improved Performance. Can anybody suggest me on this.

Thanks,

Rajani.

[Former Member]

Hi,

There is a program that takes 10 hrs to execute. I need tune its performance. The program is basically reading few tables like KNA1,ANLA,ANLU,ADRC etc and updates to Custom table. I did my analysis and found few performance techniques for ABAP coding.

Now my problem is, to get this object approved I need to submit the execution statistics to client.I checked both ST05 and SE30. I heard of a Tcode where we can execute a select statement and note its time, then modify and find its improved Performance. Can anybody suggest me on this.

Thanks,

Rajani.

[Former Member]

hi,

use "get runtime" to find the execution of the select statement.

regards,

madhu

[Former Member]

Hi,

Please check:

http://help.sap.com/saphelp_nw70/helpdata/EN/5a/ace273ca0211d194b500a0c94260a5/frameset.htm

Regards

Kiran Sure

[ThomasZloch]

Hi,

from ST05 you can go to an "Editor for changing SQL statement", there you can alter the statement, do another "explain" and compare the results. Not sure though if this helps your particular problem here.

Your report takes 10 hours to finish. Can you run it for a small set of data in SE30, then work down the list of the most time consuming database or internal table operations? If not, you can still start the report and check in SM50 which is the select statement where it spends all that time.

Post the long running statements here, so we can have a further look.

Cheers

Thomas

[Former Member]

Hi Thomas,

Here is small part of my code.There are 2 lac records in ANLU. How can I optimize it.

data: begin of i_anlu occurs 0,

kunnr type kna1-kunnr,

end of i_anlu.

select distinct zzkunnr into table i_anlu

from anlu.

if not i_anlu[] is initial.

delete i_anlu where kunnr is initial.

sort i_anlu ascending by kunnr.

endif.

Thanks,

Rajani.

[Former Member]

If your program runs 10 hours, then it should process a lot of data. And you should start optimizing with much less data!

Run the traces:

SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

SE30

/people/siegfried.boes/blog/2007/11/13/the-abap-runtime-trace-se30--quick-and-easy

If you are not able to reduce the load to a smaller amount of data, then you can use SM50, check whether then program is running and start debugging by chance. If you do it several times you will most often stop a source codes where most of the time is spent.

Note, this coding and try to optimize it or ask again.

Siegfried

[Former Member]

Hi,

Thanks for such wonderful links. I am going through the stuff in links . Meanwhile can you please briefly explain How to use the Tcode SM50.

Thanks,

Rajani.

[Former Member]

select distinct zzkunnr into table i_anlu

from anlu.

if not i_anlu[] is initial.

delete i_anlu where kunnr is initial.

sort i_anlu ascending by kunnr.

endif.

... sorry to say that but you are not very experienced, you do the expensive things first and then you add things which would improve the execution.

The delete is expensive because it has to check the whole table, it check directly if you sort first. It would not be necessary to delete anything if you

add a whewre condition.

But this single select is no performance issue.

SM50, is really selfexplaining, call it, it is just an overview on the active processes, yours will be there for quite a while. Mark it and start debugger.

Siegfried

[Former Member]

Hi Boes/Thomas,

Thanks for your good responses. This program is coded in 2004 and not by me. All the selects are written in the same way.There is less feasibility in making tuning changes such as adding ' where ' clause etc due to its functionality. I am working on it and checking all the points of standard performance tuning techniques. When this issue will get resovled I will let you know. Meanwhile I will close the thread.

I executed the tcode SM50 and indeed it is self explanatory.But I am not able to select the program in Program->debugging . waiting for your kind reply.

Thanks,

Rajani.

[Former Member]

Hi,

This is documentation regarding performance analysis. Hope this will be useful

It is a general practice to use Select * from <database>… This statement populates all the values of the structure in the database.

The effect is many fold:-

• It increases the time to retrieve data from database

• There is large amount of unused data in memory

• It increases the processing time from work area or internal tables

It is always a good practice to retrieve only the required fields. Always use the syntax Select f1 f2 … fn from <database>…

e.g. Do not use the following statement:-

Data: i_mara like mara occurs 0 with header line.

Data: i_marc like marc occurs 0 with header line.

Select * from mara

Into table i_mara

Where matnr in s_matnr.

Select * from marc

Into table i_marc

For all entries in i_mara

Where matnr eq i_mara-matnr.

Instead use the following statement:-

Data: begin of i_mara occurs 0,

Matnr like mara-matnr,

End of i_mara.

Data: begin of i_marc occurs 0,

Matnr like marc-matnr,

Werks like marc-werks,

End of i_marc.

Select matnr from mara

Into table i_mara

Where matnr in s_matnr.

[ThomasZloch]

man, I wish there was a functionality to filter out such unrelated gibberish...

[Former Member]

more information in the answers than in the question ... I still don't really understand what you really did and what does not work.

Maybe you should check the STAD (tcode) after you executed your example, this gives you repsonse time, db time and cpu time.

Siegfried