OPEN... FETCH or SELECT...ENDSELECT

[Juwin]

Hi All,

Which of the following usages, is more efficient and why?

1st method: Using OPEN CURSOR

OPEN CURSOR WITH HOLD I_CURSOR FOR

SELECT (FIELDS) FROM (TABLE)

WHERE (WHERE_TAB).

DO.

FETCH NEXT CURSOR I_CURSOR

APPENDING TABLE I_DATA_TABLE

PACKAGE SIZE I_MAXIMUM_LINES.

IF SY-SUBRC NE 0.

EXIT.

ENDIF.

<<<<<<<<<<<<<<<Other processing for selected data>>>>>>>>>>>

ENDDO.

2nd method: Using SELECT...ENDSELECT

SELECT (FIELDS)

PACKAGE SIZE I_MAXIMUM_LINES.

FROM (TABLE)

APPENDING TABLE I_DATA_TABLE

WHERE (WHERE_TAB).

<<<<<<<<<<<<<<<Other processing for selected data>>>>>>>>>>>

ENDSELECT.

I feel that the second method, has less lines of code, hits the database same number of times as the first one, is easier to understand. So, I get a feeling that second method is better than the first one. But, most of the SAP standard programs I see, use the first method, which is contradicts my thoughts.

Expert comments please.....

Thanks,

Juwin.

[Juwin]

Hi All,

Which of the following usages, is more efficient and why?

1st method: Using OPEN CURSOR

OPEN CURSOR WITH HOLD I_CURSOR FOR

SELECT (FIELDS) FROM (TABLE)

WHERE (WHERE_TAB).

DO.

FETCH NEXT CURSOR I_CURSOR

APPENDING TABLE I_DATA_TABLE

PACKAGE SIZE I_MAXIMUM_LINES.

IF SY-SUBRC NE 0.

EXIT.

ENDIF.

<<<<<<<<<<<<<<<Other processing for selected data>>>>>>>>>>>

ENDDO.

2nd method: Using SELECT...ENDSELECT

SELECT (FIELDS)

PACKAGE SIZE I_MAXIMUM_LINES.

FROM (TABLE)

APPENDING TABLE I_DATA_TABLE

WHERE (WHERE_TAB).

<<<<<<<<<<<<<<<Other processing for selected data>>>>>>>>>>>

ENDSELECT.

I feel that the second method, has less lines of code, hits the database same number of times as the first one, is easier to understand. So, I get a feeling that second method is better than the first one. But, most of the SAP standard programs I see, use the first method, which is contradicts my thoughts.

Expert comments please.....

Thanks,

Juwin.

[Former Member]

Hi Juwin,

The first method is getting data using NATIVE SQL which is not used anymore.

The standard code uses that, because they are very old code,written long back,.

Regards,

Atish

[Former Member]

Hi

Select and endselect is treated as a loop.

Open: opening the sql

Fetch: fetch the data from database

Close: close the opened sql

for more information check the following link:

http://www.sapbrain.com

Reward al helpfull answers

Regards

Pavan

[Former Member]

Hello Juwin,

following lines traced by st05 will show no difference

tables: marc.

select single * from marc.

select * from marc up to 1 rows.

endselect.

Here is the select single:

MARC PREPARE 0 0 SELECT WHERE "MANDT" = :A0 AND ROWNUM <= :A1

MARC OPEN 0 0 SELECT WHERE "MANDT" = '400' AND ROWNUM <= 1

MARC FETCH 1 0

Here goes the up to 1 rows:

MARC REOPEN 0 0 SELECT WHERE "MANDT" = '400' AND ROWNUM <= 1

MARC FETCH 1 0

So if in both cases on database level the same select statement is used how should the behaviour different?

I would suggest that don't use both query's and select endselect - is major performance issue when you test in production.

Open cursor is outdated.

Thanks

Seshu

[Juwin]

Hi,

I think people misunderstood my SELECT...ENDSELECT statement.

Please note that, in my SELECT...ENDSELECT, I am using the addition PACKAGE SIZE I_MAXIMUM_LINES.

FETCH is also inside a DO... ENDDO loop, and also uses PACKAGE SIZE I_MAXIMUM_LINES. So, both of them will hit the database, same number of times.

Logical databases defined by SAP, also uses OPEN...FETCH, logic.

Please correct me if I am wrong.

Thanks,

Juwin.