topic Re: Select Single Performance...... in Application Development and Automation Discussions

Select Single Performance......

Former Member — Mon, 31 Dec 2007 12:43:28 GMT

Hi Experts,

I have selected from 4 different tables.

How to inprove performance for following Loop.

What i have done is right and i should do in another way?

LOOP AT itab.

SELECT SINGLE name1 INTO itab-name1

FROM kna1 WHERE kunnr = itab-kunnr.

SELECT SINGLE ort01 INTO itab-ort01

FROM kna1 WHERE kunnr = itab-kunnr.

SELECT SINGLE bstkd INTO itab-bstkd

FROM vbkd WHERE vbeln = itab-vbeln.

SELECT SINGLE edatu INTO itab-edatu

FROM vbep WHERE vbeln = itab-vbeln.

ENDLOOP.

Yusuf.

Re: Select Single Performance......

stefan_koehler — Mon, 31 Dec 2007 15:43:07 GMT

Hello Yusuf,

if possible you should not perform selects in a loop (with columns of the looped table).

Every time you have to parse (pinning), execute and fetch the data (maybe you are reading the same blocks in the database).

If the table itab has not many entries you should use the "FOR ALL ENTRIES" option.

http://help.sap.com/saphelp_470/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

> You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

It depends on your underlying database and on your kernel default / custom settings how the statement is executed on the database.

How you can influence the counter (after how many entries in itab a new statement is executed) is described in sapnote #48230

Regards

Stefan

Re: Select Single Performance......

Former Member — Mon, 31 Dec 2007 16:04:52 GMT

Try like this. syntax may be wrong. but just overview.

if not itab[] is initial.

select name1 ort1 from kna1 into table i_kna1

for all entries in itab

where kunnr = itab-kunnr.

select bstkd from vbkd into table i_vbkd

for all entries in itab

where vbeln = itab-vbeln.

select edatu from vbep into table i_vbep

for all entries in itab

where vbeln = itab-vbeln.

endif.

loop at itab.

read table i_kna1 with key kunnr = itab-kunnr.

read table i_vbkd with key vbeln = itab-vbeln.

read table i_vbep with key vbeln = itab-vbeln.

endloop.

-Pavan

Re: Select Single Performance......

Former Member — Tue, 01 Jan 2008 05:42:03 GMT

Hi Pavan,

Thanks, I used FOR ALL ENTRIES and it works very good and performance also increases.

Rewarded.

Yusuf