Reg:Performance Tuning

[Former Member]

Hi Experts

I have done whatever i can right from checking the:

1. select statements i.e the selection of fields in the correct database table order

2.checking whether there are selects inside loops.

3.checking joins

4.use of binary searches.

5.check on for all entries

6.Removing move corresponding fields etc

yet,the performance of the report has not increased much.

Can anyone suggest any other measures by which it can be increased.

Helpful answers will be rewarded for sure. <= read the rules [here!|https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/rulesofEngagement]

Regards

Rehman

Edited by: Julius Bussche on Jul 30, 2008 3:29 PM

[Former Member]

hi

http://sapmaterial.com/performanc_tuning.html

Regards,

Sravanthi

[former_member181995]

Rehman,

i think this blog very much suitable to your requirement.

Amit.

[former_member194613]

If you want to improve an existing report, then you MUST use the tools to find the actual problem.

All these improvement recommendations are good during development, but they usually don't fix an actual performance problem.

Check

SQL trace:

SE30

What are you total times and what the most expensive statements, DB or ABAP?

Siegfried

[Former Member]

Hello Rehman,

This is your second post which I have had to edit, and you have only made two...

So... welcome to SDN, but please read [the rules|https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/rulesofEngagement] before posting any further.

Cheers,

Julius

[former_member206439]

Hi

1. select statements i.e the selection of fields in the correct database table order

you can refer this statments for performance

Select Statements

these are the points where u need to take care when writing select query Select Queries

Avoid nested selects

Select all the records in a single shot using into table clause of select statement rather than to use Append statements.

When a base table has multiple indices, the where clause should be in the order of the index, either

a primary or a secondary index.

For testing existence , use Select.. Up to 1 rows statement instead of a Select-Endselect-loop with an Exit.

Use Select Single if all primary key fields are supplied in the Where condition .

SQL Interface

Use column updates instead of single-row updates to update your database tables.

For all frequently used Select statements, try to use an index.

Using buffered tables improves the performance considerably.

Aggregate Functions

If you want to find the maximum, minimum, sum and average value or the count of a database column, use a select list with aggregate functions instead of computing the aggregates yourself. Some of the Aggregate functions allowed in SAP are MAX, MIN, AVG, SUM, COUNT, COUNT( * )

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)

Points to be must considered FOR ALL ENTRIES

Check that data is present in the driver table

Sorting the driver table 

Removing duplicates from the driver table

Select Over more than one Internal table

Its better to use a views instead of nested Select statements.

To read data from several logically connected tables use a join instead of nested Select statements. Joins are preferred only if all the primary key are available in WHERE clause for the tables that are joined. If the primary keys are not provided in join the Joining of tables itself takes time.

Instead of using nested Select loops it is often better to use subqueries.

2)Selection Criteria

Restrict the data to the selection criteria itself, rather than filtering it out using the ABAP code using CHECK statement. 

Select with selection list.

Note: It is suggestible to make at least on field mandatory in Selection-Screen as mandatory fields restrict the data selection and hence increasing the performance.

3)Internal tables

Table operations should be done using explicit work areas rather than via header lines.

Always try to use binary search instead of linear search. But donu2019t forget to sort your internal table before that.

A dynamic key access is slower than a static one, since the key specification must be evaluated at runtime.

A binary search using secondary index takes considerably less time.

LOOP ... WHERE is faster than LOOP/CHECK because LOOP ... WHERE evaluates the specified condition internally.

Modifying selected components using u201C MODIFY itab u2026TRANSPORTING f1 f2.. u201C accelerates the task of updating a line of an internal table.

Accessing the table entries directly in a "LOOP ... ASSIGNING ..." accelerates the task of updating a set of lines of an internal table considerably

If collect semantics is required, it is always better to use to COLLECT rather than READ BINARY and then ADD.

"APPEND LINES OF itab1 TO itab2" accelerates the task of appending a table to another table considerably as compared to u201C LOOP-APPEND-ENDLOOP.u201D

u201CDELETE ADJACENT DUPLICATESu201C accelerates the task of deleting duplicate entries considerably as compared to u201C READ-LOOP-DELETE-ENDLOOPu201D.

"DELETE itab FROM ... TO ..." accelerates the task of deleting a sequence of lines considerably as compared to u201C DO -DELETE-ENDDOu201D.

Copying internal tables by using u201CITAB2[ ] = ITAB1[ ]u201D as compared to u201CLOOP-APPEND-ENDLOOPu201D.

Specify the sort key as restrictively as possible to run the program faster.

Hashed and Sorted tables

For single read access hashed tables are more optimized as compared to sorted tables.

For partial sequential access sorted tables are more optimized as compared to hashed tables

4)Typing

Typed Parameters: Specifying the type for formal parameters in the source code, optimizes the code more thoroughly

Typed Field-Symbols: Specifying the type for formal parameters in the source code, optimizes the code more thoroughly

Control Statements (If, Case, While..)

CASE statements are clearer and a little faster than IF-constructions

Use of WHILE instead of a DO+EXIT-construction, is faster to execute.

Field Conversion

Use fields of type I instead of P for typical integral variables like indices.

Use numeric literals or named constants with a number type instead of character strings if you are dealing with type-I or integral type-P fields.

Use properly typed constants instead of literals.

Use number types for arithmetic. Use type-N fields only for pure digit strings that are not intended for calculations e.g, Telephone nos etc.

Don't mix types unless absolutely necessary.

String operations can be made faster by specifying length of character field rather than defining as string.

Tools for Performance Analysis

Run time analysis transaction SE30 :This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing. 

SQL Trace transaction ST05: The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on a particular database table of the ABAP program would be mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

Extended Program Check

This can be called in through transaction SE38 or through transaction SLIN. This indicates possible problems that may cause performance problems.

Code Inspector (SCI)

You can call the Code Inspector from the ABAP Editor (SE38), the Function Builder (SE37), the Class Builder (SE24), or as a separate transaction (SCI).

The Code Inspector indicates possible problems. However, note that, especially with performance issues: There is no rule without exception. If a program passes an inspection, it does not necessarily mean that this program will have no performance problems.