Select appending table and sort bad Performance

[fabio_perencin]

Hello all,

In my code there is a loop on xvbak. For every rows inside xvbak, the following code runs:

IF POSITION IS INITIAL.

     POSITION     = '000000'.

   ENDIF.

   POSITIONS_KEY-MANDT = SY-MANDT.

   POSITIONS_KEY-VBELN = BELEG.

   POSITIONS_KEY-POSNR = POSITION.

   READ TABLE XVBKD WITH KEY POSITIONS_KEY BINARY SEARCH.

   IF SY-SUBRC NE 0.

     SELECT * FROM VBKD APPENDING TABLE XVBKD

                        WHERE VBELN = BELEG

                        AND   POSNR = POSITION.

     SORT XVBKD.

     READ TABLE XVBKD WITH KEY POSITIONS_KEY BINARY SEARCH.

   ENDIF.

For the bad performance of SORT xvbkd I need to modify this code. Can I insert a ORDER BY PRIMARY KEY on the select?

Could this ' ORDER BY PRIMARY KEY' sort all XVBKD or only the appending data?

How can I increse my performance instead?

Thanks in advance

[fabio_perencin]

Hello all,

In my code there is a loop on xvbak. For every rows inside xvbak, the following code runs:

IF POSITION IS INITIAL.

     POSITION     = '000000'.

   ENDIF.

   POSITIONS_KEY-MANDT = SY-MANDT.

   POSITIONS_KEY-VBELN = BELEG.

   POSITIONS_KEY-POSNR = POSITION.

   READ TABLE XVBKD WITH KEY POSITIONS_KEY BINARY SEARCH.

   IF SY-SUBRC NE 0.

     SELECT * FROM VBKD APPENDING TABLE XVBKD

                        WHERE VBELN = BELEG

                        AND   POSNR = POSITION.

     SORT XVBKD.

     READ TABLE XVBKD WITH KEY POSITIONS_KEY BINARY SEARCH.

   ENDIF.

For the bad performance of SORT xvbkd I need to modify this code. Can I insert a ORDER BY PRIMARY KEY on the select?

Could this ' ORDER BY PRIMARY KEY' sort all XVBKD or only the appending data?

How can I increse my performance instead?

Thanks in advance

[Former Member]

Hi Fabio ,

Please Sort the XVBKD before both read statements with the same key as you are using to read it.

See the screen shot below.

Then after first read statement delete the values which u have not of use.

In Select statement select those fields only which u want in sequence as well as in the table.

Hope this helps ....

Regards,

AKS

[Former Member]

hello,

why can't you use a select with for all entries in XVBAK and get all the data one time in XVBKD ?

best regards,

swanand

[jrg_wulf]

Best way would be of cours to follow Swanads advice and use for all entries.

If this for any reason won't work for your case, at least make use of the result of your first reading attempt.

When you use the BINARY SEARCH, SY-TABIX will hold the index, where your current entry should be inserted. So you just have to replace your appending XVBKD statement by using a local workingarea ls_XVBKD like this

SELECT... INTO LS_XVBKD

and than

INSERT ls_XVBKD INTO XVBKD INDEX SY-TABIX.

This way, you'll keep the table sorted automaticly and save the re-sorting which will take more time for each appended line. Repeated sorting of a table gets the worst of the internal sort.

Hope it helps

Regards

Jörg

[amy_king]

Hi Fabio,

Another performance improvement you could make is to avoid performing a select inside of a loop. You mentioned that you perform the above code within a loop at internal table XVBAK. You'll see better performance if instead you perform a join between tables VBAK and VBKD, or as Swanand suggested, use select ... for all entries.

Right now, if your internal table XVBAK has 100 records, you are selecting from VBKD 100 times and sorting the result set 100 times.

Cheers,

Amy

[fabio_perencin]

Hi Amy,

yes I know what you mean, but I have to modify a standard code for the credit check and I can't modify completely the flow logic program...

Thank you very much,

Cheers,

Fabio