Performance issue using Native SQL

Former Member — Tue, 04 Apr 2006 15:29:53 GMT

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.

Can any one suggest a solution to improve the performance.

Thnaks

Vianney

Re: Performance issue using Native SQL

christian_wohlfahrt — Tue, 04 Apr 2006 16:18:10 GMT

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

topic Performance issue using Native SQL in Application Development and Automation Discussions

Performance issue using Native SQL

Re: Performance issue using Native SQL