performance issue

[Former Member]

Hi,

In my program I have select statement inside loop-------endloop.How can I avoid this?

Regards,

Hema

[Simha_]

Hi,

if u r requirement is as ,

Loop at ta_bkpf.

select single

from bsid

into table wa_bsid

where belnr = ta_bkpf.

...

....

endloop.

In this case:

Suppose u have 1000 records in TA_BKPF

select query for BSID table will trigger 1000 times

and will connect database system 1000 times.

so it may take time .

So instead of using above code u can use .

select

from BSID

into table TA_BSID

for all entries in TA_BKPF

where belnr = TA_BKPF-belnr.

it will connect database system only one time,

and it will work like case-1 also will take less time.

but u have take care that if TA_BKPF is empty then

for all entries will fail and select query will fetch all data

from BSID to TA_BSID.

so check TA_BKPF[] is initail or not.

Cheers,

SImha.

[Former Member]

before other people comment differently,

there is only a performance issue if a runtime measurement tells you that the statement is slow.

So run the SQL trace first:

SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Read this carefully.

Is this statement the slowest in the list? How long does it need? Less than 10ms or more?

Maybe the table is buffered and the select is not on the list at all.

Maybe there are some other statement, which are much slower!

In general, you can move the select out of a loop, if you use

FOR ALL ENTRIES:

+ but never forget to check whether the table is empty, otherwise you FAE select will select the whole table, which will be much slower.

+ sort the result table after the select, and use read binary search inside the loop. If you use standard reads, your performance will again be slower than before.

Run the SQL trace and the STAD again after the change and check how much your program did improve.

Siegfried

[valter_oliveira]

Hello.

This varies from situation to situation. But to avoid select inside loop, you normally have two options.

1 - Use FOR ALL ENTRIES statement

2 - If it's possible, make only one bigger and insert into an internal table, and then, when looping you r table, read the second table.

Regards.

Valter Oliveira.

[Former Member]

Hi,

If u are looping at internal table say itab and using select inside loop,then use :

select <fiellds u want to select>

from <table name>

into table<internal table having fields u want to select>

for all entreis in itab

where <condition>.

Try this code .

Reward points if helpful.

Regards,

Mukul