topic Re: Timeout error while fetching records from CE1EGOC table in Application Development and Automation Discussions

Timeout error while fetching records from CE1EGOC table

Former Member — Mon, 06 Jul 2015 15:44:44 GMT

Hello All,

I am getting a time out dump when I am trying to fetch records from CE1EGOC table in a custom report between some material range. I checked in SE16N for the total number of records and it has 3 lakhs approximately. I tried to use index also but that doesn't help. And I also tried to use concept of Fetch Cursor but that also failed.

DATA: lvc_cursor TYPE cursor.

LOOP AT gt_mat INTO gs_mat_temp.

OPEN CURSOR lvc_cursor

FOR SELECT paledger

vrgar

versi

perio

paobjnr

pasubnr

belnr

posnr

gjahr

artnr

werks

absmg_me

absmg

FROM ce1egoc

BYPASSING BUFFER

WHERE paledger EQ gv_paledger1

AND vrgar EQ gv_vrgar "record type 'F'

AND perio LE gv_perio

AND gjahr EQ pa_year

AND artnr EQ gs_mat_temp-matnr

%_HINTS ORACLE 'INDEX("CE1EGOC""CE1EGOC~5")'.

WHILE lvc_cursor IS NOT INITIAL.

FETCH NEXT CURSOR lvc_cursor INTO TABLE lt_ce1egoc_cur PACKAGE SIZE 20000.

DESCRIBE TABLE lt_ce1egoc_cur LINES lv_lines.

IF sy-subrc EQ 0.

ADD lv_lines TO lv_tot_lines.

APPEND LINES OF lt_ce1egoc_cur TO lt_ce1egoc.

ELSE.

CLOSE CURSOR lvc_cursor.

ENDIF.

ENDWHILE.

ENDLOOP.

Could you please help to fix this issue.

Thanks and Regards,

Rohit

Re: Timeout error while fetching records from CE1EGOC table

Former Member — Mon, 06 Jul 2015 16:51:28 GMT

Hi Rohit,

To set a breakpoint at the 'FETCH NEXT', the first time into WHILE, just select data with 20000 package size, it will directly go to dump? If not, how many times it takes?

regards,

Archer

Re: Timeout error while fetching records from CE1EGOC table

kiran_k8 — Tue, 07 Jul 2015 06:47:42 GMT

Rohit,

Select statement within a loop is not recommended.For better understanding check how many records are there in the itab that you are looping.If the itab is having 10 records then your select statement will get trigerred for 10 times which results in multiple hits to the database.Explore the possibilities to get the data outside the loop and process it further as per the requirements.

Not so sure if forcing the INDEX using HINTS is advisable or not.

K.Kiran.

Re: Timeout error while fetching records from CE1EGOC table

Former Member — Tue, 07 Jul 2015 07:09:29 GMT

Hi Archer,

If you do it on debugging, it works perfectly. It took almost 30 seconds to fetch 20000 records.

If I do F8 and execute it for remaining materials it is going for dump.

Best Regards,

Rohit