Optimisation-SQL

[kiran_k8]

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.

[rodrigo_paisante3]

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.

[Former Member]

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

<b>reward points if helpful</b>

rgds,

bharat.

[Former Member]

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

[kiran_k8]

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.

[Former Member]

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

[former_member194669]

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

[Former Member]

hi

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

<b>plz dont forget to reward points if helpful</b>

[Former Member]

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

[kiran_k8]

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

[kiran_k8]

Hi Folks,

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

Thanks,

K.Kiran.

[Former Member]

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

[kiran_k8]

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.

[Former Member]

Hi Kiran ,

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

<b>IF T2 < TMIN.

TMIN = T2.

ENDIF.</b>

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

[Former Member]

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.

[kiran_k8]

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.

[kiran_k8]

Hi Folks,

DATA: T1 TYPE I,

T2 TYPE I,

<b>GET RUN TIME FIELD T1.

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

GET RUN TIME FIELD T2.</b>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.

[Former Member]

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

[kiran_k8]

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.

[Former Member]

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

[kiran_k8]

Rob,

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?

4.If we want how much time does a statement is taking to get executed we should give it in betwee those two "get run time" statements right?

I will be posting more on this,kindly opine.

Thanks,

K.Kiran.

[Former Member]

1) Yes

2) I use three fields: T3 = T2 - T1.

3) Almost - see answer 1.

4) For SQL, the explain function in ST05 gives a more accurate analysis.

Rob

[kiran_k8]

Hi Folks,

data: it_cperiod type table of range_prds with header line,

it_pperiod type table of range_prds with header line.

CALL FUNCTION 'BUILD_PERIOD_TABLE'

1.Does the above fn.mod has any impact on performance ?

2.I am having a select query like this,what can I do to optimise this.

select b~mblnr

b~menge

b~lfbnr

b~lfpos

from mseg as b

inner join mkpf as a

on b_{mblnr = a}mblnr

into corresponding fields of table it_rjct

where b~werks = p_werks and

a~budat between it_cperiod-begda and it_cperiod-endda and

b~bwart = '122' and

b~ebeln <> SPACE.

Kindly help me.

Thanks,

K.Kiran.

[kiran_k8]

Hi Folks,

Kindly let me know how to optimise the above select Query.

Suppose if a table is having two indexes

In the first index there are fields a,b,c

In the second index there are fields d e f.

1.Suppose in the where clause we have A which is in the first index and D which is in the second index,does that improves the performance?

2.Is it mandatory that we have to have fields only from one index in the where clause?

3.Does their order play a role?

4.Kindly let me know how to optimise the above select query.

Thanks,

K.Kiran.

[kiran_k8]

Hi Folks,

Looking forward to SDN experts advice.

Thanks,

K.Kiran.

[Former Member]

Hi Kiran

1. If u have field A from the first index and field D from the second index, the Select will retrieve data based on any one of the index. You have to build an Index using Field A and Field D. Then check in ST05 the select will be using the index which u built.

2. No, It is not mandatory to have fields from the same index in the where clause

3. Yes, the order of the fields in the where clause does play a role. Use them in the order it is present in the table.

Cheers

4. you are just using the table a in the where clause so instead u can first select the budat from that table and then do a FOR ALL ENTRIES on that internal table for the second SELECT.

Message was edited by:

Elanchezhian Balasubramanian

[kiran_k8]

Subramaniam,

Can you please modify the above select so that I can use it as a template.Does using FOR ALL Entries will not reduce the performance.

K.Kiran.

[kiran_k8]

Hi Folks,

1.Can I create a view of any of the table to improve the performance?

2.If so on what parameters should I decide that I should I go for a VIEW.

3.On what types of tables can we create a VIEW.

4.Does the performance differs on the kind of table and the kind of view we created for that table.

5.What type of view it should be so that we can select the data using SELECT.

K.Kiran.

[Former Member]

You seem to have a number of new questions here. To avoid confusion, please close this thread and open a new one(s) with your questions.

Rob

[kiran_k8]

Rob,

As every post is one way or related to my issue I thought of continuing in this thread so that I can keep of track of the same and at the same reduce the load of SDN server by not posting many threads related to more or less a similar issue.

Fine,if you suggest,then surely I will.Hope you will keep track of my queries and help me whenever needed.

K.Kiran.

[Former Member]

I think if you check the WIKI, somewhere you'll find that there should be one question per post. This makes it easier for those who search the forum.

Rob

[kiran_k8]

Rob,

OK Boss.I agree :-).

Thanks,

K.Kiran.

[kiran_k8]

Thanks a ton to all SDNers who provided valuable information.

K.Kiran.