topic help me in Application Development and Automation Discussions

help me

Former Member — Fri, 18 May 2007 10:18:00 GMT

Hi experts,

If we write select query in loop the performance should be decrased.

For this to improve performance i need some examples. Please give some suitable examples to improve performance.

Please help me in this.

Thanks

Re: help me

Former Member — Fri, 18 May 2007 10:20:59 GMT

<b>Use for all entry instead.</b>

Ex:


* select material from mara
  SELECT matnr FROM mara INTO TABLE p_mara_table
               WHERE mtart IN s_mat.

* select plant and material combination from marc
  CHECK p_mara_table IS NOT INITIAL.

  SORT p_mara_table BY matnr.

  SELECT matnr werks
    FROM marc INTO TABLE p_marc_table
    FOR ALL ENTRIES IN p_mara_table
    WHERE matnr = p_mara_table-matnr
      AND werks IN s_plant.

Re: help me

former_member196299 — Fri, 18 May 2007 10:21:02 GMT

hi ,

you can try like this ,

Keep all the record in an itab , ( what you are looping ).

In your select statement , use FOR ALL ENTRIES .

This will increase the performance .

Regards,

Ranjita

Re: help me

Former Member — Fri, 18 May 2007 10:23:01 GMT

Hi,

You can have a look at the menu option in se38

Environment->examples->performance examples

There very good samples of codes to enhance the performance .

for your question, use FOR ALL ENTRIES, along with your Select statement

for making it performant.

Hope this helps!

Poornima

Re: help me

former_member673464 — Fri, 18 May 2007 10:24:24 GMT

hi..

Try using the key word 'For all entries' for extracting data for the data in inernal table. This will improve your performance.

regards,

veeresh

Re: help me

Former Member — Fri, 18 May 2007 10:24:53 GMT

Hi,

Go to SE30 (Runtime Analysis) and choose utilities -> Tips & Tricks. It will be helpful to u.

Reward if useful.

Regards,

U. Uma

Re: help me

sharadendu_agrawal — Fri, 18 May 2007 10:26:08 GMT

Select all the entries from the table in one go before the loop.

Now do the looping and and read the corresponding entries that u get in the header of the loop. Check for the sy-subrc value and do the further processing based on that.

This will definitely improve the performance.

Reward if helpful.

Cheers,

Sharadendu

Re: help me

Former Member — Fri, 18 May 2007 10:26:35 GMT

dont use select query in Loop.

for example.

1) if u want to use the contents of itab1 to select the data and fill in Itab2.

this is wrong way,

loop at iatb1.

select single * into itab2

where ....... = itab1-........

endloop.

This is right way,

select * from <table name> into table itab2

for all entries in itab1

where .......... and

........= itab1-...........

please reward some Points if useful

regards,

Padmakar

Re: help me

Former Member — Fri, 18 May 2007 10:30:03 GMT

Hi experts,

Like this i got this issue to solve but i am new to this area i am getting confusion when seeing it.

Can you please solve this issue which i am sending you now.

I need performance improvement in this.

***************************************************

FORM getdata.

SELECT v_{vbeln v}vdatu v_{kunnr v}erdat

p_{posnr p}matnr p~kwmeng

INTO CORRESPONDING FIELDS OF TABLE it_tab1

FROM vbak AS v

INNER JOIN vbap AS p

ON p_{vbeln = v}vbeln

WHERE v~vbeln IN s_vbeln

AND v~kunnr IN s_kunnr

AND v~erdat IN s_erdat

AND v~vbtyp eq 'C'

AND v~vkorg IN s_vkorg

AND p~matnr IN s_matnr

AND p~vstel EQ s_vstel.

IF sy-subrc <> 0.

MESSAGE i999 WITH text-003.

STOP.

ENDIF.

LOOP AT it_tab1.

SELECT single bstkd INTO it_tab1-bstkd

FROM vbkd

WHERE vbeln EQ it_tab1-vbeln.

select single name1 into it_tab1-name1 from kna1 where kunnr EQ

it_tab1-kunnr.

select single bismt into it_tab1-bismt from mara where matnr EQ

it_tab1-matnr.

if it_tab1-bismt EQ ' '.

it_tab1-bismt = it_tab1-matnr.

endif.

and posnr eq it_tab1-posnr.

----

Getting Customer purchase order number -

SELECT single gbsta INTO wa1-gbsta

FROM vbup

WHERE vbeln = it_tab1-vbeln

AND posnr = it_tab1-posnr.

Getting Referenced quantity--

CASE wa1-gbsta.

WHEN 'A'.

MOVE it_tab1-kwmeng TO it_tab1-rfmng.

MODIFY it_tab1.

CLEAR it_tab1.

CLEAR wa1.

WHEN 'B'.

PERFORM ship_qty CHANGING l_csqty.

l_csrfmng = it_tab1-kwmeng - l_csqty.

IF l_csrfmng <> 0.

MOVE l_csrfmng TO it_tab1-rfmng.

MODIFY it_tab1.

CLEAR it_tab1.

CLEAR wa1.

ELSE.

DELETE it_tab1.

CLEAR it_tab1.

CLEAR wa1.

ENDIF.

WHEN OTHERS.

DELETE it_tab1.

CLEAR it_tab1.

CLEAR wa1.

ENDCASE.

ENDLOOP.

ENDFORM. "getdata

*************************************************

For this i need to improve performance . Please help me.

Thanks

Re: help me

Former Member — Fri, 18 May 2007 10:34:57 GMT

Hi..

If you want to select a bulk amount of data inside the loop, you can do one thing.First select all the data from the particular table ,store it in an internal table and inside the loop always read this loop and copy the actual data into other internal table...See the example below....

select-options: s_matnr for mara-matnr.

select matnr werks lgort from mara into it_mara where matnr in s_matnr.

loop at it_mara.

read table it_mara <specify all the conditions>

move-corresponding it_mara to it_mara1.

endloop.

if there is more than one entries..

loop at it_mara where <put cond>.

move-corresponding it_mara to it_mara1.

append it_mara1.

endloop.

Also you can use tcodes se30 st05 and st04 to check your perfomance,.........

Reward all helpfull answers.......

Re: help me

sharadendu_agrawal — Fri, 18 May 2007 10:39:09 GMT

Take all the selct queries outside the loop. After getting all the entries start with

the looping. Read the entries from the corresponding internal table using the header of the

loop and then u can move ahead as required or process as required.

Reward if helpful

Cheers,

Sharadendu

Re: help me

Former Member — Fri, 18 May 2007 10:43:08 GMT

Hi..

First of all replace your Inner join witha for all entries..Hope you know this...The you r using so many select single inside your loop..This will decrease your perfomance a lot....Replace all this select single inside this loop...See the logic for this...

select bstkd from vbkd into table it_tab2 for all entries in it_tab1 where <put the key>.

LOOP AT it_tab1.

here replace youe select single by a read stme like this

read table it_tab2 with key bstkd = it_tab1-bstkd <like this put all the keys>

****8like this replace all of your select here.............

select single name1 into it_tab1-name1 from kna1 where kunnr EQ

it_tab1-kunnr.

endloop.

Hope this will solve this pblm..In case of any error revert back..

Reward all helpfull answers..........