Performance tuning techniques

[Former Member]

I am looking to compile a list of the major performance tuning techniques that can be implemented in an ABAP program.

Appreciate any feedback

J

[Former Member]

If you are selecting small amounts of data, make sure you use an index effectively.

If you have moderate amounts of data, make sure that you are not using nested loops. It's still good to use an index, but not relatively as important. Nested selects start to become an issue.

If you want to retrieve extremely large amounts of data, make sure that you are not using nested loops. Avoiding nested selects becomes more important. If you are retrieving an entire table, using an index is not important.

In the next week or so, I hope to have a BLOG that demonstrates this.

Rob

[Former Member]

Hi JJ,

Few More:

1. Avoid selecting data from Clustur table.

2. Use Array select stmt.. : into table i_tab

3. Avoid Nested Select

4. Check table entry before using FOR ALL ENTRIES..

5. Use INNER JOIN if more than 1 table are required for selection and are linked.

6. Use READ stmt with BINARY option.

7. Declare Internal table as required STANDARD?SORTED?HASHED..

8. MODuLARIZE Your program using SUBROUTINE/ FUNCTION MODULE .

Well there are many. If possible send your mail id.

Will forward you the document. Hope your job gets done.easily.

Reward points if that helps.

Manish

[Former Member]

Call Transaction SE30 an press the button Tips & Tricks.

Matthias

[Former Member]

Hi , this comes from the SAP ABAP Performance Tuning Course

RULES FOR EFFECTIVE SELECT STATEMENTS IN ABAP

These have been developed from those set up by the late, grea,t Axel Kurka (‘Doctor ABAP’)

1. THINK AND EXPERIMENT

Are you using the right tables?

Document tables (VBAK, BSEG etc) grow quickly and you should try to avoid using them – investigate the application area of SAP you are working in and see if the standard system is doing similar processing, what tables is it using?

2. KEEP THE RESULT SET SMALL.

Use the WHERE condition not CHECK

Never retrieve more data than you need

3. MINIMISE THE AMOUNT OF DATA TRANSFERRED BETWEEN THE DATABASE AND APPLICATION SERVER.

No SELECT *’s.

Use UPDATE .. SET if possible.

Use aggregate functions (MAX CNT SUM etc) – this is often justified even though you are breaking rule 6 (see below).

4. MINIMISE THE NUMBER OF TRANSFERS BETWEEN DATABASE AND APPLICATION SERVER.

Avoid SELECT … ENDSELECT, especially nested SELECT’s

Use INSERT … from TABLE.

Try to retrieve all your data at once

5. MINIMISE THE SEARCH OVERHEAD.

Would an index help? But, remember the extra maintenance cost and the impact on other ABAP’s.

6. REDUCE THE DATABASE LOAD.

Buffer small tables.

Avoid ORDER BY – use SORT unless the sort order is the same as the index used by the database.

Avoid reading the same data more than once

[Former Member]

Have a look at below links:

http://www.sapdevelopment.co.uk/perform/performhome.htm

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/afbad390-0201-0010-daa4-9ef...

Best Regards,

Vibha

*Please mark all the helpful answers

[anversha_s]

HI,

chk this.

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

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Performance tuning for Data Selection Statement

For all entries

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of

entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the

length of the WHERE clause.

The plus

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

The Minus

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Nested selects

The plus:

Small amount of data

Mixing processing and reading of data

Easy to code - and understand

The minus:

Large amount of data

when mixed processing isn’t needed

Performance killer no. 1

Select using JOINS

The plus

Very large amount of data

Similar to Nested selects - when the accesses are planned by the programmer

In some cases the fastest

Not so memory critical

The minus

Very difficult to program/understand

Mixing processing and reading of data not possible

Use the selection criteria

SELECT * FROM SBOOK.

CHECK: SBOOK-CARRID = 'LH' AND

SBOOK-CONNID = '0400'.

ENDSELECT.

SELECT * FROM SBOOK

WHERE CARRID = 'LH' AND

CONNID = '0400'.

ENDSELECT.

Use the aggregated functions

C4A = '000'.

SELECT * FROM T100

WHERE SPRSL = 'D' AND

ARBGB = '00'.

CHECK: T100-MSGNR > C4A.

C4A = T100-MSGNR.

ENDSELECT.

SELECT MAX( MSGNR ) FROM T100 INTO C4A

WHERE SPRSL = 'D' AND

ARBGB = '00'.

Select with view

SELECT * FROM DD01L

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

SELECT SINGLE * FROM DD01T

WHERE DOMNAME = DD01L-DOMNAME

AND AS4LOCAL = 'A'

AND AS4VERS = DD01L-AS4VERS

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

SELECT * FROM DD01V

WHERE DOMNAME LIKE 'CHAR%'

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

Select with index support

SELECT * FROM T100

WHERE ARBGB = '00'

AND MSGNR = '999'.

ENDSELECT.

SELECT * FROM T002.

SELECT * FROM T100

WHERE SPRSL = T002-SPRAS

AND ARBGB = '00'

AND MSGNR = '999'.

ENDSELECT.

ENDSELECT.

Select … Into table

REFRESH X006.

SELECT * FROM T006 INTO X006.

APPEND X006.

ENDSELECT

SELECT * FROM T006 INTO TABLE X006.

Select with selection list

SELECT * FROM DD01L

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

ENDSELECT

SELECT DOMNAME FROM DD01L

INTO DD01L-DOMNAME

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

ENDSELECT

Key access to multiple lines

LOOP AT TAB.

CHECK TAB-K = KVAL.

" ...

ENDLOOP.

LOOP AT TAB WHERE K = KVAL.

" ...

ENDLOOP.

Copying internal tables

REFRESH TAB_DEST.

LOOP AT TAB_SRC INTO TAB_DEST.

APPEND TAB_DEST.

ENDLOOP.

TAB_DEST[] = TAB_SRC[].

Modifying a set of lines

LOOP AT TAB.

IF TAB-FLAG IS INITIAL.

TAB-FLAG = 'X'.

ENDIF.

MODIFY TAB.

ENDLOOP.

TAB-FLAG = 'X'.

MODIFY TAB TRANSPORTING FLAG

WHERE FLAG IS INITIAL.

Deleting a sequence of lines

DO 101 TIMES.

DELETE TAB_DEST INDEX 450.

ENDDO.

DELETE TAB_DEST FROM 450 TO 550.

Linear search vs. binary

READ TABLE TAB WITH KEY K = 'X'.

READ TABLE TAB WITH KEY K = 'X' BINARY SEARCH.

Comparison of internal tables

DESCRIBE TABLE: TAB1 LINES L1,

TAB2 LINES L2.

IF L1 <> L2.

TAB_DIFFERENT = 'X'.

ELSE.

TAB_DIFFERENT = SPACE.

LOOP AT TAB1.

READ TABLE TAB2 INDEX SY-TABIX.

IF TAB1 <> TAB2.

TAB_DIFFERENT = 'X'. EXIT.

ENDIF.

ENDLOOP.

ENDIF.

IF TAB_DIFFERENT = SPACE.

" ...

ENDIF.

IF TAB1[] = TAB2[].

" ...

ENDIF.

Modify selected components

LOOP AT TAB.

TAB-DATE = SY-DATUM.

MODIFY TAB.

ENDLOOP.

WA-DATE = SY-DATUM.

LOOP AT TAB.

MODIFY TAB FROM WA TRANSPORTING DATE.

ENDLOOP.

Appending two internal tables

LOOP AT TAB_SRC.

APPEND TAB_SRC TO TAB_DEST.

ENDLOOP

APPEND LINES OF TAB_SRC TO TAB_DEST.

Deleting a set of lines

LOOP AT TAB_DEST WHERE K = KVAL.

DELETE TAB_DEST.

ENDLOOP

DELETE TAB_DEST WHERE K = KVAL.

Tools available in SAP to pin-point a performance problem

The runtime analysis (SE30)

SQL Trace (ST05)

Tips and Tricks tool

The performance database

Optimizing the load of the database

Using table buffering

Using buffered tables improves the performance considerably. Note that in some cases a stament can not be used with a buffered table, so when using these staments the buffer will be bypassed. These staments are:

Select DISTINCT

ORDER BY / GROUP BY / HAVING clause

Any WHERE clasuse that contains a subquery or IS NULL expression

JOIN s

A SELECT... FOR UPDATE

If you wnat to explicitly bypass the bufer, use the BYPASS BUFFER addition to the SELECT clause.

Use the ABAP SORT Clause Instead of ORDER BY

The ORDER BY clause is executed on the database server while the ABAP SORT statement is executed on the application server. The datbase server will usually be the bottleneck, so sometimes it is better to move thje sort from the datsbase server to the application server.

If you are not sorting by the primary key ( E.g. using the ORDER BY PRIMARY key statement) but are sorting by another key, it could be better to use the ABAP SORT stament to sort the data in an internal table. Note however that for very large result sets it might not be a feasible solution and you would want to let the datbase server sort it.

Avoid ther SELECT DISTINCT Statement

As with the ORDER BY clause it could be better to avoid using SELECT DISTINCT, if some of the fields are not part of an index. Instead use ABAP SORT + DELETE ADJACENT DUPLICATES on an internal table, to delete duplciate rows.

Regds

Anver

if hlped pls mark points

[Former Member]

We are trying to select data from a large indexed table. Even though the select statement is similar to one of the indexes, the sql trace shows the select statement using an index that is less efficient. In our development box it picks the more effiecient index. In production the optimizer chooses the index with the most records basically perfoming a table scan of 16,000,000 records. Can anyone tell me why the optimizer is not choosing the correct index?

[Former Member]

Are the stats (runstats) on the table up to date?

Is the optimizer running on rule based, mixed or cost based?

What is the sequence of the from clause and the where clause?

Enjoy

[Former Member]

We are not sure what optimizer we are using. How do you determine what optimizer is being used.. We ran runstats over the weekend. The select statement is * for the from the where is in order of the index we would like to use. They are not in the same order as the select "from" is following the primary key of the table and where is structured similar to the index we would like to use.

[Former Member]

the answer is "it depends" - not the best answer I'm afraid but optimizer may not select your index if it thinks that it's not "selective" enough, or if one of select-options has blank value as one of the possible values, or... (the case may be - most of the fields in the index you are trying to use have the same value or just a small number of distinct values - very likely that optimizer will not use that index).

I would suggest you check with your BD admins on why this index is not used, in some cases the only way is to use "hint" to force index usage... even SAP suggested this for their code in some OSS notes. However I would try to re-write or re-design your logic to either help otimizer to select your index or to improve performance using other index.

btw, would really help if you tell which table you are tryting to select from :o).

[Former Member]

As most of the people have mentioned above. performace criterion depends on how well you select statements are written,

few points ill like to mention over here are.

1. Select .. endselect statement should not be used as it is a big performance

issue. this will connet to databse for each fetched record.

2. While writing where clause try to give the same sequence of field as given in

batabase table. this improves performance a lot

3. Nested select statement should be avoided. in place of nested select

statemens we can use for all entries statement.

4. Try to use binary search whenever possible.

5. Check if internal table is empty while using for all entries statement.

[LucianoBentiveg]

We was using AboveSoft Analizer, to achieve that,

You can dowload from <a href="http://download.abovesoft.com">here</a>.

Regards.

[Former Member]

Have a look at belwo link. The documentation is available on SDN.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1...

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers

[Former Member]

Try to minimize database access. i.e try to minimize multiple select statements,. Instead of loops for select queries use FOR ALL ENTRIES IN ITAB.

Try to put maximum conditions in where clause, so that database access is fast.

i.e selection frm the database.

Try to use indexes of the tables while searching.

While looping use where condition.

This will surely help you improving your performance

[anjalihora]

There are so many performance tuning techniques in sap abap coding .

Few of them are very common and listed below :

1. Parallel cursor technique 

2. For all entries in 

3. select joins 

4. Using text elements to remove unnecessary ATC check errors and code inspector errors.

5. Debugging