topic Re: Performance of ABAP query program in Application Development and Automation Discussions

Performance of ABAP query program

Former Member — Fri, 03 Dec 2010 03:38:57 GMT

Hi All,

A custom program AQNWZ_TALAT=====TCS_AP_ITEMS== is taking lot of time running. And when checked the cost of the select statement is very high.

I guessed it must be some custom abap query. Correct me if I am wrong.

Also please let me know how to do the performance tuning on this code? Should I be editing the program directly or should I edit at the abap query level.

Regards,

Pooja

Moderator message:

Edited by: Thomas Zloch on Dec 3, 2010 11:01 AM

Re: Performance of ABAP query program

Former Member — Fri, 03 Dec 2010 05:03:19 GMT

Follow these tips then u r program performance reduce.

A few tips you can use for finetuning a Report

1) Use mostly primary key to access data.

2) Before READ u sort the itab

3) use mostly the indexes fields in ur where clause.

4) Restrict the the fields retrieved by your select sentences to the minimal set. (Avoid select *)

5) try to use specify where clause so the abap sql optimizer chooses the right index.

6) Avoid sentences like select lifnr name1 into corresponding fields of lfa1 from lfa1 where....

(You should declare a working area and select into the working area, is twice faster)

7) use hashed tables instead of standard tables. They are faster.

😎 avoid the use of collect as much as you can.

******************************************************

1 Always check the driver internal tables is not empty , while using FOR ALL ENTRIES

2 Avoid for all entries in JOINS

3 Try to avoid joins and use FOR ALL ENTRIES.

4 Try to restrict the joins to 1 level only ie only for 2 tables

5 Avoid using Select *.

6 Avoid having multiple Selects from the same table in the same object.

7 Try to minimize the number of variables to save memory.

8 The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

9 Avoid creation of index as far as possible

10 Avoid operators like , > , < & like % in where clause conditions

11 Avoid select/select single statements in loops.

12 Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

13 Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

14 Avoid using ORDER BY in selects

15 Avoid Nested Selects

16 Avoid Nested Loops of Internal Tables

17 Try to use FIELD SYMBOLS.

18 Try to avoid into Corresponding Fields of

19 Avoid using Select Distinct, Use DELETE ADJACENT.

regards,

muralii

Moderator message: please refrain from copy/pasting such "information" in the future, this is the fast lane to account deletion. I'm only leaving them because Siegfried has already commented each one from a realistic point of view below.

Edited by: Thomas Zloch on Dec 3, 2010 10:58 AM

Re: Performance of ABAP query program

Former Member — Fri, 03 Dec 2010 09:47:05 GMT

>A few tips you can use for finetuning a Report

The actually a good collection of the most important pain points. But the recommendations are unfortunately quite poor, and even wrong. I have updated them a little bit.

And for German speaking people I have added a reference to a textbook, where arll my recommendations are explained in detail.

Siegfried

1) Use mostly primary key to access data.

... nonsense, the task determines the WHERE clause, it is not your choice

2) Before READ u sort the itab

3) use mostly the indexes fields in ur where clause.

... as 1, it is the other way, the task determines the WHERE-clause, make surwe that there is an index which

supports it

4) Restrict the the fields retrieved by your select sentences to the minimal set. (Avoid select *)

nonsense, most SELECT use and must use *, use field list in wide tables

5) try to use specify where clause so the abap sql optimizer chooses the right index.

o.k., but who understands that in depth?

6) Avoid sentences like select lifnr name1 into corresponding fields of lfa1 from lfa1 where....

feel free to use if required

(You should declare a working area and select into the working area, is twice faster)

... such general statements are always incorrect

7) use hashed tables instead of standard tables. They are faster

no, use sorted tables, hashed are very special and can only be used if there is a unique key

😎 avoid the use of collect as much as you can.

total nonsense, use collect with hashed tables, works perfect, if you use it with standard tables, do not mix it with other statements

1 Always check the driver internal tables is not empty , while using FOR ALL ENTRIES

100% correct

2 Avoid for all entries in JOINS

... not generally true

3 Try to avoid joins and use FOR ALL ENTRIES.

... hmmm, there are pages of discussions, this statement was wrong, is wrong and will be wrong!!!

4 Try to restrict the joins to 1 level only ie only for 2 tables

nonsense, joins are complicated, you must know hw indexes are used, if everything is fine, then 10 or 20 tables can be joined

5 Avoid using Select *.

see 4)

6 Avoid having multiple Selects from the same table in the same object.

... try to avoid to SELECT the same data, different criteria can be necessary

7 Try to minimize the number of variables to save memory.

... obvious,

8 The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

... no is must not the optimizer will not be influenced be the order , but do it, it increases readability

9 Avoid creation of index as far as possible

... no, o.k. if you are not experienced than avoid ... otherwise the creation of the required indexes is absolutely mandatory for new tables, for SAP standard tables most indexes are already there. But still if very new accesses are added, then new indexes can become necessary.

10 Avoid operators like , > , < & like % in where clause conditions

... no, you must add them, but be aware that the search is only poorly supported by them, but the result set is reduced.

11 Avoid select/select single statements in loops.

... if they are going to the database! If the tables are buffered, then these statements are perfect!

12 Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

.... Actually try to avoid standard tables, use sorted tables instead. If you must use a standard table, than the recommendation is true.

13 Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

.... no, use them if required. Again, if table is buffered, then they MUST be avoided.

14 Avoid using ORDER BY in selects

... if it is not ORDER BY PRIMARY KEY. Again, if the table is buffered, then other orders MUST be avoided.

15 Avoid Nested Selects

... use join. Again, if the tables are buffered, then Nested SELECTs are fine!

16 Avoid Nested Loops of Internal Tables

.... impossible, there is no other option. Use sorted tables and verything is fine!

17 Try to use FIELD SYMBOLS.

... what means try? Use ASSIGNING fieldsymbol in LOOPs, but use it in READs only for very wide tables (>1kB). Use it always for tables with tables in the workarea!

18 Try to avoid into Corresponding Fields of

... see 6) is wrong!

19 Avoid using Select Distinct, Use DELETE ADJACENT.

.... no, use it if required and if it can reduce the result set by a factor of 2 or more. Again, if table is buffered, then it MUST be avoided.

Wenn Sie mit der Performance Ihres ABAP Programms nicht zufrieden sind, werfen Sie doch einen Blick in mein neues Buch:

Siegfried Boes: Performance-Optimierung von ABAP®-Programmen Nov. 2009 464 Seiten 59u20AC

Leseproben und weitere Informationen: http://www.dpunkt.de/buecher/3096.html

Edited by: Thomas Zloch on Dec 3, 2010 10:53 AM

Re: Performance of ABAP query program

Former Member — Fri, 03 Dec 2010 09:55:15 GMT

... layout problem ... then I had 2 anwer postings

ähh no, thanks Thomas for editing it !

Edited by: Siegfried Boes on Dec 3, 2010 10:57 AM

Re: Performance of ABAP query program

Former Member — Sat, 04 Dec 2010 23:04:41 GMT

To hold up the spirit of the proven and reliable answer <b>it depends</b> I couldn't resist to nitpick a bit on the updated recommendations. So all you non-sticklers don't complain to me later for having wasted your time if you continue reading...

<div class="jive-quote">3) use mostly the indexes fields in ur where clause.

... as 1, it is the other way, the task determines the WHERE-clause, make sure that there is an index which supports it</div>

Depending on the size of your result set and the number of filtered entries it might actually be more efficient to do a full table scan (prime examples where this might be the case are data extracts or huge reports, see for examples [Oracle's comments on full table scans|http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#i44851]). And sometimes we even need to convince the optimizer that a full table scan is in fact better than the indirect access via an index...

4) Restrict the the fields retrieved by your select sentences to the minimal set. (Avoid select *)
...nonsense, most SELECT use and must use *, use field list in wide tables

By selecting only the columns I need I give the database a chance to possibly read less data and then also transfer less data over the network to the application server (and possibly less data in memory on the app server, which was found to be beneficial in 7). I don't see why I would use select * unless I need all fields (or almost all fields if my result set is not too large) and in that case the minimal set corresponds to all fields.

11) Avoid select/select single statements in loops.
... if they are going to the database! If the tables are buffered, then these statements are perfect!

I'm not sure if perfect is the right expression, I'd replace it with usually ok. If the data is actually available in the buffer depends obviously on the table buffering strategy (i.e. full versus partial) and what data you're reading how and when (and obviously reading data from a buffered table that is not yet buffered cannot be faster then reading data from a table without buffering).

Similarly the recommendation for 13 (must avoid aggregate functions for buffered tables) and 19 (must avoid select distinct for buffered tables) also seem to be based on the assumption that either the table is fully buffered or most required rows are buffered. Even if that's true, I'd personally have performance in the back of my mind while coding, but would try to avoid any [premature optimization|http://en.wikipedia.org/wiki/Premature_optimization#When_to_optimize] and first of all stick to [KISS|http://en.wikipedia.org/wiki/KISS_principle]: If my coding is more readable and shorter, even though my select bypasses table buffering, why should I forgo it unless I measured some actual performance impact?!

14) Avoid using ORDER BY in selects
... if it is not ORDER BY PRIMARY KEY. Again, if the table is buffered, then other orders MUST be avoided.

I think I've babbled too much already on table buffering, so let me skip this here. However, I strongly disagree with generally restricting order by to the table's primary key (even for non-buffered tables). I thought the order by is usually an operation on the result set - so why not let the database sort a small result set?

And even if the result set is larger, we still might be as efficient as without the order by clause if the table has an index for those fields (the prime example is obviously where sort fields and main condition fields are in one appropriate index).

Not to mention the fact that I have in fact also used the order by clause successfully to push the Oracle optimizer in the right direction (i.e. use of appropriate index) without having to specify any hints (probably due to the approach described in [optimizing with order by|http://download.oracle.com/docs/html/A95912_01/wn32tune.htm#i631241]).

Cheers, harald