topic Re: selects inside a loop or read inside a loop in Application Development and Automation Discussions

selects inside a loop or read inside a loop

kiran_k8 — Fri, 01 Feb 2008 05:39:51 GMT

Hi Folks,

I have a loop having 23 select statements in it.Will replacing some of these select statements with READ statement will enhance the performance?

Thanks,

K.Kiran.

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 05:43:15 GMT

read works like select single.

if you have some select single statements then replacing them by read within loop will improve performance.

regards,

kushagra

Re: selects inside a loop or read inside a loop

former_member156446 — Fri, 01 Feb 2008 05:43:34 GMT

yes it will if you use the read with binary search addition

you need to sort the table before read .... binary search.

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 05:44:36 GMT

Instead of 23 select statments preferably what you can do is to take the same number of variable and the move the variables to a new internal table where the performance of the report will increase.

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 05:45:07 GMT

Hi kiran,

surely , it will increase ur performance . Bcoz , u r avoiding database fetch each time in ur loop.

Do that and test ur speed.

Regrds

Karthik

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 05:50:21 GMT

Hi Kiran,

Read with Binary search will certainly improve the performance and the table has to be sorted by the corresponding field.

It actually splits the entries in to two halves and then starts searching if the required data is found in the first half then it does not go for the second half there by increasing the performance.

Regards,

Sai

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 05:55:00 GMT

It is definitely going to improve the performance of the report as the number of database hits will be reduced.

You can have different internal tables. You can use FOR ALL ENTRIES to fetch data and read those internal tables inside your loop.

Regards

Arindam

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 06:16:50 GMT

Read is like select single,

you need to sort the table first using key fields.

use 'key' to check the data to be read.

check sy-subrc after read.

Better YOU use SUB Quarries.

REGARDS,

GAURAV J.

Edited by: GAURAV on Feb 1, 2008 7:17 AM

Edited by: GAURAV on Feb 1, 2008 7:35 AM

Re: selects inside a loop or read inside a loop

former_member377374 — Fri, 01 Feb 2008 06:40:50 GMT

Hi,

Yes. You are true. Peformance will surely increase, if you write SELECT statement out side the loops and use only READ statement inside the loop.

1. While READ you can use Binary search Extension (if the table is already sorted).

2. If possible use for all entries clause in Select statement to fetch the data.

3. Use select single, if you know that only one record with the key avialable in database table.

Hope this should Help.

best Regards,

Sreedhar.

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 06:58:14 GMT

hello kiran,

better not use select statement in the looop u can sue read statement,

Regards

Santhosh

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 07:17:28 GMT

if you have a single record then use read statement, if you have multiple records then we have to use loop inside loop.

to use read statement we need to sort the internal table firest.

while using the read statement place binary searc at the end of read statement.

Re: selects inside a loop or read inside a loop

Former Member — Fri, 01 Feb 2008 07:21:52 GMT

READ should be used for interanal tables only

SELECT should be used for dabtabase tables.

Pl check from where u r retrieving the information and then replace it.

Madhavi

Re: selects inside a loop or read inside a loop

Former Member — Wed, 29 Apr 2009 10:47:32 GMT

Dear All,

I just want to know, how much time will be taken if we use a select statement in loop and read statement in loop.

Regards

Re: selects inside a loop or read inside a loop

Former Member — Wed, 29 Apr 2009 11:01:21 GMT

Read will retrieve the data from Application Server.

Select will retrieve the data from Database table.

The performance suggestion is, do not use order by clause in Select statement. Instead of that use SORT statement. This will do the sort being at Application Server.

Re: selects inside a loop or read inside a loop

Former Member — Sat, 25 Jul 2009 13:01:29 GMT

Hi All,

I m reading a table inside a loop. But its not giving the correct result. In every case sy-subrc after read is not equal to zero. Plz provide the solution.

LOOP AT gt_lfa1_y INTO gwa_lfa1_y.

READ TABLE gt_vendor INTO gwa_vendor WITH KEY

lifnr = gwa_lfa1_y-lifnr BINARY SEARCH.

If No Entries Found

IF sy-subrc EQ 0.

lv_sytabix = sy-tabix.

DELETE gt_lfa1_y index lv_sytabix.

ELSE.

If Plant Is not Empty

IF gwa_lfa1_y-werks NE space.

MOVE gwa_lfa1_y-lifnr TO gwa_vendor-lifnr.

MOVE gwa_lfa1_y-name1 TO gwa_vendor-name1.

MOVE gwa_lfa1_y-ktokk TO gwa_vendor-ktokk.

MOVE c_use TO gwa_vendor-source.

MOVE sy-datum TO gwa_vendor-erdat.

MOVE sy-uname TO gwa_vendor-ernam.

APPEND gwa_vendor TO gt_vendor.

ELSE.

If Plant Is Empty Then Read Table LFB1

READ TABLE gt_lfb1 INTO gwa_lfb1 WITH KEY

lifnr = gwa_lfa1_y-lifnr BINARY SEARCH.

IF sy-subrc EQ 0.

MOVE gwa_lfa1_y-lifnr TO gwa_vendor-lifnr.

MOVE gwa_lfa1_y-name1 TO gwa_vendor-name1.

MOVE gwa_lfa1_y-ktokk TO gwa_vendor-ktokk.

MOVE c_use TO gwa_vendor-source.

MOVE sy-datum TO gwa_vendor-erdat.

MOVE sy-uname TO gwa_vendor-ernam.

APPEND gwa_vendor TO gt_vendor.

ELSE.

If No Records Read From Table LFB1, Read Table LFM1

READ TABLE gt_lfm1 INTO gwa_lfm1 WITH KEY

lifnr = gwa_lfa1_y-lifnr BINARY SEARCH.

IF sy-subrc EQ 0.

MOVE gwa_lfa1_y-lifnr TO gwa_vendor-lifnr.

MOVE gwa_lfa1_y-name1 TO gwa_vendor-name1.

MOVE gwa_lfa1_y-ktokk TO gwa_vendor-ktokk.

MOVE c_use TO gwa_vendor-source.

MOVE sy-datum TO gwa_vendor-erdat.

MOVE sy-uname TO gwa_vendor-ernam.

APPEND gwa_vendor TO gt_vendor.

ENDIF.

CLEAR: gwa_lfa1_y,

gwa_lfb1,

gwa_lfm1,

gwa_vendor.

ENDLOOP.