topic fine tuning the database query in Application Development and Automation Discussions

fine tuning the database query

Former Member — Fri, 17 Mar 2006 11:23:20 GMT

To analyse the load of a particular object. we do use 'performance analysis' in ABAP. But let us suppose we've come to know that more load is on the database. i.e. suppose 80% on database, 20% on Application server. what could be the possible steps that u wud perform on the query so that the load gets decreased. we don't have a chance to transform the load from database to application server (such as using internal tables and hence reducing the load). we should do something on the database side. so what can we do. any solution would be highly appreciated.

thanks in advance for the solution.

Re: fine tuning the database query

suresh_datti — Fri, 17 Mar 2006 11:27:16 GMT

Hi Kiran,

One option could be to use an INDEX on the table..

Regards,

Suresh Datti

Re: fine tuning the database query

Former Member — Fri, 17 Mar 2006 11:27:44 GMT

Hi Kiran,

Yes, You can reduce the load on the Database by better use of internal tables.

Avoid Selects in loops.

Avoid Nested Selects and instead use For all entries stataments.

Avoid Select-endselect statements instead use, Select into table type of statements.

For more info,

/message/1161039#1161039 [original link is broken]

Regards,

Ravi

Re: fine tuning the database query

Former Member — Fri, 17 Mar 2006 11:31:36 GMT

Hi,

You should use ST05 and then Switch TRACE ON for SQL.

Then do your query again.

After you have run your query, you switch the trace OFF and then you display the trace.

When displaying the trace you can see which SQL queries that have been made and how long time they have taken (left most column).

If the time is RED that indicates a bad query.

Anyway. Doubleclick on the query to find out how the query is made.

And analyse the table(s) that are used and whether or not an INDEX is used.

If no index is used you can ADD an index to the database table(s) in transaction SE10. Or directly in the database if you have that possibilty.

ST05,

Trace on,

Run query,

Trace off,

Display trace,

Analyze trace.

Good luck!

BR Mattias

Re: fine tuning the database query

Former Member — Fri, 17 Mar 2006 11:32:15 GMT

Hi Kiran,

Few points:

1) Try to put less no. of select Queries in the prog.

2) Avoid repeating select query on to the same program.

3) Try to use select single instead of select *.

4) Use read inside a loop with binary search instead of using select inside a loop.

and lot more but at this amount of time following above steps will surely readuce the database loads.

Cheers

Sunny

Rewrd points, if found helpful

Re: fine tuning the database query

Former Member — Fri, 17 Mar 2006 11:36:53 GMT

hi kiran,

The pdf below briefs how to improve and tune performance in ABAP...

http://www.sappoint.com/faq/faqsql.pdf

Also check...

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

Hope it helps...

Regs,

jaga

Re: fine tuning the database query

jayanthi_jayaraman — Fri, 17 Mar 2006 11:45:29 GMT

Hi,

Whatever values you need from a datatable,get it using a single select statement rather than using select inside loop...endloop.

Put all the necessary data in internal table at a stretch from database and then use read or loop statements to process the data from internal table.