Re: regarding performance issue

Former Member · ‎2007 Jun 08

hi experts

suppose one program performance will take 12 hour,regarding performance tunning technique used i minimised 12 hr to 8 hr.

the main question is again what r the methods/performance that we have to use

that decreases 8 hr to 2 hr.i faced recently this question from ibm.

thanks

subhasis

Former Member · ‎2007 Jun 09

Hope you replied as a good consultant:

IT DEPENDS... and took it from there...:-)

Former Member · ‎2007 Jun 11

Hi,

u should fallow below rules for improving performance of program,

 1. First remove occurs 0 and occurs 0,

use types and delcare structure.

then declare internal table.

ex-

types: begin of ty_lfa1,

kunnr like kna1-kunnr,'end of ty_kna1.

data : i_kna1 type standard table of ty_kna1 with header line.

 instead of inner join use for allentries.

 into corresponding fields of will decrease performance.

 delete adjacent duplicates from itab_final comparing matnr lgort.

place it outside the loop.

 before every select statenet check sy-subrc = 0.if sucessful the write another one.

 don’t' loop an internal table more than once.

and for better performane follow below points

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

Avoid for all entries in JOINS

Try to avoid joins and use FOR ALL ENTRIES.

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

Avoid using Select *.

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

Try to minimize the number of variables to save memory.

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

Avoid creation of index as far as possible

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

Avoid select/select single statements in loops.

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

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

Avoid using ORDER BY in selects

Avoid Nested Selects

Avoid Nested Loops of Internal Tables

Try to use FIELD SYMBOLS.

Try to avoid into Corresponding Fields of

Avoid using Select Distinct, Use DELETE ADJACENT

Go through the following Document

Check the following Links

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

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

Regards

suresh.d

Former Member · ‎2007 Jun 11

Depends on database ,how many records in tables ? if you have more than milion then you can not decrease as such you expected ,may be you can decrease 15 min when you tune the code ..

See the steps :

1. Query including select and sorting functionality

Consider the below queries and their levels of efficiencies is saving the execution time.

Code A

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on

f_{matnr = g}matnr where g_{stlal = '01' order by f}ernam.

Code B

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01'.

sort itab_new by ernam.

Both the above codes essentially do the same function, but the execution time for code B is considerably lesser than that of Code A. Reason: The Order by clause associated with a select statement increases the execution time of the statement, so it is profitable to sort the internal table once after selecting the data.

2. Performance Improvement Due to Identical Statements – Execution Plan

Consider the below queries and their levels of efficiencies is saving the execution time.

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01' .

sort itab_new.

select f_{matnr f}ernam

f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as

f inner join mast as g on f~matnr =

g_{matnr where g}stlal

= '01' .

Code D (Identical Select Statements)

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01' .

sort itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01' .

Both the above codes essentially do the same function, but the execution time for code B is considerably lesser than that of Code A. Reason: Each SQL statement during the process of execution is converted into a series of database operation phases. In the second phase of conversion (Prepare phase) an “execution plan” is determined for the current SQL statement and it is stored, if in the program any identical select statement is used, then the same execution plan is reused to save time. So retain the structure of the select statement as the same when it is used more than once in the program.

3. Reducing Parse Time Using Aliasing

A statement which does not have a cached execution plan should be parsed before execution; this parsing phase is a highly time and resource consuming, so parsing time for any sql query must include an alias name in it for the following reason.

1. Providing the alias name will enable the query engine to resolve the tables to which the specified fields belong to.

2. Providing a short alias name, (a single character alias name) is more efficient that providing a big alias name.

Code E

select j_{matnr j}ernam j_{mtart j}matkl

g_{werks g}aenam g~stlal into table itab_new from mara as

j inner join mast as g on j_{matnr = g}matnr where

g~stlal = '01' .

In the above code the alias name used is ‘ j ‘.

4. Performance Tuning Using Order by Clause

If in a SQL query you are going to read a particular database record based on some key values mentioned in the select statement, then the read query can be very well optimized by ordering the fields in the same order in which we are going to read them in the read query.

Code F

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

end of itab_new.

select MATNR ERNAM MTART MATKL from mara into table itab_new where

MTART = 'HAWA' ORDER BY MATNR ERNAM MTART MATKL.

read table itab_new with key MATNR = 'PAINT1' ERNAM = 'RAMANUM'

MTART = 'HAWA' MATKL = 'OFFICE'.

Code G

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

end of itab_new.

select MATNR ERNAM MTART MATKL from mara into table itab_new where

MTART = 'HAWA' ORDER BY ERNAM MATKL MATNR MTART.

read table itab_new with key MATNR = 'PAINT1' ERNAM = 'RAMANUM'

MTART = 'HAWA' MATKL = 'OFFICE'.

In the above code F, the read statement following the select statement is having the order of the keys as MATNR, ERNAM, MTART, MATKL. So it is less time intensive if the internal table is ordered in the same order as that of the keys in the read statement.

Code F

Code G

5. Performance Tuning Using Binary Search

A very simple but useful method of fine tuning performance of a read statement is using ‘Binary search‘ addition to it. If the internal table consists of more than 20 entries then the traditional linear search method proves to be more time intensive.

Code H

select * from mara into corresponding fields of table intab.

sort intab.

read table intab with key matnr = '11530' binary search.

Code I

select * from mara into corresponding fields of table intab.

sort intab.

read table intab with key matnr = '11530'.

.

Former Member · ‎2007 Jun 11

hi,

->first we have to minimise code redundency.

->avoid inner joins and use FOR ALL ENTRIES.

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

->if u are using Database frequently i.e if u want to retrive the data more than one time from the same table depending on conditionthen first write one select statement using all the conditions and store the data into internal table.

make use of READ statement to retrieve data from that internal table ,this will decrese the database utilization.

->avoid nested looping.

-> if u want o get data depending on checking radio boxes etc,selec the data depending n the condition.i.e

for example if u want to display vendor information when ven check box is clicked,display customer information when cust check box is clicked,instead of retrieving data from all the relavent tables write coding

if ven = 'X'.

write select statement to retrive vendor data.

elseifcust = 'X'.

write select statement to retrive customer data.

endif.

this will decrese performance very well.

->make sure u dont have any unused variable declarations etc.

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

Try to minimize the number of variables to save memory.

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

Avoid creation of index as far as possible

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