Timeout error while fetching records from CE1EGOC table

[Former Member]

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

[Former Member]

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

[Former Member]

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

[kiran_k8]

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.