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

[Former Member]

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

[Former Member]

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]

[Former Member]

This message was moderated.

[Former Member]

This message was moderated.

[Sandra_Rossi]

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.

[Former Member]

hi,

Refer to this link..

[Open Cursor Vs Open Sql (SELECT)|]

[Sandra_Rossi]

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

[Former Member]

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

[sujeet2918]

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.

[Sandra_Rossi]

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

[Subhankar]

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

[Former Member]

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.

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

[Sandra_Rossi]

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.

[ThomasZloch]

>

> 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

You should always discard the first measurement due to table buffering and caching. If you do that, and the select operations are identical, I'm very sure you will see no runtime difference between these two techniques, just as Sandra describes.

As far as I am concerned, the only reason to use these OPEN CURSOR and FETCH statements (with addition WITH HOLD) is to be able to send DB commits when doing block processing of large amounts of data, also just like Sandra described.

Thomas

[Sandra_Rossi]

Thomas, I don't think the difference was due to the buffering/cache, because Harsha ran it several times. The problem comes from the abap he used, as I said in my previous post.

I hope that I participated to kill the myth about cursor-fetch is faster than select-endselect, they are the same at performance level (as was killed by other people the myth of "select up to 1 rows is faster than select single"!)

[ThomasZloch]

>

> Thomas, I don't think the difference was due to the buffering/cache, because Harsha ran it several times. 

Hmm, please help me out, where does he state that he ran the selects at least once before starting to take the "official" timing?

[Sandra_Rossi]

Bad point for me, I misinterpreted when he said "4 test runs", in fact it's possible that he ran the test only once, and as he tested 10, 100, 1000, 10000, you're right data was maybe not buffered yet.

In fact, as I was convinced that the difference was due to its abap code (I still am), I answered a little too fast. Sorry about that.

[Former Member]

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

Thanks & Regards

Harsha

[Sandra_Rossi]

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

[Former Member]

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

[Sandra_Rossi]

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!

[Former Member]

Thank you!!!

[Former Member]

I have found the answer

[Sandra_Rossi]

Which is what?

[Sandra_Rossi]

One thing more, it seems that you were also interested by comparison with SELECT ... INTO TABLE ..., so I apologize with people who answered to this question, as I told them it was out of topic.

And btw, Harsha, you should have corrected me when I told that, so that I could better answer.

Bye

[Former Member]

I believe the actual intention of using cursors is to allow multiple SELECTs on the same table without losing cursor position.

Rob

[Former Member]

Hi,

1) I have ran the program several times, after getting to know about the buffer cache from this forum, I still get a small difference in these statements, 90% of the times the the difference between FETCH and SELECT is about 300.

2) For more details on this, please find in this link https://wiki.sdn.sap.com/wiki/display/ABAP/SELECTStatementsandCURSORstatement-Performance+Analysis.

Please add or suggest modifications to the wiki. Please correct me in places I am wrong so that the things are in place as per my perspective.

Regards

Chk (Harsha).

[Former Member]

Hi All,

Please find some more statistics on this.

Note: All test runs are for selecting 100 rows from the table VBAK as in the code in the previous messages. Test Run 1 is after executing the program for several times(8) in order to eliminate the buffer/cache effect.

Using SELECT Using CURSOR

Test Run 1 : 1741 1490

Test Run 2 : 1860 1485

Test Run 3 : 7374 13849

Test Run 4 : 1707 1500

Test Run 5 : 2046 1582

Test Run 6 : 7656 1510

Test Run 7 : 1794 1470

Test Run 8 : 2049 2066

Test Run 9 : 2154 1911

Test Run 10 : 5461 5052

Regards

ChK

[Sandra_Rossi]

On the left, is it SELECT INTO TABLE or SELECT-ENDSELECT ?

[ThomasZloch]

I just ran the test for 100,000 entries from BKPF, no significant difference.

SELECT: 0,125 sec

CURSOR: 0,124 sec

SELECT: 0,126 sec

CURSOR: 0,129 sec

SELECT: 0,127 sec

CURSOR: 0,118 sec

SELECT: 0,122 sec

CURSOR: 0,124 sec

SELECT: 0,118 sec

CURSOR: 0,118 sec

Harsha, like Sandra I suspect that your difference are due to different code logic used.

Thomas

[Former Member]

Analysis of the performances in still on!!

[former_member194613]

before some assumptions are stated, please think twice:

+ Can it really be expected that the SELECT ... ENDSELECT is slower than the OPEN CURSOR?

+ The SELECT ... ENDSELECT is really widely used in all programms and its performance is crucial

for performance. So it must be as good as any similar. It can look different for the user, that

internally it must be as good as possible.

But it is good, that this was figured after a while!

OPEN CURSOR is good if you want to read blockwise not only on one table burt interrelated on several

table, something like that:

do n times.

  ....condition ...
FETCH NEXT CURSOR curs1 INTO it1 PACKAGE SIZE.
 
 ....condition ...
FETCH NEXT CURSOR curs2 INTO it2 PACKAGE SIZE.

enddo.

The main advantage is the option to use it WITH HOLD, as Thomas stated.

OPEN CURSOR WITH HOLD.
   SELECT db_tab1 ...

DO n times.
    FETCH NECT CURSOR  ... INTO itab PACKAGE SIZE 10000.

    process itab
    INSERT db_tab FROM itab.

* commit package but do not close cursor!  
  CALL FUNCTION DB_COMMIT.

ENDDO.

With that you can process large amounts of data. They must be commited, otherwise the redo-logs of the database can become too large.

Siegfried

[Former Member]

>

> With that you can process large amounts of data. They must be commited, otherwise the redo-logs of the database can become too large.
> 
> Siegfried

Hi Siegfried,

mostly agree.

I have only some concerns commiting inbetween a transaction that might fail and must be rolled back ...to what state? Kind of intermediate - and this would not always be acceptable. (you at the failing cash point while the software rolls back to an intermediate state (let's say the part where they withdrawn the money from the bank account and BEFORE it comes out of the slot).

I know you are talking about batches, mass data and the like - but same problems could arise here

if you have transaction boundaries defining a success (commit) -or-failure (roll back) scenario.

I.e. I have to change master data in a lot of tables and failed I'm glad I have my ROLLBACK to go to a "virgin" state .

The reason that database resources becoming too large should not lead to break a transaction in the middle with a mess of intermediate states. I think its the part of the system architecture to support the business transactions needed.

In the case mentioned redo-log files becoming not larger as the maximum size defined, they will be generated more often but with a reliable archiving strategy they will be needed only until the next backup, and can be deleted afterwards.

bye

yk

[Sandra_Rossi]

Siegfried and yk,

Remember that we were only talking of COMMIT database in this thread, not database update :

In SAP, the database commit may occur in lots of cases because of the principle of workprocesses (after the roll out of internal session) ; the following statements do a commit database : WAIT UP TO, SUBMIT, CALL TRANSACTION, CALL SCREEN, CALL FUNCTION in RFC mode, etc.

I just wanted to warn, so that the discussion don't go too far from the main topic (or open a new thread).

sandra

Edited by: Sandra Rossi on Jan 15, 2009 3:27 PM (I added CALL SCREEN)