Optimizing code

[Former Member]

Hi, I want to optimize the next select.... it tooks almost 4 minutes to execute

vbpa have 5.200.182 records

vbap have 1.344.536 records

SELECT DISTINCT a_{kunnr b}werks

INTO TABLE t_cus_werk

FROM ( vbpa AS a INNER JOIN vbap AS b ON a_{vbeln = b}vbeln )

WHERE b~werks = p_werks AND

b~erdat IN r_date.

If we could reduce the time to half

Best regards

Juan

[Sandeep_Panghal]

Hi,

U can try b y breaking the inner join into individual select statements.

Sandeep

[Former Member]

I would put the VBAP table first in the Inner Join.

otherwise, can you add a restriction on VBAK( vkorg... ) for eg ?

Then Join VBAK>VBAP>VBPA...

This should be much better !

Erwan

[Former Member]

HI,

Try by using separate select statements..

select distinct kunnr from <table eg.a> into <internal table eg.itab> where condition.

select distinct fieds from <table eg.b> into table< itab2 ><b>for all entries</b> in itab1 where condition.

reward if helpful.

regards,

AJith

[Former Member]

use for all entries ...

first select all the entries into it_vbap ..

select vbeln ...
          into table it_vbap 
          where vbeln in s_vbeln 
*                 werks = p_werks
*                 erdat in r_date.
 

select kunnr
        werks 
        into table t_cusweek  
        from vbpa for all entries in it_vbap 
       where vbeln = it_vbap-vbeln 
                werks = p_werks
                erdat in r_date.

sort t_cusweek by kunnr werks.

delete adjacent duplicates t_cusweek comparing kunnr werks.

this will fetch u the distinct records limiting the number of searches .

regards,

vijay

[Former Member]

Thanks to all

vijay, ur solution is very interesting, but "in s_vbeln" is confusing me.

Regards

[Former Member]

Hi,

Vijay's querry looking good but before using the second select stmt for all entries pl check the content of the internal table it_vbap .

Just add the following before the second select:

IF NOT it_vbap[] IS INITIAL.

....

...

ENDIF.

Cheers.

[Former Member]

:):):)

juan ..

its ur sales order range ..

<b>select-options: s_vbeln for vbak-vbeln .</b>

i hope u will be doing for the range of sales orders right ,

so select the entries into ur table based on that ..

regards,

vijay

[Former Member]

s_vbeln is Selection Option .

[Former Member]

thanks for the back up ..sunanda ..

forgot to add that ..

juan

before second select

do this

if not it_vbap[] is initial.

select ....
         ....
        for all entries  in it_vbap..

endif.

[Former Member]

:):):)

i need all the customers from the selected plant.

The problem is because in kna1 not all the customers have plant... so i need to go to vbap

Regards

[ferry_lianto]

Hi Juan,

Please try this.

SELECT a_{kunnr b}werks

INTO TABLE t_cus_werk

FROM ( vbpa AS a INNER JOIN vbap AS b ON a_{vbeln = b}vbeln )

WHERE b~werks = p_werks AND

b~erdat IN r_date.

SORT t_cust_werk.

DELETE ADJACENT DUPLICATES FROM t_cust_werk.

Also please check this links for performance tuning tips.

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

http://www.sap-img.com/abap/abap-fine-tuning.htm

Hope this will help.

Regards,

Ferry Lianto

[Former Member]

ERDAT is not an index field on VBAP, but it is on VBAK. So, try:

SELECT DISTINCT a~kunnr b~werks
  INTO TABLE t_cus_werk
  FROM   vbpa AS a
    INNER JOIN vbap AS b
      ON a~vbeln = b~vbeln
    INNER JOIN vbak AS c
      ON a~vbeln = c~vbeln
  WHERE b~werks = p_werks AND
        c~erdat IN r_date.

Rob

[Former Member]

Hi Juan,

It is basically a flawed approach. Getting a few records of master data searching a lot of transactional data is not the best of solutions.

Whatever might be the optimization you do, you would endup selecting all the records from atleast one of the table, which hurts the performance.

You should probably sit with your functional consultant for better alternatives.

Regards,

Ravi Kanth Talagana

[Former Member]

As I told you before try the Join between !

VBAk // VBAP // VBPA !

This should be much better !

[Former Member]

Yes!!!!!

Thanks to all

Thanks Erwan

Best regards