topic Re: Optimisation-SQL in Application Development and Automation Discussions

Optimisation-SQL

kiran_k8 — Wed, 20 Jun 2007 13:25:48 GMT

Hi Folks,

1.I have a program which is having 10 select statements.Is there any way to know which select statement is eating most of the time and thus reducing the execution speed.Can ST05 or SE30 of any use in this case.

2.Does SYST has any field which lets us know the total amount of time a program is taking to display the output.

3.Suppose if we are joining two tables using inner join does the sequence of fields that we write in the select statement effects the performance.Do we need to write the fields in the same sequence as they are in the table?

4.Any hints to improve the execution speed are welcome.

Thanks,

K.Kiran.

Re: Optimisation-SQL

rodrigo_paisante3 — Wed, 20 Jun 2007 13:33:18 GMT

Hi.

You can reduce time changing select.. endselect to select into table.

use select... for all entries when possible

verify if you realy need to select all tables and all fields.

Re: Optimisation-SQL

Former Member — Wed, 20 Jun 2007 13:34:36 GMT

Hi,

1)goto se30 enter ur program in program field and press execute button execute ur program and come back.click on EVALUATE button.graph will come in thatselect hit list button it will give u detailed calculation of time statement wise.

2)no such syst field is there.

3)fields sequence will not effect the performance.

4)follow this link

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ABAPSort.asp

reward points if helpful

rgds,

bharat.

Re: Optimisation-SQL

Former Member — Wed, 20 Jun 2007 13:35:54 GMT

1.) ST05 is the tool I most often use for this type of analysis.

2.) I don't think so.

3.) Probably not, but I think it's good programming style to put them in "index" order.

4.) Run ST05 and post the code of the problem SELECT.

Rob

Re: Optimisation-SQL

former_member194669 — Wed, 20 Jun 2007 13:39:58 GMT

Hi,

1. ST05 or SE30 and coverage analyzer are tools for validating the performance.

2. No SYST field will tell you the amount of time taken in select statement

3. If you cannot use primary key always check for secondary index associated with table.

or check for any database view are available?

Check for your read table statements if you have then use with binary search. pl. remember use sort the internal table with key before binary search

aRs

Re: Optimisation-SQL

Former Member — Wed, 20 Jun 2007 13:40:33 GMT

if u do not write the fields in the same sequence u will have to use

INTO CORRESPONDING FIELDS OF which is not advisable to use

so its better to write the fields in the same sequence as they are in the table

even joins reduce the system performance

prefer to use FOR ALL ENTRIES.

regards

ravish

plz dont forget to reward points if helpful

Re: Optimisation-SQL

kiran_k8 — Wed, 20 Jun 2007 13:45:00 GMT

Rob,

4.) Run ST05 and post the code of the problem SELECT.

I had pasted that particulat select statement in st05 after checking sql trace-activate trace and enter seql statement.

Can you please let me know how to use st05 in this case.

Thanks,

K.Kiran.

Re: Optimisation-SQL

Former Member — Wed, 20 Jun 2007 13:49:07 GMT

Try it this way:

In one session, set a break point in your code to stop at he select and execute the code.

In another session, start ST05 (activate the trace) after the brteakpoint is reached.

After the program is ended deactivate the trace and list it. You should see problem points in the trace.

Rob

Re: Optimisation-SQL

Former Member — Wed, 20 Jun 2007 13:50:19 GMT

Hi Kiran ,

Yes ST05 will help you find which SQL statement is taking a lot of time.

There is also one option to find which part of the code takes a lot of time.

You need to use GET RUNE TIME command.

Here is a templeat of how to use this command

DATA: T1 TYPE I, T2 TYPE I.

GET RUNE TIME FIELD T1.

SOURCE CODE LINES

GET RUN TIME FIELD T2.

TAKE DIFF. (T2-T1)

Hope this helps,

Assign points if helpful

Regards

Arun

Re: Optimisation-SQL

kiran_k8 — Thu, 21 Jun 2007 06:55:51 GMT

Hi Folks,

Can anyone here please confirm "time taken to execute sql" in the list output is exactly the amount of time that this sql statement has taken to fetch the data.

Please execute the program to see the time taken and then comment on it.

REPORT ZOPT .

data:begin of imakt occurs 0,

matnr like makt-matnr,

spras like makt-spras,

maktx like makt-maktx,

end of imakt.

DATA: T1 TYPE I,

T2 TYPE I,

TMIN TYPE I.

TMIN = 1000000.

GET RUN TIME FIELD T1.

select matnr spras maktx from makt into table imakt where spras = sy-langu.

GET RUN TIME FIELD T2.

T2 = T2 - T1.

IF T2 < TMIN.

TMIN = T2.

ENDIF.

WRITE: 'Time taken for Execution of SQL:', TMIN, 'microseconds'.

loop at imakt.

write:/ imakt-matnr,imakt-spras,imakt-maktx.

endloop.

Thanks,

K.Kiran.

Message was edited by:

Kiran K

Re: Optimisation-SQL

kiran_k8 — Thu, 21 Jun 2007 07:55:36 GMT

Hi Folks,

Every time it is run,the time taken varies.Why?

Thanks,

K.Kiran.

Re: Optimisation-SQL

Former Member — Thu, 21 Jun 2007 08:03:35 GMT

Hi Kiran ,

The time taken will never be the same , as the time taken also depends on many other factors like network congention , and many other similar functions.

Regards

Arun

Re: Optimisation-SQL

kiran_k8 — Thu, 21 Jun 2007 08:08:13 GMT

Arun,

Thanks.

Can you please go through the prog i had pasted above and let me know whether the time taken shown in the list output is the time taken by the sql statement to fetch the data.Please confirm.

Thanks,

K.Kiran.

Re: Optimisation-SQL

Former Member — Thu, 21 Jun 2007 08:14:02 GMT

Hi Kiran ,

It is partially correct , but i am not clear why have you used the code given below

IF T2 < TMIN.

TMIN = T2.

ENDIF.

Actually T2 is the time taken for the select statement , your program will given an incorrect time if T2 is greater than 1000000.

Regards

Arun

Re: Optimisation-SQL

Former Member — Thu, 21 Jun 2007 08:24:48 GMT

Hi Kiran

Execute ur program in one session and in an another session go to transaction SM50. Here u can see on which table ur select is taking the most time.

Now check this select of urs on this particular table, check for the available index.

Go to ST05 and check whether ur select is picking up any of the available index else try creating an index based on ur selection criteria, now check again whether ur select is picking up the index u created, now check the performance.

Retrieve only the necessary fields and put it in the same sequence as it is in the table. Avoid using MOVE CORRESPONDING, avoid SELECT - ENDSELCTS, avoid SELECTS inside a loop, instead SELECT the data before the loop and read it inside the loop. Check all ur READ statements if any, use BINARY SEARCH along with the READ statements.

And abt ur question on the varying execution time, as Arun has mentioned the execution time depends on various factors like the load on the database, the network, the number of users accessing the data, etc.

Hope these help u.

Re: Optimisation-SQL

kiran_k8 — Thu, 21 Jun 2007 08:52:29 GMT

Bala,

I had executed the report.

I went to sm50.

It is showing some list which I was not able to makeout what exactly it is displaying.

So,glad if you can throw some light on this area.

Thanks a ton to everybody.

K.Kiran.

Re: Optimisation-SQL

kiran_k8 — Thu, 21 Jun 2007 09:19:55 GMT

Hi Folks,

DATA: T1 TYPE I,

T2 TYPE I,

GET RUN TIME FIELD T1.

select matnr spras maktx from makt into table imakt where spras = sy-langu.

GET RUN TIME FIELD T2.T2 = T2 - T1.

write:/ t2.

I have some doubts.

1.If we give any statement in between get run time field t1 and get run time field t2 it will return the time taken to execute that statement.right?

2.why two fields t1 and t2?

3.Here it is giving the time taken by the sql to fetch the data,right?

Thanks,

K.Kiran.

Re: Optimisation-SQL

Former Member — Thu, 21 Jun 2007 09:37:18 GMT

Hi Kiran

Double click on ur username in that list where it is taking long time, it will give more information abt the process which u are executing.

SM50 gives u a list of all the process that are running in the system. Here u can find out which process is taking lot of time and it even lists out the action the process is performing and on which table. So based on these information u can determine whether a sequential read on a particular table is taking a very long time then. It even gives u the select statement which is taking the long time when u double click. now pick this select and use in ST05 and follow the steps that I had mentioned earlier.

Check it and let me know if u need any further help here.

Dont forget to keep refreshing the list in SM50 when ur program is executing.

Message was edited by:

Elanchezhian Balasubramanian

Re: Optimisation-SQL

kiran_k8 — Thu, 21 Jun 2007 09:51:34 GMT

Bala,

As you said it is showing the user name but not the report name that I had executed.When I clikc on the user name it is showing some data having "last sql statement" which is no way related to my program.

Now coming to st05,it is showing data some in dark yellow and some in light yellow and some TIME in pink.Does they infer anything.

Under duration for prepare,fetch and open it is showing some duration,which one we should consider.

Sorry to bug you all.

Thanks,

K.Kiran.

Re: Optimisation-SQL

Former Member — Thu, 21 Jun 2007 13:24:10 GMT

Kiran - unfortunately, the same sSQL tatement can vary wildly in times taken to execute. Partly due to different system loads but more importantly due to buffering. Not SAP necessarily buffering - there are other types of buffering going on that you never see or hear about. Typically, the first time you execute a statement, it takes the longest.From the SAP help on "GET RUN TIME:

"SAP recommends that you measure the runtime several times and take the minimum result. "

Rob