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.
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.
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.
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 .
2. Content Assistance
The SQL Console offers content assist that will help the developer to construct the query quickly by pressing ctrl + space.
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.
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.
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.
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.
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.
After adding the current query as “Favorites”
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.