topic Re: Difference between select and open cursor + fetch cursor in Application Development and Automation Discussions

Difference between select and open cursor + fetch cursor

Former Member — Thu, 16 Sep 2010 09:47:15 GMT

Hello,

I have the following two pieces of code. Could you please let me know the difference in performance / database time and load on the system when this code is executed.

Assume the table contains 100000 records.

case1:

select * from mara into corresponding fields of table <internal_table>.

case2:

open cursor g_cursor with hold for select * from mara package size 5000.

do.

fetch next cursor g_cursor appending corresponding fields of table <internal_table>.

enddo.

Regards,

Suraj

Re: Difference between select and open cursor + fetch cursor

Former Member — Thu, 16 Sep 2010 09:52:29 GMT

Hi,

Slight change in code.

case1:

select * from mara into corresponding fields of table <internal_table>.

case2:

open cursor g_cursor with hold for select * from mara package size 5000.

do.

fetch next cursor g_cursor appending corresponding fields of table <internal_table>.

if sy-subrc <> 0.

endif.

enddo.

Regards,

Suraj

Re: Difference between select and open cursor + fetch cursor

HermannGahm — Thu, 16 Sep 2010 09:54:25 GMT

> Could you please let me know the difference in performance / database time and load on the system when this code is executed.

why don't youmeasure it yourself with ST05 / ST04 ?

Re: Difference between select and open cursor + fetch cursor

Clemenss — Sun, 19 Sep 2010 12:52:51 GMT

Hi Suraj Shenoy,

I do not think there is much difference. The OPEN CURSOR .. fetch technique is rarely seen in any newly-developed programs. The PACKAGE SIZE can be used without cursor and fetch. It makes sense if you process each package directly sfter gettin it from the database.

Meanwhile, after the database optimizer has been improved a lot, you really do not have to care about it too much.

Performance can be gained by selecting required data from the database exactly once and by fetching only the fields you really need.

A table like MARA may have quite big size in the system. This makes it worth first to analyze what fields you really need and then put the fieldnames you need into an internal table, i.e. lt_required_fields type table of fieldname, then select and process blockwise with some experimenting on constant gc_mara_PACKAGE SIZE type sy-dbcnt value 10000.

SELECT (lt_required_fields) 
  INTO CORRESPONDING FIELDS OF TABLE lt_mara_fields
  FROM MARA
  PACKAGE SIZE gc_mara_PACKAGE SIZE
  WHERE ...
* process the entries

ENDSELECT.

I would not use the 'open cursor' .. 'fetch' technique any longer because there is no obvious advantage and for maintenance it may be not easy to understand. The FETCH statement decouples the INTO clause from the other clauses in the SELECT statement but I just do not see any advantage.

The documentation on [Using a Cursor to Read Data|http://help.sap.com/erp2005_ehp_04/helpdata/EN/fc/eb3b23358411d1829f0000e829fbfe/content.htm] notes that this programming method is quicker for nested databse access with outer and inner loop than using nested SELECT statements, since the cursor for the inner loop does not continually have to be reopened.

I think nested database accesses are no good idea at all.

Regards,

Clemens