topic Re: Help with timeout for select inside loop in Application Development and Automation Discussions

Help with timeout for select inside loop

Former Member — Sat, 17 Apr 2010 18:06:09 GMT

Hi everyone, im a little confused with this code. Can you help me to optimize it? Its giving Timeout dump.

Thanks so much, I´m new in abap and I tried hard, but its getting worse.

Any idea is welcomed.

LOOP AT it_lips.

SELECT SINGLE *

FROM vbak

WHERE vbeln = it_lips-vgbel.

IF sy-subrc <> 0 OR vbak-vbtyp <> 'C'.

CONTINUE.

ENDIF.

SELECT SINGLE *

FROM vbup

WHERE vbeln = it_lips-vgbel

AND posnr = it_lips-vgpos.

IF sy-subrc = 0 AND vbup-lfgsa = 'C'.

READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

IF it_likp-wadat < it_likp-wadat_ist.

it_data-statu = 'N'.

v_countn = v_countn + 1.

ELSE.

it_data-statu = 'S'.

v_counts = v_counts + 1.

ENDIF.

ELSE.

CONTINUE.

ENDIF.

it_data-vbeln = it_lips-vgbel.

it_data-posnr = it_lips-vgpos.

SELECT SINGLE matnr arktx kwmeng vrkme

INTO (it_data-matnr, it_data-arktx, it_data-mengs, it_data-vrkme)

FROM vbap

WHERE vbeln = it_lips-vgbel

AND posnr = it_lips-vgpos.

it_data-menge = it_data-mengs.

it_data-kunnr = vbak-kunnr.

SELECT SINGLE name1

INTO it_data-name1

FROM kna1

WHERE kunnr = it_data-kunnr.

it_data-edatu = it_likp-wadat.

it_data-wadat = it_likp-wadat_ist.

it_data-vkorg = it_likp-vkorg.

APPEND it_data.

CLEAR: it_data, vbak, it_vbep, vbap, kna1, it_lips, it_likp.

ENDLOOP.

Re: Help with timeout for select inside loop

asik_shameem — Sat, 17 Apr 2010 20:32:01 GMT

Hi,

It is not recommended to use Select inside loop. Use select with for all entries. Something like below.

SELECT SINGLE *
  FROM vbak
  INTO TABLE it_vbap
  FOR ALL ENTRIES IN it_lips
  WHERE vbeln = it_lips-vgbel.

LOOP AT it_lips.

READ TABLE it_vbak WITH KEY vbeln = it_lips-vgbel
IF sy-subrc EQ 0 OR it_vbak-vbtyp EQ 'C'.
  CONTINUE.
ENDIF.

ENDLOOP.

[http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp]

Re: Help with timeout for select inside loop

Former Member — Sat, 17 Apr 2010 22:54:14 GMT

Hi,


im a little confused with this code.

Yeah, we all are, but not only about the code. Who assigns performance optimization tasks to newbies instead of giving them first the opportunity to do a little reading? And if the newbies are out to optimize the code it makes me wonder what the skill level is of the people writing the code originally...

Please, there's tons of stuff of documentation out there. I know it's overwhelming, but I feel generous today (maybe because the sun was shining), so let me help start you off with a small list of recommended reading:

<li>Before posting, check out thread and </li>

<li>Try to read some previous postings in this forum (and I'd recommend following postings by by <em>Hermann Gahm</em> or <em>Siegfried Boes</em>, who always have nice detailed explanations).</li>

<li>Have a look at some of the blogs on ABAP performance (e.g. Siegfried Boes' blog on [ST05|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken];)</li>

<li>Most importantly, never underestimate the power of the ABAP online documentation, e.g. check out [SQL performance|http://help.sap.com/abapdocu_70/en/ABENOPEN_SQL_PERFO.htm] and for example the [FOR ALL ENTRIES|http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_ITAB.htm] reference as pointed out by the previous poster above (this will immediately give you lots of ideas like using joins, avoiding reading all columns, etc.)</li>

</ul>

Cheers, harald

Re: Help with timeout for select inside loop

Former Member — Sun, 18 Apr 2010 02:01:45 GMT

Thanks for the helping code and for the reading.

And Sorry.. I´m trying to do my best and your right...

Bye !

JCO

Re: Help with timeout for select inside loop

Former Member — Sun, 18 Apr 2010 20:21:28 GMT

The issue of SELECTing within a LOOP is much overblown. All of the SELECTs are SELECT SINGLE using the fully qualified primary key. Using a JOIN or FOR ALL ENTRIES might speed this up a bit, but dollars to doughnuts, the problem is here:

READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

If it_likp is a standard table, you will be reading half of all of the entries, on average, every time you do the read. If you don't want to change it to a sorted or hashed table, SORT it_likp by vbeln before going into the loop and use the addition BINARY SEARCH on the READ statement.

Rob

Edited by: Rob Burbank on Apr 18, 2010 9:34 PM

Edited by: Rob Burbank on Apr 19, 2010 10:15 AM