Improve Select performance

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[jimmy_chan2]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[vinodkumar_thangavel]

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.

[raphael_almeida]

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

[Former Member]

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

[Former Member]

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

[raphael_almeida]

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