topic Re: OPEN CURSOR method to fetch data in Application Development and Automation Discussions

OPEN CURSOR method to fetch data

Former Member — Fri, 16 Oct 2009 11:59:04 GMT

Hi,

Is the following OPEN CURSOR method to fetch data faster than the normal SELECT statement ?

OPEN CURSOR [WITH HOLD] <c> FOR SELECT <result>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

I have read about this in the following URL, it is not mentioned about its adavantages over normal SELECT.

[http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb3b23358411d1829f0000e829fbfe/content.htm]

Can anyone please let me know if OPEN CURSOR method is faster than normal SELECT and would improve the performance ?

Thanks in Advance.

Edited by: Dagny on Oct 16, 2009 1:59 PM

Re: OPEN CURSOR method to fetch data

Former Member — Fri, 16 Oct 2009 13:05:24 GMT

You could easily check this yourself by using ST05 to see if it is faster than a normal SELECT; however, I doubt that you would. Cursors are generally used when the same table must be accessed while another SELECT already has been used against it.

Rob

Re: OPEN CURSOR method to fetch data

Former Member — Fri, 16 Oct 2009 13:33:17 GMT

no it is not.

It is not used to improve performance for simple statements, but for other purposes.

Mainly the WITH HOLD possiblity is interesting if a lot of data should be changed, then after several a database commit can be done, which does not kill the cursor. The processing can continue with the rest of data. Otherwise it could be difficult to figure out what was changed and what not.

Nested processing is also possible, but I would say not necessary anymore. The join is in recent releases so unproblematic, that hand-made joins are not necessary.

Siegfried

Re: OPEN CURSOR method to fetch data

Former Member — Sat, 17 Oct 2009 07:23:12 GMT

Thank you Siegfried and Rob for the responses. I do not have to make any changes in the database tables. I just need to fetch the data and using this data , fetch data from another database table.

This is the SELECT I am planning to write:

SELECT equnr

FROM equi

PACKAGE SIZE 10000

APPENDING TABLE i_equi

WHERE equnr IN s_equnr

AND erdat IN s_erdat.

DESCRIBE TABLE i_equi[] LINES vn_lines.

IF vn_lines = cn_lines.

PERFORM export_data_equi TABLES i_equi.

REFRESH i_equi[].

CLEAR vn_lines.

ENDIF.

ENDSELECT.

So, I get the data from EQUI, each time 10000 records upto 1500000 records(cn_lines), then using those 1500000 records, in PERFORM export_data_equi, I am getting records from AUSP table.

SELECT objek atinn atzhl mafid klart adzhl

FROM ausp

PACKAGE SIZE 10000

APPENDING TABLE i_ausp

FOR ALL ENTRIES IN i_equi

WHERE objek = i_equi-equnr

and atinn in r_atinn_cf2

and atzhl in r_atzhl

and mafid in r_mafid

and klart in r_klart

and adzhl in r_adzhl.

DESCRIBE TABLE i_ausp[] LINES vn_lines.

IF vn_lines = cn_lines.

PERFORM export_data TABLES i_ausp.

REFRESH i_ausp[].

CLEAR vn_lines.

ENDIF.

ENDSELECT.

In PERFORM export_data, I am exporting data as dataclusters into INDX table.

EXPORT p_ausp TO DATABASE indx(au) ID index_ausp.

I will import this data in another program.

All this I am doing, as there is huge data and normal select is taking lots of time and the client wants it to happen faster.

As there are'nt any people with this knowledge of OPEN CURSOR or Exporting / Importing data from data clusters, I request you to please let me know if my above approch is right. Now that you guys have said that OPEN CURSOR is not necessary.

Thanks a lot.

Re: OPEN CURSOR method to fetch data

Former Member — Sat, 17 Oct 2009 19:46:50 GMT

Well, I think the perform inside the SELECT will cause a COMMIT and this will cause a dump.

Rob

Re: OPEN CURSOR method to fetch data

Former Member — Sun, 18 Oct 2009 14:50:27 GMT

> as there is huge data and normal select is taking lots of time and the client wants it to happen faster.

what is lots of time and what is faster?

Up to 1.500.000 records is a lot, maybe the expectations are wrong. Otherwise, how often is it done? Once?

The export to database must also be committed of not? Then an open CURSOR WITH HOLD could help.

And the DESCRIBE is not necessary, it is even incorrect. At the end you get a rest which you don't know in advance.

So read with package size and process that package, refresh and SELECT the next package.

Siegfried