topic Re: Selects within loops in Application Development and Automation Discussions

Selects within loops

Former Member — Mon, 21 May 2007 05:23:48 GMT

Hi everyone!

I have been told that having selects within loops is a bad practice. Can anyone please tell me how having selects within loops may have a negative impact on performance?

I have a while loops in my program and within that while loop, I am running a select query on particular internal table. Is there any particular reason why I should be changing my code?

Thanks in advance,

Divyaman

Re: Selects within loops

Former Member — Mon, 21 May 2007 05:46:59 GMT

Hi,

when ever a SELECT statement has to be executed, it establishes a connection with the database server,

hence if you have select within a loop, it has to connect to database server so many times . instead if you write your select out of the loop there will be connection to data base server only once which will improve the performance.

Regards,

Raghavendra

Re: Selects within loops

Former Member — Mon, 21 May 2007 05:59:06 GMT

Thanks Raghavendra for your response!

But, in my case, I am running the select query on an internal table. So, there should not be any performance degradation here because connection to the database is not required in each iteration of the loop. Correct me if I am wrong here!

Thanks,

Divyaman

Re: Selects within loops

Former Member — Mon, 21 May 2007 07:06:35 GMT

hi divyaman,

that's not bad code to write which u have written,

still u can avoid that select statement also by writing as follows,

if not <itab> is initial.

select * from <table> into <itab1> for all entries in <itab> where <field> eq <itab-field>.

endif.

this statement can still improve your performance.

plz check once,,,

regards..

seshu.

Re: Selects within loops

Former Member — Mon, 21 May 2007 08:18:28 GMT

Hi Divyaman,

why do you want to write a select query on an internal table. instead read the required records for further processing, if not...use nested loops.

the performance depends on the internal table size. you can check the loop's performance using Get runtime command.

regards,

madhu

Re: Selects within loops

Former Member — Mon, 21 May 2007 08:38:44 GMT

Please go throught the SM30 There in the trips&trciks you can see the difference and performance of the all types of select query ..

place your program name or t.code and active sql trace , then see it inthe exact ...

do the same in ST05 there you can see the SQL Statement 'SELECT' how much time it take ...........

Regards points if it is usefull

Girish

Re: Selects within loops

Former Member — Mon, 21 May 2007 08:55:57 GMT

Hi,

i am giving an e.g. say you have and itab with 100000 records and field matnr and u want material text i.e. maktx for that then there are two possibilities:

1. You are using loop:

loop at itab.

select single maktx into itab-maktx where....

modify itab transporting maktx.

endloop.

Here, u will find select statement is exectured once for each record. this is is less performing codes as 1. each time is will take time to access db table and 2. search field from db table. so performance will be very less.

but, if u are using for all entries then codes will look like this:

select matnr maktx into itab_matnr from makt FOR ALL ENTRIES IN itab where....
"Now update itab-maktx using itab_matnr-maktx

This will access data table only once and will be fetch all records so it will reduce overheades as in above e.g. like table access time etc. so this second option is better may be this will reduce load on database but load will be shared by ABAP. bcoz itab enteris will be processed by using loop.

if there are very fiew records then u will not notice too much diffierence but for huge data second method is far better than first.

Jogdand M B

Re: Selects within loops

Former Member — Wed, 23 May 2007 05:38:39 GMT

Thanks guys for your response! I've rewarded points to helpful answers.

Cheers!

Re: Selects within loops

former_member196280 — Wed, 23 May 2007 09:28:24 GMT

Hi,

Instead of using select statments inside loops you can use it like this

SELECT * from <table> INTO <iternal table> for all entries in <itab>.

This will imporove the performance.

Reward points for all useful answers.

Regards,

SaiRam