topic Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT in Application Development and Automation Discussions

Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Thu, 08 Jan 2009 05:25:52 GMT

Hi,

Can some one please elaborate on the difference between the above ways of getting data. I have gone through the documentation, but it does not directly mention anything about performance differences between them, also please cite the link which can give more detailed info about this conceptually. Most of them show the syntax diagrams only and ways to use the cursor

Thanks

ChK

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Thu, 08 Jan 2009 05:31:48 GMT

If documentation provided is not sufficient, just put a search using proper subject line on SCN.

You will get lots of threads.

Check this also [www.help.sap.com]

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Thu, 08 Jan 2009 05:36:36 GMT

This message was moderated.

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Thu, 08 Jan 2009 07:02:28 GMT

This message was moderated.

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Thu, 08 Jan 2009 22:16:26 GMT

At database level, I think they are the same. But at ABAP level, there are some differences (just compare by yourself, and it may depend on the abap release you use) that the developer would prefer to use one, or the other.

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Fri, 09 Jan 2009 02:08:02 GMT

hi,

Refer to this link..

[Open Cursor Vs Open Sql (SELECT)|]

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Sat, 10 Jan 2009 10:26:05 GMT

Avinash, this thread doesn't discuss about SELECT...ENDSELECT

ChK, I have found nothing in SDN forums, blogs and wikis. It would be worth everybody participates in this thread to give some valuable feedback.

In google, I found one of the reasons to use OPEN CURSOR over SELECT...ENDSELECT and I agree (sorry to copy but I wanted to highlight it out) :


> SLKorolev wrote on Fri Jun 08, 2007 9:14 am [in a sapfans thread|http://www.sapfans.com/forums/viewtopic.php?t=231963]  :
>
> Suppose you need to extract some table content by portions, say in BW-R/3 interface which works technically by issuing several RFC calls (until the whole result set is tranfered). Certainly you can not use SELECT ENDSELECT here as it will open the same select every time the FM called. So, in that case you can declare OPEN CURSOR with HOLD and transfer every time next portion of query result set as the current cursor position will be saved between FM calls.

Moreover, I have extracted (below) syntax diagrams for both constructs from [abap 7.1 documentation|http://help.sap.com/abapdocu/en/ABENABAP.htm], we see that there are very few differences.


OPEN CURSOR [WITH HOLD] dbcur FOR 
  SELECT ... 
         FROM ... 
         [[FOR ALL ENTRIES IN itab]
           WHERE sql_cond] 
         [GROUP BY ...]
         [HAVING sql_cond] 
         [ORDER BY ...]. 

* Here you may repeat several FETCH
FETCH NEXT CURSOR dbcur 
    { { INTO { {[CORRESPONDING FIELDS OF] wa} | (dobj1, dobj2, ...) } } 
    | { INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab 
                       [PACKAGE SIZE n] } }.

CLOSE CURSOR dbcur.

SELECT ...
       FROM ...
    { { INTO { {[CORRESPONDING FIELDS OF] wa} | (dobj1, dobj2, ...) } } 
    | { INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab 
                       [PACKAGE SIZE n] } }
         [[FOR ALL ENTRIES IN itab]
           WHERE sql_cond] 
         [GROUP BY ...]
         [HAVING sql_cond] 
         [ORDER BY ...]. 
  ... 
ENDSELECT.

I think the only differences are summarized here (excerpts from abap 7.1 documentation):

\[WITH HOLD...\] Leaves the cursor open in the case of an explicitly triggered database commit or database rollback

Within a SELECT-loop you cannot execute any statements that lead to a database commit, or database rollback and consequently cause the corresponding database cursor to close

A database cursor is opened implicitly to process a SELECT-loop, and is closed again when the loop is ended. You can end the loop using the statements from section leave loops

Also, as I said in previous post, I still think that in older releases, there were some differences (if I remember well, package size was not available in one of the 2 constructs), and maybe one of the two was created after the other.

Thus, you just have more functions with OPEN CURSOR...FETCH...CLOSE than with SELECT...ENDSELECT

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Mon, 12 Jan 2009 05:35:21 GMT

Hi Sandra,

Thank you for compiling all the information into one piece. I think, we need to get more information on this to understand this better. I have analysed that, the Cursor performs better compared to the SELECT...ENDSELECT and also, SELECT statement if used normally.

Regards

ChK

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

SujeetMishra — Mon, 12 Jan 2009 05:58:45 GMT

Hello Harsha,

Using of OPEN CURSOR..FETCH CURSOR..CLOSE is much better for performance than using of SELECT.....ENDSELECT.

and for details just goto SE38 and write these keywords than press F1.

I think it will clear your Doubt.

Regards,

Sujeet.

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Mon, 12 Jan 2009 10:41:27 GMT

Hi Sujeet,

could you indicate the url to this doc because I couldn't find this information. What I just found are the performance notes here (similar to F1 in SE38 editor) : http://help.sap.com/abapdocu/en/ABENOPEN_SQL_PERFO.htm

There is nothing in cursor documentations : [OPEN CURSOR|http://help.sap.com/abapdocu/en/ABAPOPEN_CURSOR.htm], [FETCH|http://help.sap.com/abapdocu/en/ABAPFETCH.htm], [CLOSE CURSOR|http://help.sap.com/abapdocu/en/ABAPCLOSE_CURSOR.htm].

Neither in [SELECT|http://help.sap.com/abapdocu/en/ABAPSELECT.htm] and [ENDSELECT|http://help.sap.com/abapdocu/en/ABAPENDSELECT.htm]

Anyway, it made me think that I could test performance by myself:

DATA curs TYPE cursor.
DATA bseg TYPE bseg.
OPEN CURSOR curs FOR SELECT * FROM bseg.
DO.
FETCH NEXT CURSOR curs INTO bseg.
IF sy-subrc <> 0.
  exit.
endif.
ENDDO.
CLOSE CURSOR curs.

compared to

DATA bseg TYPE bseg.
SELECT * FROM bseg INTO bseg.
ENDSELECT.

BSEG contains around 40000 lines.

It gives the following results (I ran it many times to be sure, it doesn't change) :

with cursor:

2.490.985 microseconds

with select...endselect :

2.490.317 microseconds

I also looked at the SQL trace, the statements are absolutely identical !

Hence, this prooves that they are technically equivalent (or did I miss something?)

Sandra

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Subhankar — Mon, 12 Jan 2009 10:49:22 GMT

Hi ....

We use OPEN CURSOR..FETCH CURSOR..CLOSE statements to retrieve data from different server (NON SAP table) or a heavy table like POS (Point of sale) table.

Also when we use these statement we use NATIVE SQL.

SELECT .... ENDSELECT can not be used to get data a table which is in the different server.

Thanks

Subhankar

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Mon, 12 Jan 2009 10:58:58 GMT

Hi Sandra,

I have a completely different results when I ran the code using CURSOR and SELECT statements for the following code.

This is the code extract that demonstrates the time taken to fetch records from the table VBAK.

GET RUN TIME FIELD F1.

SELECT VBELN ERNAM VBTYP AUART INTO CORRESPONDING FIELDS OF ITAB

FROM VBAK UP TO 100 ROWS.

APPEND ITAB.

ENDSELECT.

GET RUN TIME FIELD F2.

f3 = f2 - f1.

WRITE:/'Runtime test1 F3',F3.

GET RUN TIME FIELD F1. u201CFetches runtime for executing the data.

u201CThis is to Open the Cursor for the SELECT statement.

OPEN CURSOR c_cursor FOR

SELECT VBELN ERNAM VBTYP AUART

FROM VBAK UP TO 100 ROWS.

u201CMove the data from the Cursor into the target area.

FETCH NEXT CURSOR c_cursor INTO

TABLE itab.

u201CClose the Cursor to complete the operation.

CLOSE CURSOR c_cursor.

GET RUN TIME FIELD F2. u201CFetches runtime info from previous RUNTIME command.

f3 = f2 - f1.

WRITE:/'Runtime test2 F3',F3. u201C Time taken to execute statements between the two RUNTIME statements.

Data from the test run can be found in the following screen shots. ( 4 Test runs taken )

Runtime test1 F3=> Runtime performance for SELECTu2026ENDSELECT

Runtime test2 F3=> Runtime performance for CURSOR

Test Run 1: Selecting 10 rows from VBAK table. 1095 548

Test Run 2: Selecting 100 rows from VBAK table. 1629 1721

Test Run 3: Selecting 1000 rows from VBAK table. 18198 12160

Test Run 4: Selecting 10000 rows from VBAK table. 252172 185468

boldPerformance Analysis for CURSOR Vs SELECT Statementbold

This is the code extract that demonstrates the time taken to fetch records from the table VBAK. (Using a SELECT statement into an internal table with Header Line)

GET RUN TIME FIELD F1.

SELECT VBELN ERNAM VBTYP AUART INTO TABLE ITAB

FROM VBAK UP TO 100 ROWS.

GET RUN TIME FIELD F2.

f3 = f2 - f1.

WRITE:/'Runtime test1 F3',F3.

GET RUN TIME FIELD F1. u201CFetches runtime for executing the data.

u201CThis is to Open the Cursor for the SELECT statement.

OPEN CURSOR c_cursor FOR

SELECT VBELN ERNAM VBTYP AUART

FROM VBAK UP TO 100 ROWS.

u201CMove the data from the Cursor into the target area.

FETCH NEXT CURSOR c_cursor INTO

TABLE itab.

u201CClose the Cursor to complete the operation.

CLOSE CURSOR c_cursor.

GET RUN TIME FIELD F2. u201CFetches runtime info from previous RUNTIME command.

f3 = f2 - f1.

WRITE:/'Runtime test2 F3',F3. u201C Time taken to execute statements between the two RUNTIME statements.

Data from the test run can be found in the following screen shots. (4 Test runs taken)

Runtime test1 F3=> Runtime performance for SELECTu2026INTOu2026

Runtime test2 F3=> Runtime performance for CURSOR

Test Run 1: Selecting 10 rows from VBAK table. 964, 582

Test Run 2: Selecting 100 rows from VBAK table. 1686, 1533

Test Run 3: Selecting 1000 rows from VBAK table. 33,382, 11,350

Test Run 4: Selecting 10000 rows from VBAK table. 220,920, 113,165

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Mon, 12 Jan 2009 11:20:50 GMT

Please find further information on this in Wiki " SELECT Statements and CURSOR statement - Performance Analysis ". Updated a few minutes ago.

Thanks & Regards

Harsha

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Mon, 12 Jan 2009 11:48:57 GMT

Hi Harsha,

First of all, I think there is something wrong how you use the UP TO ROWS and FETCH.

UP TO ROWS just limit the maximum number of lines that you can get from database. If you call FETCH only once you are not sure to get all lines, as FETCH reads one package at a time and you don't know the package size (if it is calculated by the system to be let say 50, the fetch will return only 50 lines instead of expected 100). You must also use PACKAGE SIZE to set the minimum number of lines. Do the same for SELECT...ENDSELECT so that you can compare results on the same basis.

Also, I see the following 2 problems :

- For the first program, your test is not correct as you don't use the same statements for both tests : You use APPEND itab for SELECT...ENDSELECT but you use INTO TABLE for FETCH. It's a very good reason for FETCH test to be faster according to me.

- For the second program, your test is not correct as you use SELECT INTO TABLE without ENDSELECT. I think you had problems with the syntax INTO TABLE because you didn't use PACKAGE SIZE.

You can code your SELECT this way and repeat your tests:

SELECT VBELN ERNAM VBTYP AUART INTO TABLE itab
   FROM VBAK UP TO 100 ROWS PACKAGE SIZE 100.
* nothing
ENDSELECT.

and :

OPEN CURSOR c_cursor FOR
   SELECT VBELN ERNAM VBTYP AUART
   FROM VBAK UP TO 100 ROWS.
FETCH NEXT CURSOR c_cursor INTO TABLE itab PACKAGE SIZE 100.

Again, I get no difference between the 2 methods.

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Mon, 12 Jan 2009 11:50:53 GMT

I don't find the wiki, which url is it ?

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Mon, 12 Jan 2009 11:59:21 GMT

https://www.sdn.sap.com/irj/scn/wiki

SELECT Statements and CURSOR statement - Performance Analysis...I have updated it 33 mins ago

Edited by: Harsha Cheruku on Jan 12, 2009 12:59 PM

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Mon, 12 Jan 2009 12:08:22 GMT

okay I found it : [wiki SELECT Statements and CURSOR statement - Performance Analysis|https://wiki.sdn.sap.com/wiki/display/ABAP/SELECTStatementsandCURSORstatement-Performance+Analysis.]

That's great that you share your experience with people, thank you very much!

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Mon, 12 Jan 2009 12:20:05 GMT

Thank you!!!

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Former Member — Mon, 12 Jan 2009 12:20:40 GMT

I have found the answer

Re: Difference between OPEN CURSOR..FETCH CURSOR..CLOSE and SELECT...ENDSELECT

Sandra_Rossi — Mon, 12 Jan 2009 13:29:34 GMT

Which is what?