selects inside a loop or read inside a loop

[kiran_k8]

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.

[Former Member]

read works like select single.

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

regards,

kushagra

[former_member156446]

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

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[former_member377374]

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.

[Former Member]

hello kiran,

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

Regards

Santhosh

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

ENDIF.

ENDIF.

ENDIF.

CLEAR: gwa_lfa1_y,

gwa_lfb1,

gwa_lfm1,

gwa_vendor.

ENDLOOP.