*** performance issue, advice plz...

[Former Member]

Hi Experts,

I have a z-table1 in which I have some fileds, I have wrote the select query to fetch the data and got data too, now from this internal table I have to pass a field called SERIAL_NUM to another z-table2, to fetch the CO documents/items.

But in the z-table2 here SERIAL_NUM field is not a key field nor it is having the index, so it is taking time in minutes even to fetch CO doc number/items for 10 serial numbers also.

Here the z-table2 is having abt 42 lacs of records, I guess even to search the doc numbers for this 10 SERIAL_NUM it is looking each time 42 lacs records as a result it is taking time in mins.

Sample code as follows-

LOOP AT it_zeps06 INTO wa_zeps06.

SELECT SINGLE

belnr

buzei

billcode

vbeln

INTO (ws_belnr, ws_buzei, ws_billcode, ws_vbeln)

FROM zeps03

WHERE serialnum = wa_zeps06-serial_no.

Endloop.

Plese advice me how to overcome this performance issue? Is it works if I create index on serial_num filed in z-table2? Or is there any other way?

Appreciate you response.

Regards.

DC

[Former Member]

Hi,

The first issue is that you are looping over a select statement. What you could do is create another internal table with same fields as the select statement above and then use the table it_zeps06 with FOR ALL ENTRIES options and collect the serial numbers.

This should take much less time than the others. Since you are looping over a select statement, the trip from AServer to DBServer is expensive.

Thanks...

Preethanm S

[Former Member]

hi,

chane the code as follows:

if not it_zeps06[] is initial.

SELECT SINGLE

belnr

buzei

billcode

vbeln

INTO table itab1

FROM zeps03

for all entries in it_zeps06

WHERE serialnum = it_zeps06-serial_no.

endif.

itab1 must have the fieds belnr

buzei

billcode

vbeln.

regards,

Navneeth K.

[alpesh_saparia3]

Index will improve the performance. Create the index on the fields which you are putting in the where condition

-Alpesh