performance problem

[Former Member]

Dear Firends,

this report needs almost 3 hours in the Batch Mode .

Do you see any possibility to optimize it bacause of the Performance in order

to get it faster.

Thanks

Ilhan Ertas

TYPES: BEGIN OF ty_purchase_dat.
        INCLUDE STRUCTURE ebkn.
TYPES: preis  TYPE bp_wpl, 
       waers  TYPE waers,
       ername TYPE ernam.
TYPES: END OF ty_purchase_dat.

TABLES: eban, zupdpreq, aufk, ebkn .


DATA: lt_eban      TYPE TABLE OF eban,
      ls_eban      TYPE eban,
      lt_zupdpreq  TYPE TABLE OF zupdpreq,
      ls_zupdpreq  TYPE zupdpreq,
      lt_aufk      TYPE TABLE OF aufk,
      ls_aufk      TYPE aufk,
      lt_ebkn      TYPE TABLE OF ebkn,
      ls_ebkn      TYPE ebkn,
      lt_purhdat   TYPE TABLE OF ty_purchase_dat,
      ls_purhdat   TYPE          ty_purchase_dat,
      ls_pur       TYPE          ty_purchase_dat,
      lv_obj       TYPE          bp_objekt.

*SELECT  * INTO TABLE lt_eban FROM eban
*                            WHERE knttp EQ 'F'.

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_purhdat
                            FROM       ebkn AS a
                            INNER JOIN eban AS b ON
                            a~banfn = b~banfn    AND
                            a~bnfpo = b~bnfpo
                            WHERE b~knttp EQ 'F'.

SELECT * INTO TABLE lt_zupdpreq FROM zupdpreq.


LOOP AT lt_purhdat INTO ls_purhdat.

  READ TABLE lt_zupdpreq INTO ls_zupdpreq
                         WITH KEY banfn = ls_purhdat-banfn
                                  bnfpo = ls_purhdat-bnfpo.
  IF sy-subrc EQ 4.
    IF NOT ls_purhdat-aufnr IS INITIAL.
      lv_obj =  ls_purhdat-aufnr.
      CALL FUNCTION 'Z_PM_SRM'
        EXPORTING
          wrt   = ls_purhdat-preis
          objnr = lv_obj
          waers = ls_purhdat-waers.
*
      ls_zupdpreq-banfn  = ls_purhdat-banfn.
      ls_zupdpreq-bnfpo  = ls_purhdat-bnfpo.
      ls_zupdpreq-aufnr  = ls_purhdat-aufnr.
      ls_zupdpreq-erdat  = sy-datum.
      ls_zupdpreq-ernam  = sy-uname.
      ls_zupdpreq-preis  = ls_purhdat-preis.
      INSERT INTO zupdpreq VALUES ls_zupdpreq.
    ENDIF.
  ENDIF.
ENDLOOP.

Please provide a more informative subject.

Moving to the correct forum.

Edited by: Rob Burbank on Feb 24, 2009 10:25 AM

[former_member404244]

Hi,

avoid into corresponding sattement..

decalre the internal tables with the fields u require and then

select a_{field1 a}field2 b~field1FROM ebkn AS a

INNER JOIN eban AS b ON

a_{banfn = b}banfn AND

a_{bnfpo = b}bnfpo

into table lt_purhdat

WHERE b~knttp EQ 'F'.

select field1 feild2 from zupdpreq into table lt_zupdpreq .

if sy-subrc eq 0.

sort lt_zupdpreq by banfn bafpo.

endif.

LOOP AT lt_purhdat INTO ls_purhdat.

READ TABLE lt_zupdpreq INTO ls_zupdpreq

WITH KEY banfn = ls_purhdat-banfn

bnfpo = ls_purhdat-bnfpo

binary search.

IF sy-subrc EQ 4.

IF NOT ls_purhdat-aufnr IS INITIAL.

lv_obj = ls_purhdat-aufnr.

CALL FUNCTION 'Z_PM_SRM'

EXPORTING

wrt = ls_purhdat-preis

objnr = lv_obj

waers = ls_purhdat-waers.

*

ls_zupdpreq-banfn = ls_purhdat-banfn.

ls_zupdpreq-bnfpo = ls_purhdat-bnfpo.

ls_zupdpreq-aufnr = ls_purhdat-aufnr.

ls_zupdpreq-erdat = sy-datum.

ls_zupdpreq-ernam = sy-uname.

ls_zupdpreq-preis = ls_purhdat-preis.

INSERT INTO zupdpreq VALUES ls_zupdpreq.

ENDIF.

ENDIF.

ENDLOOP.

Regards,

Nagaraj

[Former Member]

hi:

1. In stead using select * , use select <field list.

2. avoid corresponding fields

3. use open cursor as

[Link|http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb3b23358411d1829f0000e829fbfe/content.htm]

Regards

Shashi

[Former Member]

Hi,

Suggestion.

Always your performance depends on the select statement and number of fields,

If you restrict your selection in the select statement rather than using * .

try to create internal table with selected fields and select the same fields in the select statement.

And in the second select statement try to put any filter(condition) in your select statement.

and Also if possible Use your selection fields in your select statement.

Hope this helps you

Regards,

Amitteja

[Former Member]

Avoid into corresponding fields of ..

Sort lt_zupdpreq .. and while reading use binary search ..

[Former Member]

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_purhdat

FROM ebkn AS a

INNER JOIN eban AS b ON

a_{banfn = b}banfn AND

a_{bnfpo = b}bnfpo

WHERE b~knttp EQ 'F'.

in where condition you have to pass BANFN and BNFPO values also

and create the secondry index for knttp

[ThomasZloch]

Likely causes:

1) EBAN select, no index is used,

2) EBAN/EBKN Join, no index is used, duplicate EBAN access

3) ZUPRDREQ, no WHERE condition, how large is this table?

4) READ TABLE lt_zupdpreq, does not use binary search

5) CALL FUNCTION 'Z_PM_SRM', only you know what's going on inside

So: use indexes and binary search.

Thomas

[Former Member]

thomas thx.

Can you pls help me to use index. I have never used index any before.

1) EBAN select, no index is used, 
2) EBAN/EBKN Join, no index is used, duplicate EBAN access

4) READ TABLE lt_zupdpreq, does not use binary search

And secondly is it enough if use the sort command and in t is binary search like ?

sort  lt_zupdpreq by banfn bnfpo

READ TABLE lt_zupdpreq INTO ls_zupdpreq

WITH KEY banfn = ls_purhdat-banfn

bnfpo = ls_purhdat-bnfpo

binary search.

Please notice the binary search statement occurs at the end of my read coomand

regards

Ilhan

[ThomasZloch]

The binary search works OK like that, sort first then use "binary search" at end of read statement, OR use internal tables defined as TYPE SORTED TABLE OF ...

> I have never used index any before

Never too late to start. Examine the primary and secondary indexes of EBAN in SE11 and see if you can provide any of the fields in those indexes, starting top-down (e.g. MATNR would be nice or EKGRP). Just KNTTP is not enough, even if you defined your own index for it, it would probably not be very selective, although there would be some improvement (depends on the percentage of "F" values compared to the overall number of entries).

Also, run your program through code inspector, it will tell you all places where index support is missing.

Thomas

[Former Member]

Hi Erdem,

Please follow the above instructions and also check with this :

Try creating Index for the field knttp in the table EBAN.

Go to SE11 --> table name VBAK-->Click on Display-->You will find tegh INdex button at the right hand-top side of the application. Click on it and click on Create Index-->
-->Provide a name for it and click on -->Maintain it in Log on language-->Provide the field name and click on Activate.

Regards,

Kittu

[Former Member]

Hi Kittu,

creating Index for the field knttp in the table EBAN its not a problem.

Is it necessary to refer to the created index within the abap code ?

Regards

sas

[Former Member]

Creating an index on a field is one of the most common recommendations in this forum. Unfortunately, it is usually wrong. In this case among all the other problems, you have to consider whther this single field would b e selective enough to help.

Rob

[Former Member]

what means creating index.

Regards

sas

[Former Member]

Hi ,

Definition for Creating Index from SAP.help.com.

An index is a copy of a database table that is reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example, when using a binary search. A table has a primary index and a secondary index. The primary index consists of the key fields of the table and is automatically created in the database along with the table. You can also create further indexes on a table in the Java Dictionary. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the primary index. Different indexes for the same table are distinguished from one another by a separate index name. The index name must be unique. Whether or not an index is used to access a particular table, is decided by the database system optimizer. This means that an index might improve performance only with certain database systems. You specify if the index should be used on certain database systems in the index definition. Indexes for a table are created when the table is created (provided that the table is not excluded for the database system in the index definition). If the index fields represent the primary keys of the table, that is, if they already uniquely identify each record of the table, the index is referred to as an unique index.

Hope now you are clear about index.

Thanks

Mani

[Former Member]

hi is that all what I need !

Go to SE11 --> table name VBAK-->Click on Display-->You will find tegh INdex button at the right hand-top side of the application. Click on it and click on Create Index-->
-->Provide a name for it and click on -->Maintain it in Log on language-->

But where is the connection or corelation to my abap code.

How do I refer to the created index from abap code

bye

ilhan

[Former Member]

Hi ,

Index acts like key .

When your fetching the data in a table and in the condition if you use Key fields the performance will be more than ,

when you fecth the data in table and in condition if you are not using key it has no binary serach (Key search in a field and it takes some time to fid out the value.

Hope thsi helps ,

Regards,

Amitteja

[Former Member]

Hi Erdem,

the database is aware of your index and chooses an access plan to retrieve the data form the index.

The index stores pointers that are used by the DB to do a fast-lookup in the table.

This plan is generated by the DB optimizer and it's the instance wich decides what plan to generate.

The generation is influenced by data volume and the DB objects (i.e. indexes) present.

This is ALL done behind the scenes of your ABAP. You only have to provide (important!) a meaningfull index key (must be selective as Rob stated).

Normally you would look at your WHERE clause and choose the index key on the fields involved.

But a basic understanding of DB objects as tables (and their data volume) and indexes AND the SQL you write to retrieve the data is inevitable!

The mentioned access plan can everything form worse to best if you don't know about gains and pains to speed up data access via an index.

bye

yk

[Former Member]

YukonKid that it thanks a lot.

Regards

sas

[Former Member]

Hi ,

Once you done the above steps ; creating seconday index for table,activate the same.

If you dispatch an SQL statement from an ABAP program to the database, the program searches for the data records requested either in the database table itself (full table scan) or by using an index ( index unique scan or index range scan). If all fields requested are found in the index using an index scan, the table records do not need to be accessed.

Cheers

Mani

[Former Member]

Hi,

I am sorry to ask again but do you have a small coding about for me to understand better.

Regards

sas