topic Re: *** performance issue, advice plz... in Application Development and Automation Discussions

*** performance issue, advice plz...

Former Member — Tue, 28 Aug 2007 09:33:38 GMT

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.

Re: *** performance issue, advice plz...

Former Member — Tue, 28 Aug 2007 09:39:30 GMT

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

Re: *** performance issue, advice plz...

Former Member — Tue, 28 Aug 2007 09:40:44 GMT

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.

Re: *** performance issue, advice plz...

alpesh_saparia3 — Tue, 28 Aug 2007 09:46:59 GMT

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

-Alpesh