Fetch statement using native SQL

Former Member — Wed, 05 Aug 2015 17:18:08 GMT

Hi,

I have a report which uses native sql.

EXEC SQL.

OPEN c1 FOR

SELECT VBAK.VKORG, VBAK.AUART, VBAK.VBELN, VBAP.POSNR, VBKD.BSTKD,

VBAP.ZZEXTMATGRP, VBAP.PSTYV, VBAP.MATNR, MARA.BISMT,

MAKT.MAKTX, MARA.mtpos_mara, TVMST.VMSTB,

VBAP.WERKS, VBAP.LGORT,....

.ENDEXEC.

The above code has many selects with in based on several conditions.... I am not sure how to improve the performance without rewriting it with open sql using selection screen parameters.

Having said that, i do not ( as it is an existing one ) want to change the open cursor statement and wanted to know if there is a way to use packet size or any other method to improve the performance while doing the fetch,

DO.

EXEC SQL.

FETCH NEXT c1 INTO :i_tab

ENDEXEC.

IF sy-subrc <> 0.

EXIT.

ENDIF.

APPEND r_tab TO i_tab.

ADD 1 TO l_kount.

IF L_KOUNT >= ROWS.

EXIT.

ENDIF.

ENDDO.

The above is my fetch statement and the fetch gets a single line everytime it runs. I know I can use packet size and use the FETCH to a internal table, but in native SQL, it is not allowing me to use an internal table .

Is there a way to fetch to an internal table while using native sql , if not, are there any means to improve the performance of this code?

Thank you for your valuable time

Re: Fetch statement using native SQL

paul_bakker2 — Wed, 05 Aug 2015 22:57:14 GMT

Hi,

I suggest you rewrite it in Open SQL and see how the performance compares.

Use the full battery of Open SQL features and add indexes where required.

Any idea why they went with Native SQL in the first place? It's usually not a good idea.

cheers

Paul

Re: Fetch statement using native SQL

Former Member — Mon, 10 Aug 2015 17:04:34 GMT

Thank you Paul for your reply.

I am not sure why they used native SQL , said there are some features they could not achieve with open sql, though I am not sure of it. The requirement is put on hold for now. So its all good, for now

topic Re: Fetch statement using native SQL in Application Development and Automation Discussions

Fetch statement using native SQL

Re: Fetch statement using native SQL

Re: Fetch statement using native SQL