Help with timeout for select inside loop

[Former Member]

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.

[asik_shameem]

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]

[Former Member]

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:

<ul style="list-style:square">

<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

[Former Member]

Thanks for the helping code and for the reading.

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

Bye !

JCO

[Former Member]

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