Performance issue using Native SQL

[Former Member]

Hi

I am getting the data from Oracle database using Native SQL in ABAP. I am facing performance problem when i am using Native SQL to get data from Oracle database.Its been very slow and getting timed out.

This is the code i am using for this.

EXEC SQL PERFORMING BUILD_TAB.

• Get the territory id

SELECT TERR_ID

FROM DWPROD.VW_ARDETAIL_PRD1@DWP

INTO :IT_TERR-TERR_ID

• :DG_TERR_ID

WHERE COMPANY = :dt_output-bukrs

AND PAY_ADDR_ID = :dt_output-kunnr

AND DOC_NO = :dt_output-belnr

AND DOC_ITEM_NO = :dt_output-buzei

ENDEXEC.

IF sy-dbcnt >= 1.

READ TABLE it_terr INDEX 1.

dg_terr_id = it_terr-terr_id.

EXEC SQL.

• Get the Saled rep id, name

SELECT MR_USER_ID,

MR_NAME

FROM DWPROD.VW_TERR@DWP

INTO :dt_output-srep_id,

:dt_output-srep_name

WHERE TERR_ID = :DG_TERR_ID

ENDEXEC.

*Get sales manager id and sales manager name

EXEC SQL.

SELECT SALES_GRP_ID

FROM DWPROD.VW_TERRRPT@DWP

INTO :DG_SGRPID

WHERE TERR_ID = :DG_TERR_ID

ENDEXEC.

IF sy-dbcnt >= 1.

EXEC SQL.

SELECT MGR_USER_ID,

MGR_NAME

FROM DWPROD.VW_SGRP@DWP

INTO :dt_output-sman_id,

:dt_output-sman_name

WHERE SALES_GRP_ID = :DG_SGRPID

ENDEXEC.

ENDIF.

ENDIF.

Can any one suggest a solution to improve the performance.

Thnaks

Vianney

[christian_wohlfahrt]

Hi John!

Looks like you need just one entry out of the tables, but you select 'into table'.

Try to add a 'up to 1 rows' restriction, then Oracle will be much faster with the execution.

To get an idea, who this parameter looks like in native SQL for Oracle, write a simple test program with an open SQL statement (including this parameter / select single) and run a SQL-trace (ST05). Here you can see in 'explain' how native translation looks like, can also maintain some small tests with 'enter SQL statement'.

Regards,

Christian