topic Re: Open sql vs native sql in Application Development and Automation Discussions

Open sql vs native sql

Former Member — Fri, 05 Jan 2007 02:39:46 GMT

Hi,

I want to know do you always use native sql for abap programming, i believe that use native sql has better performance than open sql and easy to retrieve data from database, but may be face the upgrade issues, database changes and so on. how do you balance them. Thanks!

Re: Open sql vs native sql

gopi_narendra — Fri, 05 Jan 2007 03:17:38 GMT

Open SQL

Open SQL is a set of ABAP/4 commands which perform operations on database tables. The results of these operations and associated error messages are independent of the database system used. Open SQL thus offers you unified SQL syntax and semantics for different database systems (see Portability). An ABAP/4 program which uses these commands can be run on database systems supported by SAP without modification.

Native SQL

Native SQL allows you to perform operations on databases over and above those in the Open SQL command set. In contrast to Open SQL, Native SQL supports not only operations on the local database active in the R/3 System, but also on any external databases.

We normally use open SQL, but in some case when you want to say look at the upper case value of an upper/lower case field in a database, you can use native SQL.

Native SQL seems somewhat slower and you have to know which databse you will be using it on before coding.

For more information type EXEC in the editor and press F1.

Click the below link for OPEN SQL

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3969358411d1829f0000e829fbfe/content.htm

Click the below link for Native SQL

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Please assign points to all helpful answers

Regards

- Gopi

Re: Open sql vs native sql

seshatalpasai_madala — Fri, 05 Jan 2007 04:00:27 GMT

Hi,

By using NATIVE SQL we ristrict the application. It becomes DATABASE dependant. You cannot migrate from one DATABASE system to another with out code change. OPEN SQL provides you that flexibility. And also since the conversion of OPEN to NATIVE happens in the application server's database interface it will not have much difference in performance, and the avantages of buffering and locking concepts of ABAP come handy here. SO its always better to use OPEN SQL than Native SQL unless you are sure that you will never migrate to any other database.

Regards,

Sesh

Re: Open sql vs native sql

Former Member — Fri, 05 Jan 2007 09:33:34 GMT

Hi,

If you are accessing the SAP tables and database you go for native SQL.

In case you want to access other database other than the one SAP is using you go for Open SQL.

Regards

Arun

Re: Open sql vs native sql

VinayPrasad_PM — Fri, 05 Jan 2007 11:50:47 GMT

Hi,

By default Open SQL statements use automatic client handling.Also you can use same syntax irrespective of underlying data base.

Native SQL does not do automatic client handling. It treats MANDT field just as any other field.

Native SQL statements are always risky being DB specific queries, since it by passes R/3 database interface, for this reason we need to use Open SQL.

ABAP Dictionary that contain long columns with the types LCHR or LRAW should only be addressed using Open SQL, since the columns contain extra, database-specific length information for the column. Native SQL does not take this information into account, and may therefore produce incorrect results.

So it takes greater resposibility on part of application developers before using NATIVE SQL.

Regards,

Vinay

Re: Open sql vs native sql

Former Member — Fri, 05 Jan 2007 14:02:29 GMT

My experience is that native SQL is slower than open SQL, but that native SQL allows things that open SQL doesn't (like translating to upper or lower case when selecting). Since native SQL is platform specific, if you write native SQL on one database platform, it may not work on another.

Rob

Re: Open sql vs native sql

Former Member — Fri, 12 Jan 2007 04:05:43 GMT

HI,

Yes...sometimes Native SQL is good than Open SQL, b'coz you will never get a time out error while accessing huge databases.

You can write Oracle stmts inside the Native SQL, like fetching first 1000000 records initially, then doing your processing, then again fetch the next 1000000.

In this way, the parallel processing will happen and you will never run out of memory.

Regards

Subramanian

Re: Open sql vs native sql

Former Member — Fri, 12 Jan 2007 04:55:33 GMT

Portfolio,

Open SQL

Native SQL

We normally use open SQL, but in some case when you want to say look at the upper case value of an upper/lower case field in a database, you can use native SQL.

Native SQL seems somewhat slower and you have to know which databse you will be using it on before coding.

For more information type EXEC in the editor and press F1.

Click the below link for OPEN SQL

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3969358411d1829f0000e829fbfe/content.htm

Click the below link for Native SQL

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Please assign points to all helpful answers