Skip to Content

Fine Tuning ABAP Open SQL Statements


The Open SQL syntax and features for ABAP has been evolving and has had major advances with release AS ABAP 7.4 SP5. It is a clear indication that Open SQL still remains the preferred approach for database access from the ABAP Application layer.

As ABAP developers we always had our moments of fine tuning Open SQL statements in our application code for achieving better performance. When you did this kind of activity ,

  • did you ever wonder how to check quickly the performance of a complex Open SQL?
  • did you ever look for a simple tool to verify the output of a Open SQL statement you are modeling with quick preview?
  • did you ever want to check the run time of a SELECT that you are fine tuning statement to see how best it would help in improving the performance?

If you answered “Yes” for any of the above questions, Please read on to know more about our brand new SQL Console in ABAP in Eclipse 🙂

What is ABAP SQL Console?


  The SQL Console is a powerful tool that helps the ABAP developers to work with Open SQL Select statement. It can display the result of select query in an intuitive way without the need of ABAP programming. This tool basically eliminate the need of first creating a ABAP repository object like Report Program or Class etc. and use debugger to evaluate the statement at run time in order to test any select statement . It saves lot of developer time by simply let them type the Select statement and preview the result in one step .


The new SQL Console is available from NW ABAP 7.4 SP08 with ADT release 2.31.



Get introduced to the extended Open SQL Syntax

When you use SQL console for the data preview of Open SQL SELECT statements, by default you need to use the newest syntax for extended Open SQL. You could read more about the new Open SQL syntax here and about why should we use new Open SQL here.


Now let’s see more about the features of SQL Console in Abap in Eclipse.


Launch SQL Console

 

    You can launch SQL console by right click on the Abap Project and select SQL Console.


/wp-content/uploads/2014/09/open_549351.png


          The SQL Console can also be launched from Data Preview too. A new button called “SQl Console” is added to the Data preview to open SQL Console with the query based on last executed action in the data preview.


/wp-content/uploads/2014/09/reply_549476.png




SQL Console Editor


    SQL Console opens with the last executed Open SQL statement  in the editor and the results will be automatically shown in an intuitive tabular manner as below. One can open more than one SQL Console and work parallel by right click the Abap Project and select SQL Console any number of times.


editor.PNG


Features like in other Source based Editors

    

      1. Syntax Highlighting

                   

             The SQL Console highlights the text based on Open SQL grammar like other editors like Class , Program etc .

         syntax.PNG           

              

    2. Content Assistance

               

                 The SQL Console offers content assist that will help the developer to construct the query quickly by pressing ctrl + space.

        /wp-content/uploads/2014/09/conentassist_549495.png


     3. Element Information

   

                  The SQL Console supports Element information by pressing F2 on any fields or tables or view , will give the information about the selected object.

        /wp-content/uploads/2014/09/f2_549496.png

   

    4. Navigation


             By pressing F3 or holding ctrl key and selecting the object will open the object in different editor.


   


Automatic Syntax Check

 

          If you have configured AiE to do Automatic syntax check in the Preference page, sql console will pick up the setting and do automatic syntax check based on the configuration.Nevertheless, the open sql select statement can be checked by pressing the check button within the editor or selecting the “Check” option on right click context menu.

Like other AiE source based editors, the error marker will be displayed in the ruler and information can be viewed in the problems view.

syntaxcheck.PNG



Execute Open SQL Select statement


       After constructing the error free Open Sql Select statement, the query can be executed  by either pressing F8 or selecting the Run button inside the Editor. This will display the result in an intuitive way on the right side of the editor.



Show executed query and execution time


     The SQL Console will not only display the results but also displays the actual query executed in the Abap Server and the time taken to execute the query in the log at the bottom of the SQL Console Editor.This information will be really useful in order to optimize the query for better performance.


editor.PNG

Execute query in another system.


   One of the coolest feature in AiE is that once you open any object in one system, the same object in different system can be opened directly by right click the object and select the new system from the “open in other project” context menu. This feature is also available in SQL Console. After one constructed the query and tested in the development system and now wanted to test the same query against the different system, can be done simply by right click inside the SQL Console and choose the system. This will open SQL console with the same query against the specified system and will be executed instantaneously.



View Previously Executed Query in History.


   All the query which were executed in the SQL Console will be stored as history irrespective of query executed in any system or editor. This helps the developer to execute the previously executed query in one step . The default size of history is 10 where one can configure the size from the “History..” option.

/wp-content/uploads/2014/09/history_550264.png           


Mark Frequently executed Query as Favorites

   The developer can mark the frequently executed query as favorites by clicking on the down arrow in the Run button and selecting “Add To Favorites”, so that the developer can get selected query even it is overridden by the history . The favorites query are grouped together and will be displayed at the top of history.

/wp-content/uploads/2014/09/addto_550310.png

After adding the current query as “Favorites”

/wp-content/uploads/2014/09/fr_550293.png

 

Summary


      So that’s all about the brief information on SQL Console in AiE. We believe that this tool will really help the Abap developers to be more productive than before given various features as mentioned above and help them to fine tune the existing  Open SQL select statement to perform better. We hope you will try out this tool and most important give us feedback about the same.



     

To report this post you need to login first.

9 Comments

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

  1. Jasmin Gruschke

    Hi Vijayan,
    just a short comment concerning performance:
    You mention that the SQL Console allows for quick performance checks of an Open SQL statement. Yet the SQL Console limits the retrieved result set of a query to 5k rows.
    So developers need to take into account that this only gives a rough estimate when e.g. comparing two Open SQL statements with each other.
    Cheers,
      Jasmin

    (0) 
    1. Raghuvira Bhagavan

      Hi Jasmin,

      Yes, you are right. The fact that a max of 5k records are shown per query should be taken when comparing the Open SQL statements.

      We limited the records to max of 5k records for performance reasons. in general, we found that the developers are fine with the limit of 5k records. If you, or anyone in the forum disagree, please let me know.

      Best Regards,

      Raghuvira on behalf of Vijayan

      (0) 
      1. Dmitrii Iudin

        I can say that I disagree. I would really prefer it to be configurable somwhere in the same way se16 is doing it. It is not uncommon for some BW Scenarios to have a query that Returns more then 100000 rows

        (0) 
  2. Suresh Ganti

    Hello Jasmin and Vijayan,

    I have a question based on 5k rows max. We are currently trying to pull data from BSID table and show all open invoices. We are encountering an issue that when we pull data from BSID we can only pull max 5K records.

    Is there any possibility to increase this limit and can you please let me know were is this parameter set with this threshold of 5K.

    Thanks,

    Suresh

    (0) 
    1. Raghuvira Bhagavan

      Hi Suresh,

      Currently there is no way to increase the 5K limit. But now that we are receiving many requests on increasing this limit, we will see how to enable it without hitting the performance.

      Do you see any particular order of number such as 10k, 20k records that should be fine? Or would you like to download all the records?

      Best Regards,

      Raghuvira

      (0) 
      1. Suresh Ganti

        Hello Raghuvira,

        Thank you for the response and really appreciate for taking the time to reply the post.

        We would like to pull all records, but is there any other options i.e. if I use OData service can I pull all the records and if I create an attribute view can I pull all records.

        Example: If I need to write an custom report in Eclipse, if I use the code push down method, do I still have this limitation of 5K records?

        Thanks,

        Suresh Ganti

        (0) 
      2. Tobias Schnur

        Hi Raghuvira,

        I really like the SQL console but I agree with Suresh. Whenever I want to use it productivly to do some analysis this 5k limitation is really annoying. It prevents you from downloading large results. It even prevents you from checking how many records in a large table match your constraints. Select count(*) didn’t work for me in the SQL console. That makes the SQL console unusable in many scenarios.

        Regards

        Tobias

        (0) 
  3. Vishu Khandelwal

    Hello Vijayan,

    I have recently started learning SAP HANA from open sap. to try out new features of Open SQL, I have taken a trial system having all admin rights. But when I write new open SQL statements, eclipse is showing me below errors.

    Do I need to maintain any configuration/setting in this trial system for using new statements of Open SQL. Please help. Thanks.

    (0) 

Leave a Reply