Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

performance query

Former Member
0 Kudos

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

1 ACCEPTED SOLUTION

DirkAltmann
Active Participant
0 Kudos

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

13 REPLIES 13

former_member189845
Active Participant
0 Kudos

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
0 Kudos

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.

0 Kudos

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 ...

0 Kudos

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
Active Participant
0 Kudos

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_Kumar
Product and Topic Expert
Product and Topic Expert
0 Kudos

You can try OPEN CURSOR technique.

former_member213851
Active Contributor
0 Kudos

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
Active Participant
0 Kudos

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

0 Kudos

thank  you dirk,

It was working fine.

Regards

Ravi

Former Member
0 Kudos

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
0 Kudos

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.


0 Kudos

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_member215563
Active Participant
0 Kudos

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

Regards,

Faiz