topic Re: performance query in Application Development and Automation Discussions

performance query

Former Member — Tue, 09 Apr 2013 17:56:56 GMT

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

Re: performance query

former_member189845 — Tue, 09 Apr 2013 18:52:48 GMT

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

Re: performance query

Former Member — Wed, 10 Apr 2013 04:36:49 GMT

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.

Re: performance query

uppu_narayan — Wed, 10 Apr 2013 04:48:34 GMT

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

Re: performance query

Sandeep_Panghal — Wed, 10 Apr 2013 04:58:31 GMT

You can try OPEN CURSOR technique.

Re: performance query

former_member213851 — Wed, 10 Apr 2013 05:22:14 GMT

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

Re: performance query

DirkAltmann — Wed, 10 Apr 2013 05:56:20 GMT

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

Re: performance query

Former Member — Wed, 10 Apr 2013 06:01:38 GMT

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

Re: performance query

Former Member — Wed, 10 Apr 2013 07:01:39 GMT

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.

Re: performance query

former_member15255 — Wed, 10 Apr 2013 11:21:37 GMT

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

Re: performance query

Former Member — Wed, 10 Apr 2013 11:32:39 GMT

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

Re: performance query

former_member15255 — Wed, 10 Apr 2013 11:42:21 GMT

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

Re: performance query

Former Member — Wed, 10 Apr 2013 12:06:10 GMT

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

Regards,

Faiz

Re: performance query

Former Member — Tue, 16 Apr 2013 22:14:38 GMT

thank you dirk,

It was working fine.

Regards

Ravi