Skip to Content
<p> </p><p>Access your Tables directly using SQL in your routines instead of ABAP Open SQL.<br />Many a times you will have situations where you will have to do a lookup on very large tables but then not too sure if the same would work in the best way possible.</p><p>And hit a solid wall when trying to find out what exactly happens in the backend database system. And your Basis or DBA will not be of help here since they do not understand ABAP and talk in a language totally different called SQL written and tested using editors like SQLPLUS through the Operating system command prompt or use tools like TOAD etc.</p><p>In this case if you are more comfortable with SQL as opposed to ABAP SQL ( Open SQL ) you have a way of doing the select statements that we have a love hate relationship  with in a different way.</p><p>What is the difference between Native and Open SQL?</p><p>Databases evolved over time and each database has an SQL Library which differs ever so slightly between databases. For example an SQL statement written for an Oracle Database need not always work in an SQL Server environment. There are numerous parameters like Hints, Indices etc which sometimes can become specific to databases.</p><p>How does SAP handle it?</p><p>SAP being database neutral supports another standard called Open SQL which is the select statements that we see regularly. There is an SAP kernel and a set of DB Shared libraries that do the task of translating the Open SQL into database specific SQL. As a result in many cases if you want to tune your query to the UBER level which your DBA might suggest – you will not have all the options to do so within the confines of OPEN SQL.</p><p><br />A typical example might be:</p><p>Let’s say you have Sales order line items for the last 5 years amounting to about 12 million records. You would like to look this up and do an update to the same through a program for various reasons – but then it becomes tough because you do not know how long this will take and the DBA for your system suggests that there are SQL scripts that they can give you which will make it easier and faster for your process to run , but then do you convert that SQL script into Open SQL or use it as it is – because the original SQL script might also have hints which make it faster on that specific database…</p><p><br />How do you do it….?<br /> 
<textarea cols=”55″ rows=”20″ name=”native SQL”>  EXEC SQL.
   INSERT /*+ APPEND */ INTO “<table name>”
   (
     “<Field 1>”,
      “<Field 2>”,
      “<Field 3>”,
    )
    SELECT
     DECODE( RI.ROW_INDEX, 1, FC.”<Field 1>”, FC.”<field 2>” ) AS “<field 1>”,
      FC.”<field 2>”,
    FROM
    (
      SELECT /*+ ORDERED INDEX( FC “<Table Name>” ) USE_HASH( AM FC ) USE_HASH( MT ) */
        FC.”<field 1>”,
        AM.”<Field 2>”,
        SUM( <Field> ) / 100 AS “<field>”,
      FROM
       “<Table name>” AM,
       “<Table Name>” FC,
      WHERE
        FC.”<Field Name>” = :parameter
     GROUP BY
        FC.”<Field 1>”,
    ) FC,
    (
      SELECT
        ROWNUM AS ROW_INDEX
      FROM
        “<Table Name>”
      WHERE
        ROWNUM <= 2
    ) RI
    ORDER BY
      FC.”<Field Name>”,
  ENDEXEC.
</textarea><br />
<br />The initial program for this was about 300 lines of code with indices etc and still we got the program to run in about 45 minutes or so – with the native SQL – we first validated and tuned the code with the DBA and then the program ran in about 30 minutes. The resultant code was also only about 50 lines of code in all…<br />Also I have removed the fields and their descriptions to mask client specific information. <br /><br />Advantages:

    • Maximum usage of all the features that a Database has to provide
    • All databases have specific features to enable select statements and insert updates etc to run faster – but then these features being database specific – cannot be utilized easily within the constraints of Open SQL.

Disadvantages:

    1. The statement within the EXEC SQL cannot be debugged or checked for syntax – if it is wrong you will get a short dump and that’s all…
    2. the code cannot be ported easily when porting across multiple systems with different databases – code written is specific for the database – do not expect it to work outright when copying code from an Oracle system to SQL Server or DB2 system.

This is intended to part of a blog series talking about usage of SQL in a datawarehousing environment and where and how SQL can be used instead of Open SQL and possibly tuned further. Please let me know your views on the same…

 

Also this does not necessarily lend itself to SAP BW Alone but have highlighted a case where it was used in an SAP BW Environment.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann
    Hi there,

    actually I’m a big fan of using native SQL when it pays to give away the portability. There really are some features available in each DBMS that aren’t available from ABAP.
    Anyhow, up to now I only see that you use hints. That surely can be done from within Open SQL as well (e.g. described for Oracle in note 772497).
    So, what more is there?
    regards,
    Lars

    (0) 
    1. Bala Prabahar
      Hi,

      I know there are some DDL features(Table partitioning for example; SAP supports limited partitioning feature in Oracle) not fully supported by SAP; so we in Basis group create partitioned tables using SQLPLUS. not sure what DML features are not supported by OPEN-SQL. As far as I know, there are only few things you can do at DML level to improve the performance:
          1) Mass inserts/updates/deletes and
          2) Hints
          3) Any thing else?
      My opinion is that more tuning can be done by appripriately designing the application/program. This means appropriate use of internal tables,  avoiding repetitive reading of the same record from db by storing the value in program variable, postponing the lock acquistion to as late as possible and releasing it as soon as possible by shortening the scope of the transaction(this will improve the concurrency level), reading only needed table fileds instead of SELECT * (if all fields are part of an index, then index reads will get everything you need (if you specify index only columns in SELECT) instead of one more read from table) etc.

      Regards,
      Bala

      (0) 
      1. Arun Varadarajan Post author
        I am not sure if there is a wealth of possibilities but then to take an example – if you need some lookups and modifications in certain large tables … then when you have a tool like TOAD or SQLPLUS Utilities to tell you which might work faster – you can rely on Native SQL … there might be certain DDL specific functions that you can take advantage of but then in most cases it does not lend itself towards usage in an application context.
        Just wanted to highlight that you can use this oiption also and have a greater transparency as to what might be possibly happening…

        BR
        Arun

        (0) 
  2. Vijay Vijayasankar
    I am also a fan of Native SQL, and had used it quite a bit when I was an ABAP programmer. I have never used it in BW though. At some point, I stopped using it in ABAP too – when I realized that it is hard to maintain it in production support mode.

    I also like OS level commands for file handling. It is much faster and you can execute it from within ABAP too with an SXPG* function
    .

    (0) 
    1. Arun Varadarajan Post author
      The OS command is extremely nifty when it comes to systems that have a large number of flat files being loaded- we reduced a ton of support time by putting in an OS command to check for a file before commencing the load for the file and our load times improved greatly due to reduced restarts due to file coming in late…
      (0) 

Leave a Reply