SAP ABAP Perfection - Fixing performance issues - The SELECT statement

In my last post, I had discussed about the simple ways on increasing the performance of your program. Continuing where I left off, this post discusses the subtle improvements you can make to your SELECT statement. It will bring you substantial performance improvements without much effort.

I have tried to answer some basic questions. Please feel free to skip those of you are already aware about the same.

Do you need to read this post?
If you have ever faced performance issues with the programs you work on our are supporting your clients on. If not, read this post for some general knowledge ;)

What is the SELECT statement?
The SELECT statement is one of the very important statements of ABAP language which enables us to fetch data from the underlying database.

What is the basic syntax?
As mentioned above the use of the SELECT statement is to get specific data set from some table(s) and store it in one or more variables. 

SELECT [n fields] FROM [table] INTO [Internal Table] 

WHERE [condition 1] AND [condition 2].

Simplifying -

SELECT [what] 

FROM [where]

INTO [store in]

WHERE [based on which conditions].

How to improve performance? 

You will be happy to know that we can improve performance in each section of the select statement easily. Just remember the rules discussed below and you should do just fine.

SELECT [what] - In this section, we define which fields we need to select from the database. 

• Use only those fields which are required instead of selecting all the fields in the table. This not only improves performance, but also uses much less memory space for execution

• Put the fields to be selected in the order in which they are maintained in the table

FROM [where] - This is where we mention the table or views from where we want to get the data. 

• Use FOR ALL ENTRIES instead of JOINS. 'FOR ALL ENTRIES' restricts the data set for querying the second table (when we want data from two tables together). This avoids the huge memory space required by the JOIN to execute. 

• Use database VIEWS instead of JOINS. Database views have a performance advantage over dynamic JOINS and can help you to select the data faster

INTO [store in] - In this section, we define where we want to put the data selected from the table. 

• Get all the data required by your program into an internal table using a single SELECT statement instead of doing multiple selects. You can separate the data programmatically into different internal tables once you have the full data set. This ensures that you avoid multiple database hits and thus increase performance dramatically. Also, separation of data in the program requires much less time than several database hits

• Avoid selecting the table entries into a work area inside a loop. This is by far the biggest performance problem that brings the system to a halt. In the old days, developers had to use the SELECT-ENDSELECT statement to fetch data. Now, you have the option of selecting into an internal table. Use it without fail

WHERE [based on which conditions] - This section is used to define the conditions based on which the select query will fetch the data.

• Conditions should be based on the index in the table. E.g.: if the primary index in the table is maintained as [field1, field2 and field3], then the conditions in the WHERE clause should also be like - [field1 = var1 AND field2 = var2 AND field3 = var3]. Basically, following the sequence of the index tells the SELECT statement to use that index while querying the table. If you do not follow the sequence of index, then it depends on the system query analyzer to find the best index possible. In many cases, it does not find any index to use resulting in a huge performance impact

• Avoid using the OR condition. In the open sql language, the OR condition is converted into multiple selects resulting in more database hits than you planned for. Try to skip the OR condition and later delete the extra data selected programmatically. This will give you better performance as lesser number of database hits will be executed

So there you are guys, these were the simple steps you can follow to get a lot of juice from your SELECT statement. Of course, there are not the only steps. There are much more and need a case to case analysis. 

Feel free to put in your comments and suggestions.