Performance

Former Member · ‎2008 Feb 26

Friends Pls help me to tune the following Code :

FORM selection.

IF distchn-low = '04' .

SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM vbpa

JOIN zvbrk_rp ON ( vbpa~vbeln = zvbrk_rp~vbeln )

JOIN makt ON ( zvbrk_rp~matnr = makt~matnr )

WHERE zvbrk_rp~vkorg IN salesorg AND

zvbrk_rp~vtweg IN distchn AND

zvbrk_rp~pltyp IN pricelst AND

zvbrk_rp~matnr IN material AND

vbpa~parvw = 'AF' AND

vbpa~pernr IN salesman AND

makt~spras = sy-langu AND

zvbrk_rp~fkdat IN bdate.

LOOP AT itab.

itab-approved = itab-approved * itab-kurrf.

SELECT SINGLE * FROM pa0001 WHERE pernr = itab-pernr.

IF sy-subrc = 0.

MOVE :

pa0001-ename TO itab-ename .

ENDIF.

CLEAR: itab-pltyp, itab-kzwi3.

MODIFY itab.

ENDLOOP.

ELSE.

SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM vbpa

JOIN zvbrk_rp ON ( vbpa~vbeln = zvbrk_rp~vbeln )

JOIN makt ON ( zvbrk_rp~matnr = makt~matnr )

WHERE zvbrk_rp~vkorg IN salesorg AND

zvbrk_rp~vtweg IN distchn AND

zvbrk_rp~pltyp IN pricelst AND

zvbrk_rp~matnr IN material AND

vbpa~parvw = 'AF' AND

vbpa~pernr IN salesman AND

makt~spras = sy-langu AND

zvbrk_rp~fkdat IN bdate.

LOOP AT itab.

itab-approved = itab-approved * itab-kurrf.

SELECT SINGLE * FROM pa0001 WHERE pernr = itab-pernr.

IF sy-subrc = 0.

MOVE : pa0001-ename TO itab-ename .

ENDIF.

CLEAR: itab-kunag, itab-knumv, itab-posnr .

MODIFY itab.

ENDLOOP.

ENDIF.

ENDFORM. " SELECTION

&----

*& Form PREPARATION

&----

text

----

--> p1 text
<-- p2 text

----

FORM preparation.

DATA: gross_sales LIKE zvbrk_rp-kzwi1 VALUE 0,

discount_sa LIKE zvbrk_rp-kzwi5 VALUE 0,

sold_qty LIKE zvbrk_rp-fklmg VALUE 0,

promotion LIKE zvbrk_rp-kzwi3 VALUE 0,

total_approved LIKE itab-approved ,

prom_val TYPE p DECIMALS 2.

DATA: newmatnr TYPE c, endmatnr TYPE c.

IF distchn-low = '04'.

SORT itab BY pernr kunag matnr. "kunag matnr pernr.

LOOP AT itab .

SELECT SINGLE * FROM a005 WHERE kappl = 'V' AND kschl = 'PR00' AND

kunnr = itab-kunag AND matnr = itab-matnr AND

datab <= sy-datum AND datbi >= sy-datum.

IF sy-subrc EQ 0.

SELECT SINGLE * FROM konp WHERE knumh = a005-knumh.

IF sy-subrc = 0.

itab-approved = konp-mxwrt * itab-kurrf.

ENDIF.

SELECT * FROM konv WHERE knumv = itab-knumv

AND kposn = itab-posnr

AND kschl IN ('ZPRP' , 'ZPRV') .

itab-kzwi3 = ( itab-kzwi3 + konv-kwert ) * itab-kurrf.

ENDSELECT.

MODIFY itab.

ENDLOOP.

ELSE.

SORT itab BY pernr pltyp matnr. "pltyp matnr pernr.

LOOP AT itab .

SELECT SINGLE * FROM a006 WHERE kappl = 'V' AND kschl = 'PR00' AND

vkorg = itab-vkorg AND vtweg = itab-vtweg AND

pltyp = itab-pltyp AND waerk = 'AED' AND

matnr = itab-matnr AND datab <= sy-datum AND

datbi >= sy-datum.

IF sy-subrc EQ 0.

SELECT SINGLE * FROM konp WHERE knumh = a006-knumh.

IF sy-subrc EQ 0.

itab-approved = konp-mxwrt * itab-kurrf.

MODIFY itab.

ENDIF.

ENDLOOP.

ENDIF.

LOOP AT itab.

IF itab-pstyv EQ 'ZNNA' OR itab-pstyv EQ 'ZNN7' OR

itab-pstyv EQ 'ZENB' OR itab-pstyv EQ 'ZEN5' OR

itab-pstyv EQ 'ZBV8'.

IF ( itab-vbtyp = 'M' ) OR ( itab-vbtyp = 'P' )

OR ( itab-vbtyp = 'S' ).

prom_val = prom_val + itab-wavwr.

ELSEIF ( itab-vbtyp = 'N' ) OR ( itab-vbtyp = 'O' ).

prom_val = prom_val - itab-wavwr.

ENDIF.

IF ( itab-vbtyp = 'M' ) OR ( itab-vbtyp = 'P' )

OR ( itab-vbtyp = 'S' ).

gross_sales = gross_sales + ( itab-kzwi1 * itab-kurrf ) .

discount_sa = discount_sa + ( itab-kzwi5 * itab-kurrf ).

sold_qty = sold_qty + itab-fklmg .

promotion = promotion + ( itab-kzwi3 * itab-kurrf ).

ENDIF.

IF ( itab-vbtyp = 'N' ) OR ( itab-vbtyp = 'O' ).

gross_sales = gross_sales - ( itab-kzwi1 * itab-kurrf ).

discount_sa = discount_sa - ( itab-kzwi5 * itab-kurrf ).

sold_qty = sold_qty - itab-fklmg .

promotion = promotion - ( itab-kzwi3 * itab-kurrf ).

ENDIF.

AT END OF matnr . "pernr.

endmatnr = 'X'.

ENDAT.

IF endmatnr = 'X'.

MOVE itab-pltyp TO print-pltyp .

MOVE itab-kunag TO print-kunag .

MOVE itab-matnr TO print-matnr .

MOVE itab-maktx TO print-maktx.

MOVE itab-pernr TO print-pernr .

MOVE itab-ename TO print-ename .

MOVE gross_sales TO print-kzwi1 .

MOVE discount_sa TO print-kzwi5 .

MOVE sold_qty TO print-fklmg .

MOVE itab-approved TO print-approved .

MOVE promotion TO print-kzwi3 .

MOVE prom_val TO print-prom_val.

print-netsale = print-kzwi1 - ( -1 * print-kzwi5 ) .

IF print-fklmg = 0.

print-grossunt = 0 .

print-netunit = 0 .

ELSE.

print-grossunt = print-kzwi1 / print-fklmg .

print-netunit = print-netsale / print-fklmg .

ENDIF.

print-diff = print-netunit - print-approved .

print-diffval = print-fklmg * print-diff .

print-totpromv = print-kzwi3 + print-diffval .

APPEND print.

CLEAR print.

CLEAR: gross_sales, discount_sa, sold_qty, promotion,

total_approved,prom_val.

CLEAR endmatnr .

ENDIF.

ENDLOOP.

ENDFORM. " PREPARATION

Thanks & Regds.

Viji..

Former Member · ‎2008 Feb 26

Hi,

By using ST05 run the report and get where exactly , it would have taken more time(dump out) , If suppose you have not defined primary key in select query, define it .

If primary kei is also there go for creating INDEXES for respective database table.

Thanks,

Krishna Rao

Former Member · ‎2008 Feb 26

Instead...

LOOP AT itab.

itab-approved = itab-approved * itab-kurrf.

SELECT SINGLE * FROM pa0001 WHERE pernr = itab-pernr.

IF sy-subrc = 0.

MOVE :

pa0001-ename TO itab-ename .

ENDIF.

CLEAR: itab-pltyp, itab-kzwi3.

MODIFY itab.

ENDLOOP.

select data from pa0001 for all entries in itab...

then loop at itab.

read table it_pa0001 with key ....

move data...

modify itab.

endloop.

This decreases database performance and PA0001 will be hit only once instead so many times untill all records are processed in loop of itab.

Former Member · ‎2008 Feb 26

The problem appears to be in the first SELECT. I don't see that you are using an index.

However, there is a secondary index table VAPMA that will allow you to SELECT sales documents based on material. I'd try that.

Rob

Former Member · ‎2008 Apr 15

ABAP provides few tools to analyse the perfomance of the objects, which was developed by us.

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

by using this tool we can analyse the perfomance issues related to DATABASE calls.

Perfomance Techniques for improve the perfomance of the object.

1) ABAP/4 programs can take a very long time to execute, and can make other processes have to wait before executing. Here are some tips to speed up your programs and reduce the load your programs put on the system:

2) Use the GET RUN TIME command to help evaluate performance. It's hard to know whether that optimization technique REALLY helps unless you test it out.

3) Using this tool can help you know what is effective, under what kinds of conditions. The GET RUN TIME has problems under multiple CPUs, so you should use it to test small pieces of your program, rather than the whole program.

4) Generally, try to reduce I/O first, then memory, then CPU activity. I/O operations that read/write to hard disk are always the most expensive operations. Memory, if not controlled, may have to be written to swap space on the hard disk, which therefore increases your I/O read/writes to disk. CPU activity can be reduced by careful program design, and by using commands such as SUM (SQL) and COLLECT (ABAP/4).

5) Avoid 'SELECT *', especially in tables that have a lot of fields. Use SELECT A B C INTO instead, so that fields are only read if they are used. This can make a very big difference.

6) Field-groups can be useful for multi-level sorting and displaying. However, they write their data to the system's paging space, rather than to memory (internal tables use memory). For this reason, field-groups are only appropriate for processing large lists (e.g. over 50,000 records). If you have large lists, you should work with the systems administrator to decide the maximum amount of RAM your program should use, and from that, calculate how much space your lists will use. Then you can decide whether to write the data to memory or swap space.

Use as many table keys as possible in the WHERE part of your select statements.

7)Whenever possible, design the program to access a relatively constant number of records (for instance, if you only access the transactions for one month, then there probably will be a reasonable range, like 1200-1800, for the number of transactions inputted within that month). Then use a SELECT A B C INTO TABLE ITAB statement.

😎 Get a good idea of how many records you will be accessing. Log into your productive system, and use SE80 -> Dictionary Objects (press Edit), enter the table name you want to see, and press Display. Go To Utilities -> Table Contents to query the table contents and see the number of records. This is extremely useful in optimizing a program's memory allocation.

9) Try to make the user interface such that the program gradually unfolds more information to the user, rather than giving a huge list of information all at once to the user.

10) Declare your internal tables using OCCURS NUM_RECS, where NUM_RECS is the number of records you expect to be accessing. If the number of records exceeds NUM_RECS, the data will be kept in swap space (not memory).

11) Use SELECT A B C INTO TABLE ITAB whenever possible. This will read all of the records into the itab in one operation, rather than repeated operations that result from a SELECT A B C INTO ITAB... ENDSELECT statement. Make sure that ITAB is declared with OCCURS NUM_RECS, where NUM_RECS is the number of records you expect to access.

12) If the number of records you are reading is constantly growing, you may be able to break it into chunks of relatively constant size. For instance, if you have to read all records from 1991 to present, you can break it into quarters, and read all records one quarter at a time. This will reduce I/O operations. Test extensively with GET RUN TIME when using this method.

13) Know how to use the 'collect' command. It can be very efficient.

14) Use the SELECT SINGLE command whenever possible.

15) Many tables contain totals fields (such as monthly expense totals). Use these avoid wasting resources by calculating a total that has already been calculated and stored.

Some tips:

1) Use joins where possible as redundant data is not fetched.

2) Use select single where ever possible.

3) Calling methods of a global class is faster than calling function modules.

4) Use constants instead of literals

5) Use WHILE instead of a DO-EXIT-ENDDO.

6) Unnecessary MOVEs should be avoided by using the explicit work area operations

see the follwing links for a brief insifght into performance tuning,

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

1. Debuggerhttp://help.sap.com/saphelp_47x200/helpdata/en/c6/617ca9e68c11d2b2ab080009b43351/content.htm

2. Run Time Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c6/617cafe68c11d2b2ab080009b43351/content.htm