topic Re: Optimizing code in Application Development and Automation Discussions

Optimizing code

Former Member — Fri, 24 Nov 2006 13:59:59 GMT

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

Re: Optimizing code

Sandeep_Panghal — Fri, 24 Nov 2006 14:03:21 GMT

Hi,

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

Sandeep

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:07:20 GMT

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:09:15 GMT

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:09:25 GMT

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

Re: Optimizing code

ferry_lianto — Fri, 24 Nov 2006 14:14:32 GMT

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:18:50 GMT

Thanks to all

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

Regards

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:19:53 GMT

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.

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:21:59 GMT

:):):)

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:22:09 GMT

s_vbeln is Selection Option .

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:26:25 GMT

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.

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:31:56 GMT

:):):)

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:47:50 GMT

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 14:51:29 GMT

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

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 15:00:37 GMT

As I told you before try the Join between !

VBAk // VBAP // VBPA !

This should be much better !

Re: Optimizing code

Former Member — Fri, 24 Nov 2006 19:17:56 GMT

Yes!!!!!

Thanks to all

Thanks Erwan

Best regards