Fetch statement using native SQL

[Former Member]

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

[paul_bakker2]

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

[Former Member]

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