topic Re: Performance Tuning in Application Development and Automation Discussions

Performance Tuning

Former Member — Thu, 22 Feb 2007 09:42:10 GMT

Hi all,

There is a Report which displays data from 2 tables and uses 2 Select Statements to get the data. I have modified a Report and now there are 5 Select Statements. This has slowed down the report considerably. How to display the data in quick time. ANY TIPS?

Nirav

Re: Performance Tuning

Former Member — Thu, 22 Feb 2007 09:45:09 GMT

Do some of these to improve the performance

1. avoid using nested selects and nested loops.

2. avoid using selects inside a loop.

3. avoid using select ... end select statements

4. avoid using joins ... use for all entries statement ..

regards,

santosh

Re: Performance Tuning

Former Member — Thu, 22 Feb 2007 09:45:40 GMT

Hello Nirav,

Check this link :

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

Re: Performance Tuning

Former Member — Thu, 22 Feb 2007 09:46:07 GMT

You could post part of the code.

But: -

1. Use Inner Joins

2. INTO TABLE

3. No nested loop

4. FOR ALL ENTRIES

5. Use as much of the key as possible

Re: Performance Tuning

Former Member — Thu, 22 Feb 2007 09:46:41 GMT

Hi,

First select data from the Main Table into an Internal Table.

Then for all entries of that Int table and fetch the individual table data into different internal Tables.

Then Loop the first int table and read the data of others into a final table and display.

Or if all the tables are properly linked with proper relation fields then JOINING will fetch the data faster.

Regards,

Anji

Re: Performance Tuning

Clemenss — Thu, 22 Feb 2007 09:48:18 GMT

5. try to use primary or secondary keys in where condition. When using key fields, start with first key field.

6. Select only the fields you need for display/processing - avoid select *

Regards,

Clemens

Re: Performance Tuning

Former Member — Thu, 22 Feb 2007 09:53:12 GMT

Hi,

1) Dont use nested seelct statement

2) If possible use for all entries in addition

3) In the where addition make sure you give all the primary key

4) Use Index for the selection criteria.

5) You can also use inner joins

6) You can try to put the data from the first select statement into an Itab and then in order to select the data from the second table use for all entries in.

7) Use the runtime analysis SE30 and SQL Trace (ST05) to identify the performance and also to identify where the load is heavy, so that you can change the code accordingly

Hope this helps you.

Reward points if found helpful

Re: Performance Tuning

Former Member — Thu, 22 Feb 2007 09:55:38 GMT

Please have a look at below link:

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

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers

Re: Performance Tuning

Former Member — Fri, 23 Feb 2007 05:30:21 GMT

Hi Martin,

Part of code is:

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

SELECT BUDAT MBLNR FROM MKPF INTO CORRESPONDING FIELDS OF

ITAB WHERE BUDAT IN PDATE.

SELECT MATNR BWART DMBTR SAKTO FROM MSEG INTO

CORRESPONDING FIELDS OF ITAB WHERE

MBLNR = ITAB-MBLNR

AND SAKTO IN

('0000404000' , '0000404002' , '0000404006' , '0000404011' ,

'0000404100' , '0000404200', '0000404201', '0000404300',

'0000404400', '0000404800', '0000404900' , '0000405000',

'0000475090', '0000475085').

SELECT SINGLE TXT20 FROM SKAT INTO CORRESPONDING FIELDS OF

ITAB WHERE SAKNR = ITAB-SAKTO AND SPRAS = 'EN'.

SELECT SINGLE MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF

ITAB WHERE MATNR = ITAB-MATNR AND SPRAS = 'EN'.

SELECT SINGLE MATKL FROM MARA INTO CORRESPONDING FIELDS

OF ITAB WHERE MATNR = ITAB-MATNR.

APPEND ITAB.

ENDSELECT.

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

awaiting your tips.

Nirav

Re: Performance Tuning

Former Member — Fri, 23 Feb 2007 05:41:35 GMT

hi,

avaoid using select * instead use select clause

ie select only those fields which you need.

eg : select mara,matnr....from mara.

when using select single in where clause give exactly primary key ,

and when using select single check for sy-subrc = 0

ie eg select single from mara where matnr = '001'.

if sy-subrc = 0.

message e001(z).

and when using for all entries try to test that internal table is empty or not.

avoid join and use for all entries.

avoid using select/select single in loops.

hope these might help you.

cheers!

sri

Re: Performance Tuning

Former Member — Fri, 23 Feb 2007 05:42:35 GMT

Try to avoid select endselect as this statment works as a loop and everytime it accesses the database.

Use different internal tables to fetch the data from the datbase tables.

SELECT BUDAT MBLNR FROM MKPF INTO CORRESPONDING FIELDS OF

ITAB WHERE BUDAT IN PDATE.

if not itab[] is initial.

SELECT MATNR BWART DMBTR SAKTO FROM MSEG INTO

CORRESPONDING FIELDS OF ITAB1 for all entries in itab WHERE

MBLNR = ITAB-MBLNR

AND SAKTO IN

('0000404000' , '0000404002' , '0000404006' , '0000404011' ,

'0000404100' , '0000404200', '0000404201', '0000404300',

'0000404400', '0000404800', '0000404900' , '0000405000',

'0000475090', '0000475085').

SELECT TXT20 FROM SKAT INTO CORRESPONDING FIELDS OF

ITAB2 for all entries in itab WHERE SAKNR = ITAB-SAKTO AND SPRAS = 'EN'.

SELECT MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF

ITAB3 for all entries in itab WHERE MATNR = ITAB-MATNR AND SPRAS = 'EN'.

SELECT MATKL FROM MARA INTO CORRESPONDING FIELDS

OF ITAB4 for all entries in itab WHERE MATNR = ITAB-MATNR.

endif.

Then you can combine data from these internal table into a single final internal table.

If possible try to avoid into corresponding fields by decalring appropriate structure of internal tables.

Instead of for all entries you can use inner joins also if you can specify most of the key fields in where clause.

Best Regards,

Vibha

*Please mark all the helpful answers

Re: Performance Tuning

Former Member — Fri, 23 Feb 2007 05:54:36 GMT

When using the statement:

SELECT * FROM table

In order to extract all records from a given table,

it is more efficient to narrow the selection using specific table fields as search keys.

For example:

SELECT * FROM VBAK... 
WRITE: / VBAK-VBELN...
ENDSELECT.

Instead use:

SELECT VBELN, AUART, BNAME, KUNNR INTO
 (VBAK-VBELN, VBAK-AUART, ….)  
FROM VBAK...
WRITE: / VBAK-VBELN, VBAK-AUART, ...
ENDSELECT.

When using the statement:

SELECT FROM table ... EXIT

In order to EXIT from a select loop when a required condition is met, it is more efficient to use the

UP TO statement as the required exit point.

For example:

SELECT VBELN BNAME … INTO (VBAK-VBELN…) 
FROM VBAK.
 
IF SY-DBCNT > 1000.    
EXIT.       
ENDIF.
 
WRITE: / VBAK-VBELN, VBAK-BNAME…
 
ENDSELECT.

This select loop is exited when 1000 records

have been written. It is better to use a

more focused select statement such as:

SELECT VBELN, AUART, BNAME, KUNNR INTO 
(VBAK-VBELN, VBAK-AUART, ….)  
FROM VBAK… UP TO 1000 ROWS.
 
WRITE: / VBAK-VBELN, VBAK-AUART…
 
ENDSELECT.
 
Where table fields are specified and the 
UP TO statement is used to exit the loop.
When using a nested select statement like:
 
SELECT VBELN AUART VTWEG VKORG SPART 
INTO IVBAK FROM VBAK
WHERE VBELN BETWEEN... AND...
                   
SELECT POSNR MATNR ZMENG MEINS INTO IVBAP 
FROM VBAP
WHERE VBELN = IVBAK-VBELN.
 
     	WRITE: /IVBAK-VBELN, ...
 
ENDSELECT.
 
ENDSELECT.

It is better to use DATABASE VIEWS like:

SELECT * FROM ZV_VBAK
       WHERE VBELN BETWEEN... AND...
       WRITE: ZV_VBAK-VBELN...
ENDSELECT.

When performing an ABAP calculation

within a select statement such as:

SELECT MATNR KWMENG MEINS INTO VBQTY 
FROM VBAP WHERE...
 
COLLECT VBQTY.
 
ENDSELECT.

Where the COLLECT statement is used to calculate

the sum of records populated in internal table VBQTY, it is better to use:

SELECT MATNR SUM(KWMENG) MEINS INTO TABLE VBQTY 
FROM VBAP 
WHERE...
GROUP BY....

When populating an internal table with records from another internal table:

LOOP AT ITAB1.
MOVE ITAB1 TO ITAB2.
	APPEND ITAB2.
ENDLOOP.

It is better to use:

tab2[ ] = itab1[ ].
 
LOOP AT ITAB1.
MOVE ITAB1 TO ITAB2.
	APPEND ITAB2.
ENDLOOP.

It is better to use this statement:

 
APPEND LINES OF ITAB1 TO ITAB2.

When deleting records from an internal table,

like:

LOOP AT IVBAK WHERE BNAME = ‘SMITH’.
DELETE BNAME.
ENDLOOP.

It is better to use this statement:

DELETE IVBAK WHERE BNAME = ‘Smith’.

If you are looping at a table where records have

a certain property it is more efficient to sort

by the property, read the index of the first

record with the property and then loop at the

table from the index, exiting when the property

no longer holds true.

When deleting records from an internal table,

like:

 
LOOP AT IVBAK WHERE BNAME = ‘Smith’.
…
ENDLOOP.

It is significantly more efficient to use these statements:

SORT IVBAK BY BNAME ASCENDING.
READ TABLE IVBAK INTO WVBAK
WITH KEY BNAME = ‘Smith’.
LOOP AT IVBAK FROM INDEX SY-TABIX.
	IF BNAME <> ‘Smith’.
		EXIT.
	ENDIF.
ENDLOOP.

Reward if this helsp.