topic Re: Improve Select performance in Application Development and Automation Discussions

Improve Select performance

Former Member — Wed, 21 Jan 2015 19:51:50 GMT

I am trying to improve a function module that gets called anywhere between 5 to 45000 times per processing run. In this function module, I found the following select statements:

Original code

    SELECT SINGLE kunnr FROM vbpa INTO customer_number
      WHERE vbeln = wk_document_number
         AND   parvw = 'AG'.

    SELECT SINGLE kunnr FROM vbpa INTO distributor_number
      WHERE vbeln = wk_document_number
         AND   parvw = 'ZE'.

I tried the following 2 variations to get the customer_number and distributor_number

Variant 1

SELECT VBELN PARVW KUNNR
   FROM VBPA into TABLE IT_VBPA
    WHERE VBELN = wk_document_number
           and PARVW in ('AG', 'ZE').

LOOP at IT_VBPA into WA_VBPA.
   CASE WA_VBPA-PARVW.
     WHEN 'AG'.
       customer_number = wa_vbpa-kunnr.
     WHEN 'ZE'.
       distributor_number = wa_vbpa-kunnr.
   ENDCASE.
ENDLOOP.

Variant 2

SELECT PARVW KUNNR into (WA_VBPA-PARVW, WA_VBPA-KUNNR)
   FROM VBPA
    WHERE VBELN = wk_document_number
           and PARVW in ('AG', 'ZE').

   IF WA_VBPA-PARVW = 'AG'.
    customer_number = wa_vbpa-kunnr.
   ENDIF.

   IF WA_VBPA-PARVW = 'ZE'.
    distributor_number = wa_vbpa-kunnr.
   ENDIF.

ENDSELECT.

I ran this test 10 times.

The Original code using 2 Select Single statements had the best performance. averaged about 278 ms

The Variant 1 code had the worst performance averaged about 308 ms

The Variant 2 code performance was nearly identical to the Original code averaged about 285 ms

Any thoughts on how to improve this. The average run times look low, but when there are 45000 records to process, they add up. For example, in the case of the original code, this would be about 208 minutes of processing time and if it was doing the code in Variant 2, it would take 231 minutes.

Thanks

Re: Improve Select performance

Former Member — Wed, 21 Jan 2015 20:43:02 GMT

You might try changing the first select to one based on VBAP, so long as KUNNR is always the actual customer number you want. This way the SELECT SINGLE will be able to use the complete index.

Rob

Re: Improve Select performance

Former Member — Thu, 22 Jan 2015 02:15:37 GMT

Hi, Bob,

I think there are some other process logic you must need call this FM, right?

So maybe you could create a global itab2 use to store VBPA data.

In FM:

SORT gt_itab2 BY vbeln.

READ gt_itab2 into wa_itab2 WITH KEY vbeln = wk_document_number BINARY SEARCHER.

IF SY-SUBRC NE 0.

clear_wa_itab2.

wa_itab2-vbeln = wk_document_number.

     SELECT SINGLE kunnr FROM vbpa INTO customer_number
      WHERE vbeln = wk_document_number
         AND   parvw = 'AG'.
     wa_itab2-customer = customer_number.

    SELECT SINGLE kunnr FROM vbpa INTO distributor_number
      WHERE vbeln = wk_document_number
         AND   parvw = 'ZE'.

wa_itab2-distributor = distributor_number.

APPEND wa_itab2 TO gt_itab2.

ELSE.

customer_number = wa_itab2-customer.

distributor_number = wa_itab2-distributor.

ENDIF.

Hope can help you.

regards,

Archer

Re: Improve Select performance

jimmy_chan2 — Thu, 22 Jan 2015 02:19:44 GMT

Hi,

You should use batch processing as below:

1. Get all related SO (r_vbeln) into interal table IT_VBPA that can read table only one time.

SELECT VBELN PARVW KUNNR

FROM VBPA into TABLE IT_VBPA

WHERE VBELN in r_vblen

and PARVW in ('AG', 'ZE').

2. In looping:

read table it_vbpa into wa_vbpa with key vbeln = l_vbeln PARVW = 'AG'.

customer_number = wa_vbpa-kunnr.

read table it_vbpa into wa_vbpa with key vbeln = l_vbeln PARVW = 'AE'.

distributor_number = wa_vbpa-kunnr.

Best regards,

Jimmy Chan

Re: Improve Select performance

Former Member — Thu, 22 Jan 2015 15:04:51 GMT

I added POSNR in the selects and the results are the same in all 3 variations of the Select statements

Re: Improve Select performance

Former Member — Thu, 22 Jan 2015 15:05:20 GMT

I added 'POSNR' in the selects and the results are the same in all 3 variations of the Select statements

Re: Improve Select performance

Former Member — Thu, 22 Jan 2015 15:41:04 GMT

Actually, I meant VBAK. See if this works:

SELECT SINGLE vbak~kunnr vbpa~kunnr

INTO (customer_number, partner_number)

FROM vbak

JOIN vbpa ON

vbpa~vbeln = vbak~vbeln

WHERE vbpa~parvw IN ('AG', 'ZE').

Rob

Re: Improve Select performance

Former Member — Thu, 22 Jan 2015 16:51:19 GMT

The big issue is that this code is in a pricing condition exit RV64xxxx, so it gets called several times for each line item.

Re: Improve Select performance

Former Member — Thu, 22 Jan 2015 20:57:53 GMT

The main program (that is calling the RV64xxxx ) should be saving all partner data in an internal table. IF it is not then you can build an internal table with all partner data and then read that internal table inside RV64xxxx. If required you can pass the internal table using export statement and then import it inside RV64xxxx. This way you can avoid using select statement inside RV64xxxx and improve performance.

Re: Improve Select performance

vinodkumar_thangavel — Fri, 23 Jan 2015 04:40:02 GMT

Hi,

U shall try the below code by avoiding LOOP.

SELECT VBELN PARVW KUNNR

FROM VBPA into TABLE IT_CUST

WHERE VBELN = wk_document_number

and PARVW in ('AG', 'ZE').

IT_DIST[] = IT_CUST[].

sort IT_DIST by ZE.

sort IT_CUST by AG.

Delete IT_DIST where PARVW ne 'ZE'.

Delete IT_CUST where PARVW ne 'AG'.

Regards,

Vinodkumar.

Re: Improve Select performance

Former Member — Fri, 23 Jan 2015 13:58:48 GMT

Checked it out, but the code is being called from SAPLV61A forms XKOMV_KWERT_ERMITTELN and XKOMV_BEWERTEN. So, I cannot add code there. I think I will try to find an earlier user exit and Export to Memory

Re: Improve Select performance

raphael_almeida — Fri, 23 Jan 2015 22:11:16 GMT

Hi Bob !

Have you tried using up to x rows with endselect?

Another way for you to improve your performance is to create cursors and put limits packages. The two ways reduced the query time x runtime.

Adding, you can put your declarations of internal table as hashed tables and use the primary keys to search.

BR,

Raphael

Re: Improve Select performance

Former Member — Tue, 27 Jan 2015 13:15:53 GMT

Hi Bob,

I am trying to answer as per my understanding and assumption. I am assuming that your code is getting called many times for same document number.

If my assumption is right then may be you can build the logic to fetch data from VBPA and store in some variables or internal table when your code gets executed first time and then you can check this variables and if entries are exist in these variables then do not execute select statements and use the variable values.

Like this,

if customer_number is initial.

    SELECT SINGLE kunnr FROM vbpa INTO customer_number
      WHERE vbeln = wk_document_number
         AND   parvw = 'AG'.

endif.

if distributor_number is INITIAL.

    SELECT SINGLE kunnr FROM vbpa INTO distributor_number
      WHERE vbeln = wk_document_number
         AND   parvw = 'ZE'.

ENDIF.

may be you have to export customer_number and distributor_number to memory at first execution and IMPORT and use same on subsequent execution.

Let me know if this helps.

Regards,

Sid

Re: Improve Select performance

raphael_almeida — Fri, 30 Jan 2015 15:25:57 GMT

Hello Bob,

You could find the answer to your problem?

Remember to close this topic if you have found, following as explained in the document below, oks ?!

BR,

Raphael

Re: Improve Select performance

Former Member — Fri, 30 Jan 2015 16:11:25 GMT

yes, I believe export to memory is the only way to handle this. Thanks