performance query

[Former Member]

Hi folks

I have the below code and i am getting performance issue on below code.

Please suggest

      SELECT bukrs belnr gjahr xblnr awkey FROM bkpf INTO
*       TABLE it_bkpf FOR ALL ENTRIES IN it_vbrk WHERE xblnr = it_vbrk-xblnr.
      TABLE it_bkpf FOR ALL ENTRIES IN it_vbrk
                    WHERE bukrs = it_vbrk-bukrs AND
                          XBLNR = it_vbrk-xblnr and
                          awkey = it_vbrk-awkey.

it is taking 2 min to excecute this code and some times if data is high it is taking much time and going on dump not able to resolve it.

Please suggest.

Regards

Ravi

[former_member189845]

Hi Billa,

I checked the code through the code inspector where as you said it giving error on performance checks on

Analysis of where condition for SELECT

But it giving performance issue on this select statement

SELECT vbeln fkart vkorg vtweg knumv fkdat belnr kunrg xblnr bukrs
           FROM vbrk INTO TABLE it_vbrk1
           WHERE fkdat IN p_date   AND
               ( fkart = 'ZUCS' OR
                 fkart = 'ZUDR'  ) AND
                 vkorg = 'G006'    AND
*              ( vtweg = '20'   OR
*                vtweg = '15' )    AND
                 vtweg = '20'   AND
                 fksto NE 'X'.

Where Error is like :  Large table VBRK: No field of a table index in WHERE Condition

And for this select statement

SELECT bukrs belnr gjahr xblnr awkey FROM bkpf INTO

      TABLE it_bkpf FOR ALL ENTRIES IN it_vbrk

                    WHERE bukrs = it_vbrk-bukrs AND

                          XBLNR = it_vbrk-xblnr and

                          awkey = it_vbrk-awkey.

just check on the table which has primary index and use it

[Former Member]

I don't know the complete requirement but at this stage i can only suggest you

to go for secondary index on table BKPF  with fields BUKRS,XBLNR & AWKEY.

[Former Member]

If we have multiple secondary index then also it slows down the performance.

Secondary index order must be same as in where claws.

Use oracle hint if the database you are using is oracle

like :: %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001") along with ur select statement.

  where "SPFLI" "SPFLI~001"  is secondary index ...

[former_member15255]

Hello Sai,

By providing Hints Oracle don't you feel that we are restricting to database Oracle, what if the database is Sequel / DB2 etc.

Regards

Suresh Nair

[uppu_narayan]

Hi Ravikumar,

         Short dump in this may come due to some memory overflow, the reason may be that your internal table is empty so it is selecting all the record resulting in memory overflow........so you got check the is initial condition for internal table...

thanks and regards,

narayan

[Sandeep_Panghal]

You can try OPEN CURSOR technique.

[former_member213851]

Hi Ravikumar,

You are using LOOP inside LOOP for VBRP and VBRK.

So to improve performance use Parallel Cursor Technique as per below Link:

http://www.buzzparas.com/2011/05/parallel-cursor-logic-used-in-sap-abap-1789

Aslo when using READ statement , use BINARY SEARCH as addition to statement.

Don't forget to sort tables before READ and Parallel cursor technique.

Regards,

Sachin

[DirkAltmann]

Hi Ravikumar,

the BKPF has no suitable index for your where clause. Try the following where clause:

                    ....

                    WHERE awtyp = 'VBRK'

                           and awkey = it_vbrk-awkey.

In general, you should take care, that the internal table is not initial. If you have an empty table and use this table by FOR ALL ENTRIES, then you will get all entries from selecting table. In your case, you get the full BKPF if IT_VBRK is empty.

Kind regards

Dirk

[Former Member]

thank  you dirk,

It was working fine.

Regards

Ravi

[Former Member]

Hi Former Member!

There is only 1 key field used in where condition in this query.  So try this:

SELECT bukrs belnr gjahr xblnr awkey FROM bkpf INTO

      TABLE it_bkpf FOR ALL ENTRIES IN it_vbrk

                    WHERE bukrs = it_vbrk-bukrs.

LOOP AT it_bkpf INTO wa_bkpf.

READ TABLE it_vbrk INTO wa_vbrk WITH KEY xblnr = it_vbrk-xblnr awkey = it_vbrk-awkey.

IF SY-SUBRC <>0.

DELETE it_bkpf WHERE bukrs = wa_BKPF-bukrs AND belnr = wa_bkpf-belnr AND gjahr = wa_bkpf-gjahr.

ENDIF.

ENDLOOP.

(and please let me know if i am wrong)

Regards,

Khushboo

[Former Member]

Hi Ravikumar,

Try this code.

if it_vbrk[] is not initial.

clear:it_bkpf,it_bkpf[].

     SELECT bukrs belnr gjahr xblnr awkey

     FROM bkpf

     INTO TABLE it_bkpf

     FOR ALL ENTRIES IN it_vbrk
     WHERE bukrs = it_vbrk-bukrs AND
                     XBLNR = it_vbrk-xblnr and

                      awtyp = 'VBRK' and
                     awkey = it_vbrk-awkey.

endif.

create a secondery index for BKPF table with where clause parameters in the same order.

[former_member15255]

Hi Ravi Kumar

There is one secondary index with following keys

BUKRS

BSTAT

XBLNR

So create a range for BSTAT i.e. R_BSTAT with all possible value ranges

if it_bkpf[] is not initial.

SELECT bukrs belnr gjahr xblnr awkey

FROM bkpf INTO

      TABLE it_bkpf FOR ALL ENTRIES IN it_vbrk

                    WHERE bukrs = it_vbrk-bukrs AND

                          bstat IN  R_BSTAT

                          XBLNR = it_vbrk-XBLNR.

IF IT_BKPF[] IS NOT INITIAL.

LOOP AT it_bkpf INTO wa_bkpf.

READ TABLE it_vbrk INTO wa_vbrk WITH KEY awkey = it_vbrk-awkey.

IF SY-SUBRC <>0.

DELETE it_bkpf  INDEX SY-TABIX.

ENDIF.

ENDLOOP.

ENDIF.

Regards

Suresh Nair

[Former Member]

Use SELECT..ENDSELECT with package size...this will prevent short dumps ..if the data is huge...

Regards,

Faiz