10-16-2009 12:59 PM
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
Edited by: Dagny on Oct 16, 2009 1:59 PM
10-17-2009 8:46 PM
Well, I think the perform inside the SELECT will cause a COMMIT and this will cause a dump.
Rob
10-16-2009 2:05 PM
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
10-16-2009 2:33 PM
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
10-17-2009 8:23 AM
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.
10-17-2009 8:46 PM
Well, I think the perform inside the SELECT will cause a COMMIT and this will cause a dump.
Rob
10-18-2009 3:50 PM
> 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