topic Re: perfomance optimization code - Select Statement in Application Development and Automation Discussions

perfomance optimization code - Select Statement

bhat_vaidya2 — Thu, 27 May 2010 05:55:30 GMT

Hi,

I wrote a piece of code that is working well but my boss wants me to rewrite the code due to performance since we are going to process huge amount of data using READ KEY and Internal table.

Code

Data rp TYPE _ty_s_TG_1.
LOOP AT RESULT_PACKAGE INTO rp.
  SELECT SINGLE COST FROM DSO_TABLE  INTO rp-COST
        WHERE ITEMID EQ rp-ITEMID.
        MODIFY RESULT_PACKAGE FROM rp.

  Clear rp.

ENDLOOP.

How do I rewrite this code for better performance.

thanks

Edited by: Matt on May 27, 2010 8:23 AM

Re: perfomance optimization code - Select Statement

former_member209217 — Thu, 27 May 2010 06:01:16 GMT

Hi,

Instead of giving select statement inside the loop try using it Outside the loop.

SELECT records from the database table which are relevant (Using FOR ALL ENTRIES).

SORT the internal table

Use a READ TABLE statement WITH BINARY SEARCH addition inside the loop.

Regards,

Lakshman.

Re: perfomance optimization code - Select Statement

Former Member — Thu, 27 May 2010 06:10:41 GMT

Hi,

Try the below code.

Data rp TYPE tys_TG_1.

data : begin of itab1,

include structure <DSO_TABLE>,

data : end of itab1.

SELECT costr ITEMID

FROM DSO_TABLE

INTO TABLE itab1

FOR ALL ENTRIES IN RESULT_PACKAGE

WHERE <Conditon>

LOOP AT RESULT_PACKAGE INTO rp.

read table itab1 with key itemid = rp-item.

MODIFY RESULT_PACKAGE.

ENDLOOP.

Regards

Nehruu

Re: perfomance optimization code - Select Statement

Former Member — Thu, 27 May 2010 06:12:59 GMT

1. Avoid select statement inside loop. It will hit the database multiple times, which is time consuming. Select all the cost entries from DSO_TABLE for RESULT_PACKAGE using FOR ALL ENTRIES. Then, inside the loop read the cost using READ with BINARY SEARCH after sorting the table outside the loop.

2. What is the size of RESULT_PACKAGE? If it's a huge table then use field symbols instead of MODIFY statement. It's faster.



Data rp TYPE _ty_s_TG_1.

field-symbols: <fs_rp> LIKE LINE OF RESULT_PACKAGE.

"Declare one internal table int_cost with item and cost

SELECT ITEM
             COST 
   FROM DSO_TABLE 
     INTO int_cost
       FOR ALL ENTRIES IN RESULT_PACKAGE
        WHERE ITEMID EQ RESULT_PACKAGE-ITEMID.

IF sy-subrc EQ 0.
  SORT int_cost BY itemid.
ENDIF.


LOOP AT RESULT_PACKAGE ASSIGNING <fs_rp>.

  READ TABLE int_cost INTO wa_cost WITH key itemid = <fs_rp>-itemid
                                                                       binary search.

  If sy-subrc EQ 0.
   <fs_rp>-cost = wa_cost-cost.
  ENDIF.

ENDLOOP.

Edited by: Satyajit on May 27, 2010 11:45 AM

Re: perfomance optimization code - Select Statement

matt — Thu, 27 May 2010 06:27:04 GMT


> 1. Avoid select statement inside loop. It will hit the database multiple times, which is time consuming. Select all the cost entries from DSO_TABLE for RESULT_PACKAGE using FOR ALL ENTRIES. Then, inside the loop read the cost using READ with BINARY SEARCH after sorting the table outside the loop.
> 
> 2. What is the size of  RESULT_PACKAGE? If it's a huge table then use field symbols instead of MODIFY statement. It's faster.
> 
> 
> 
> Data rp TYPE _ty_s_TG_1.
> 
> field-symbols: <fs_rp> LIKE LINE OF RESULT_PACKAGE.
> 
> "Declare one internal table int_cost with item and cost
> 
> SELECT ITEM
>              COST 
>    FROM DSO_TABLE 
>      INTO int_cost
>        FOR ALL ENTRIES IN RESULT_PACKAGE
>         WHERE ITEMID EQ RESULT_PACKAGE-ITEMID.
> 
> IF sy-subrc EQ 0.
>   SORT int_cost BY itemid.
> ENDIF.
> 
> 
> LOOP AT RESULT_PACKAGE ASSIGNING <fs_rp>.
> 
>   READ TABLE int_cost INTO wa_cost WITH key itemid = <fs_rp>-itemid
>                                                                        binary search.
> 
>   If sy-subrc EQ 0.
>    <fs_rp>-cost = wa_cost-cost.
>   ENDIF.
> 
> ENDLOOP.
> 
> 
> 
> 
> 
> 
> Edited by: Satyajit on May 27, 2010 11:45 AM

Use the above solution, but define int_cost as a HASHED table with UNIQUE KEY ITEMID, and use READ TABLE int_cost INTO wa_cost WITH TABLE KEY itemid = <fs_rp>-itemid.

I'm astonished that people are still using BINARY SEARCH. Please read the help on SORTED and HASHED tables, and start using them in your programs!

Re: perfomance optimization code - Select Statement

Former Member — Thu, 27 May 2010 07:44:53 GMT

Everything right what MATT said.

Two additional comments:

+ What does the huge table RESULT_PACKAGE come from? Maybe it is also selected .... then a Join could even be better.

+ And always check whether the table in the SELECT statement is table buffered, if so then leave it inside the loop! Remove SELECT from LOOPs is incorrect, you must remove DB accesses, but not table buffer accesses!

Siegfried

Re: perfomance optimization code - Select Statement

matt — Thu, 27 May 2010 08:24:37 GMT

Seigfried - if you see "RESULT_PACKAGE", "SOURCE_PACKAGE", "DATA_PACKAGE", then that implies that the ABAP is within an expert, start or end routine in a BW transformation/update routine. The volume of data will typically be between a few tens of thousands of records, to a few hundred thousand, being processed at one time.

matt

Re: perfomance optimization code - Select Statement

ravi_lanjewar — Thu, 27 May 2010 10:30:14 GMT

Hi,

I think your logic having identical read problem, It can avoid using following way

types: begin of ty_DSO_TABLE,
            itemid type DSO_TABLE-itemid,
            cost   type dso_table-cost,
            exist  type c,
           end of type ty_DSO_TABLE.
DATA: it_DSO_TABLE type hashed table of ty_DSO_TABLE with unique key itemid,
     wa_DSO_TABLE type ty_DSO_TABLE.
Fields-symbols:  <rp> TYPE _ty_s_TG_1.

LOOP AT RESULT_PACKAGE assigning <rp>.
  clear wa_DSO_TABLE .
  Read table IT_DSO_TABLE into wa_DSO_TABLE with table key itemid = <rp>-ITEMID.
  if sy-subrc ne 0.
      select single itemid cost into wa_DSO_TABLE from DSO_TABLE client specified
         where mandt = sy-mandt
              and itemid =  <rp>-ITEMID.
     if sy-subrc eq 0.
          wa_dso_table-exist = 'X'
     else.
         wa_dso_table-itemid.
     endif.
     insert wa_dso_table into table IT_DSO_TABLE .
 endif
if wa_dso_table-exist = 'X'.
<rp>-cost = wa_DSO_TABLE-cost. 
endif.
ENDLOOP.

Otherwise you can you satyajit logic for master data which does not have identical read and small noumber of records

Rgds

Ravi Lanjewar

Edited by: Matt on May 27, 2010 12:48 PM - add tags

Re: perfomance optimization code - Select Statement

Former Member — Thu, 27 May 2010 12:51:13 GMT

oh, still not finished ...

@Matt, thanks I rarely encountr BW, but now I understand the huge amount of data.

The last comment makes then also sense, however it is not the optimal solution.

The optimal solution is a combination of all recommendations. The FOR ALL ENTRIES will be extremely slow if there are many duplicates in the driver table. The last recommendation will still do a lot of SELECT SINGLE.

If itemid is not a numerical field


LOOP AT RESULT_PACKAGE ASSIGNING <fs_rp>.
  COLLECT <fs_rp-item into itab.
ENDLOOP

SELECT COST

FROM DSO_TABLE

INTO int_cost

FOR ALL ENTRIES IN itab

WHERE ITEMID EQ itab-ITEMID.

For a numrical field an APPEND, with SORT DELETE ADJACENT DUPLICATES is necessary.

The buffer in the LOOP is then not necessary. However, it the whole routine is executed several times, then

a global table for the buffer is better. Some SELECTs with FOR ALL ENTRIES can make sense plus a buffer access

inside the LOOP.

You see mass data processing is not so simple.

Re: perfomance optimization code - Select Statement

bhat_vaidya2 — Mon, 31 May 2010 09:06:57 GMT

Hi,

I have written the code but I keep getting this error E:Type "ITAB_SPC" is unknown

Data rp TYPE _ty_s_TG_1.
Fields-symbols:  <rp> TYPE _ty_s_TG_1.

data : begin of itab_spc,
       S_ITEMID TYPE /N/ADSO_DSOC00-/N/S_ITEMID,
       STRDCOST TYPE /N/ADSO_DSOC00-/N/S_STRDCOST,
       end of itab_spc.

DATA: it_spc_tab type hashed table of itab_spc with unique key s_itemid,
Data: wa_spc TYPE it_spc_tab.


SELECT /N/S_STRDCOST-/N/S_ITEMID FROM  /N/ADSO_DSOC00 INTO it_spc_tab
       FOR ALL ENTRIES IN RESULT_PACKAGE
        WHERE /N/S_ITEMID EQ rp-/N/S_ITEMID.
ENDSELECT.

LOOP AT RESULT_PACKAGE ASSIGNING <fs_rp>.

  READ TABLE it_spc_tab INTO wa_spc  WITH key S_ itemid = rp-/N/S_ITEMID
binary search.

if sy-subrc eq 0.
         <rp>-/N/S_STRDCOST = wa_spc_STRDCOST.    
 endif.

Edited by: Matt on May 31, 2010 12:35 PM - when posting code, select it, and then press the < > button.

Re: perfomance optimization code - Select Statement

matt — Mon, 31 May 2010 10:37:23 GMT

Define DATA as DATA, and TYPES as TYPES:

TYPES : begin of itab_spc,
       S_ITEMID TYPE /N/ADSO_DSOC00-/N/S_ITEMID,
       STRDCOST TYPE /N/ADSO_DSOC00-/N/S_STRDCOST,
       end of itab_spc.