how to reduce the execution time.....

[Former Member]

hello everybody....

I am developing an inventory report but while fetching data from table vbak & vbap it takes a lot of time.

the code of data fetching is as follows :

SELECT vbak_{vbeln vbak}bstnk vbap_{posnr vbap}matnr vbap~kwmeng

INTO TABLE ig_vbak

FROM vbap INNER JOIN vbak

ON vbak_{vbeln EQ vbap}vbeln

WHERE

vbak~auart EQ p_auart AND

vbak~vkorg EQ p_vkorg AND

vbak~vtweg EQ p_vtweg AND

vbak~spart EQ p_spart AND

vbak~kunnr EQ p_kunnr AND

vbap~werks EQ p_werks.

Is there any alternate way so that the execution time reduces...

thanks,

nitya

[Former Member]

hello everybody....

I am developing an inventory report but while fetching data from table vbak & vbap it takes a lot of time.

the code of data fetching is as follows :

SELECT vbak_{vbeln vbak}bstnk vbap_{posnr vbap}matnr vbap~kwmeng

INTO TABLE ig_vbak

FROM vbap INNER JOIN vbak

ON vbak_{vbeln EQ vbap}vbeln

WHERE

vbak~auart EQ p_auart AND

vbak~vkorg EQ p_vkorg AND

vbak~vtweg EQ p_vtweg AND

vbak~spart EQ p_spart AND

vbak~kunnr EQ p_kunnr AND

vbap~werks EQ p_werks.

Is there any alternate way so that the execution time reduces...

thanks,

nitya

[Former Member]

Use for all entries...

select data from first table.....into itab1

sort itab by key field....delete adjacent duplicates....

now select fields from table2 into itab2 for all entries in itab1 where commonfield = itab1-commonfield.

SELECT vbeln

FROM vbak

INTO table lt_vbak

WHERE vkorg = c_vkorg

AND vtweg = c_vtweg

AND spart = c_spart

AND ( auart = 'tip' OR auart = 'tap')

AND vdatu IN r_vdatu

AND bname = space.

SELECT vbeln posnr werks matnr kwmeng

FROM vbap

INTO table i_vbap

FOR ALL ENTRIES IN lt_vbak

WHERE vbeln = lt_vba-vbeln

AND matnr IN r_matnrnf1

AND werks IN r_dfl

AND spart NE 'F1'.

Since both the tables VBAK and VBAP are quite huge, a Join on these tables may not be desirable from the performance point of view.

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

REPORT ZDEMO.

TYPES: BEGIN OF TY_VBAK,

VBELN LIKE VBAK-VBELN,

VDATU LIKE VBAK-VDATU,

END OF TY_VBAK.

TYPES: BEGIN OF TY_VBAP,

VBELN LIKE VBAP-VBELN,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

WERKS LIKE VBAK-WERKS,

KWMENG LIKE VBAP-KWMENG,

END OF TY_VBAP.

DATA: IT_VBAK TYPE STANDARD TABLE OF TY_VBAK,

WA_VBAK LIKE LINE OF IT_VBAK,

IT_VBAP TYPE STANDARD TABLE OF TY_VBAP,

WA-VBAP LIKE LINE OF IT_VBAP.

SELECT VBELN

VDATU

INTO TABLE IT_VBAK

FROM VBAK

WHERE VKORG = C_VKORG

AND VTWEG = C_VTWEG

AND SPART = C_SPART

AND ( AUART = 'TIP' OR AUART = 'TAP')

AND VDATU IN R_VDATU

AND BNAME = SPACE.

IF SY-SUBRC = 0.

SELECT VBELN

POSNR

MATNR

WERKS

KWMENG

FROM VBAP INTO TABLE IT_VBAP

FOR ALL ENTRIES IN IT_VBAK

AND MATNR IN R_MATNRNF1

AND WERKS IN R_DFL

AND SPART NE 'F1'.

IF SY-SUBRC <> 0.

ENDIF.

ENDIF.

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

Regards

Vasu

Message was edited by:

Vasu G

[Former Member]

Hi

create an secondary Index on the where condition fields and execute again.

just execute this and see

SELECT vbak_{vbeln vbak}bstnk vbap_{posnr vbap}matnr vbap~kwmeng

INTO TABLE ig_vbak

FROM vbak INNER JOIN vbap

ON vbak_{vbeln EQ vbap}vbeln

WHERE

vbak~auart EQ p_auart AND

vbak~vkorg EQ p_vkorg AND

vbak~vtweg EQ p_vtweg AND

vbak~spart EQ p_spart AND

vbak~kunnr EQ p_kunnr AND

vbap~werks EQ p_werks.

the performance tips are

1) Dont use nested select statements

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

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

ABAP performance depends upon various factors and in devicded in three parts:

1. Database

2. ABAP

3. System

Run Any program using SE30 (performance analys) to improve performance refer to tips and trics section of SE30, Always remember that ABAP perfirmance is improved when there is least load on Database.

u can get an interactive grap in SE30 regarding this with a file.

also if u find runtime of parts of codes then use :

Switch on RTA Dynamically within ABAP Code

*To turn runtim analysis on within ABAP code insert the following code

SET RUN TIME ANALYZER ON.

*To turn runtim analysis off within ABAP code insert the following code

SET RUN TIME ANALYZER OFF.

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

Check the following Links

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

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

check the below link

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

See the following link if it's any help:

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

Check also http://service.sap.com/performance

and

books like

http://www.sap-press.com/product.cfm?account=&product=H951

http://www.sap-press.com/product.cfm?account=&product=H973

http://www.sap-img.com/abap/more-than-100-abap-interview-faqs.htm

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

Performance tuning for Data Selection Statement

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

Debugger

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

http://www.cba.nau.edu/haney-j/CIS497/Assignments/Debugging.doc

http://help.sap.com/saphelp_erp2005/helpdata/en/b3/d322540c3beb4ba53795784eebb680/frameset.htm

Run Time Analyser

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

SQL trace

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

CATT - Computer Aided Testing Too

http://help.sap.com/saphelp_47x200/helpdata/en/b3/410b37233f7c6fe10000009b38f936/frameset.htm

Test Workbench

http://help.sap.com/saphelp_47x200/helpdata/en/a8/157235d0fa8742e10000009b38f889/frameset.htm

Coverage Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c7/af9a79061a11d4b3d4080009b43351/content.htm

Runtime Monitor

http://help.sap.com/saphelp_47x200/helpdata/en/b5/fa121cc15911d5993d00508b6b8b11/content.htm

Memory Inspector

http://help.sap.com/saphelp_47x200/helpdata/en/a2/e5fc84cc87964cb2c29f584152d74e/content.htm

ECATT - Extended Computer Aided testing tool.

http://help.sap.com/saphelp_47x200/helpdata/en/20/e81c3b84e65e7be10000000a11402f/frameset.htm

Just refer to these links...

You can go to the transaction SE30 to have the runtime analysis of your program.Also try the transaction SCI , which is SAP Code Inspector.

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

2 Avoid for all entries in JOINS

3 Try to avoid joins and use FOR ALL ENTRIES.

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

5 Avoid using Select *.

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

7 Try to minimize the number of variables to save memory.

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

9 Avoid creation of index as far as possible

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

11 Avoid select/select single statements in loops.

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

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

14 Avoid using ORDER BY in selects

15 Avoid Nested Selects

16 Avoid Nested Loops of Internal Tables

17 Try to use FIELD SYMBOLS.

18 Try to avoid into Corresponding Fields of

19 Avoid using Select Distinct, Use DELETE ADJACENT.

<b><REMOVED BY MODERATOR></b>

regards

Anji

Message was edited by:

Alvaro Tejada Galindo

[hermanoclaro]

Hi Nitya.

Try changing the code you sent with the code below:

SELECT vbeln bstnk
  FROM vbak
  INTO TABLE gt_vbak
  WHERE
    auart = p_auart AND
    vkorg = p_vkorg AND
    vtweg = p_vtweg AND
    spart = p_spart AND
    kunnr = p_kunnr.
IF sy-subrc = 0.
  SELECT posnr matnr kwmeng
    FROM vbap
    INTO TABLE gt_vbap
    FOR ALL ENTRIES IN gt_vbak
    WHERE
      vbeln = gt_vbeln AND
      werks = p_werks.
  IF sy-subrc <> 0.
    REFRESH: gt_vbap.
  ENDIF.
ELSE.
  REFRESH: gt_vbak.
ENDIF. 

Best Regards,

-h

[George_Lioumis]

1) define all variables and tables using TYPE instead of LIKE

2) Check table VAKPA, its key fields and indices and replace the JOIN between VBAK and VBAP by doing the following:

a) do a first select in VAKPA as follows:

data: begin of i_vbeln occurs 0,

vbeln type vbeln,

end of i_vbeln.

select vbeln from vakpa into table i_vbeln

where kunde = p_kunnr

and parvw = 'AG' "or whatever this customer is

and vkorg = p_vkorg

and vtweg = p_vtweg

and spart = p_spart

and auart = p_auart.

b) now, perform the join in VBAK and VBAP using in the where clause only the VBELN from i_vbeln (use for all entries syntax) and fetch ONLY the fields that you need. Generally, AVOID using select *.

3) tell you BASIS people to check the indices of VBAK and VBAP (transaction DB02 I think)

4) try using hashed tables instead of standard tables

Hope this helps,

<b><REMOVED BY MODERATOR></b>

George

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

Well, the problem is that you aren't using any index for this SELECT

You might try approaching it differently - from the FI side. I'm not sure if this would work or not, but you could get FI documents for the customer from BSID and BSAD. The reference document number XBLNR or assignment number ZUONR might have the document number you are looking for.

Rob

[Former Member]

Break the SELECT statement as recommended above. The biggest problem with your join is that you have FROM vbap. VBAP (Line Item Detail) is a much larger Table than VBAK (Document Header), thus you are searchihg through more records than you need to, as you are first looking in the VBAP table and then finding the matching records in VBAK. Best to start at the Header Table first, then next look at the Detail. Still 2 Selects into an Internal Table would be the best choice.

[Former Member]

had to use secondary index.