We have seen in the previous post the most important violations to best practices of ABAP programming.
These defects are ‘Blockers’: the code can not go into production until a correction is performed. No exception is permitted: zero tolerance, because the risk is too high to see a transaction aborted and the user unable to perform the desired treatment.
We also set our Quality Profile ABAP in order to identify critical defects, these ones focusing on performance, which we will see in this article.
I forgot to mention last time, but my ABAP analysis covers about 700 files representing more than 43 000 lines of which around 26 000 lines of code (the rest is comments). This is to give you some idea of the number of defects with respect to the size of the application.
Performance problems are primarily best practices not followed in SQL code. As such, they are not specific to ABAP. Below are the most numerous in the application I did analyze:
A SELECT * is no an acceptable practice in all languages, because this query returns all the columns of the table. If the number of rows returned is also important, the size of data to be transported over the network is likely to impact performance. In addition, if columns are added later in the table, the data for these new columns will be in the SELECT * when they are not used.
A SELECT DISTINCT eliminates duplicate rows, when you have more than once the same record. The problem is that this treatment is very heavy in terms of performance, especially if some of the fields used with the DISTINCT clause are not indexed. In addition, we must ensure that this treatment is useful because it often happens that there are no duplicate rows. Finally, it is often possible to avoid this treatment and programming differently the WHERE clause.
Generally, it is not recommended to access a database through a loop, with treatment of INSERT, UPDATE, MODIFY, DELETE. Generally, the loop indicates that the program reads a table or a set of records to perform this update. The performance problem may be small for a new application with a table containing some records, but over time data will be added to this table, its size will grow, the loop will become longer and performance problems will begin to appear.
A SELECT without a condition, that is to say, without a WHERE clause returns all records in the table. It is often an indication of a programming error. Again, with the addition over time of data in the table, this SELECT will cause a performance problem.
Ah, here we have a critical that does not affect performance but the stability of the application. A CASE … ENDCASE statement allows different treatments (branches) for different values of a variable or field. The OTHERS clause is used for all other cases (all other values) specifically not treated by the CASE. It is a simple question of error handling: if an event occurs that was not managed, the absence of the OTHERS clause means that no specific treatment will apply. The program will continue to run, maybe wrong or with incorrect values. The risk is high that an error occurs and that it will not be treated. In addition, this error occurs usually much later in the code, and therefore the detection of its origin is often lengthy and costly in terms of maintenance.
Some customers consider this ‘bad practice’ as a Blocker. I’m pretty okay with that.
If we look at the following defects in the code i did analyze, we see that we are back on SQL programming.
An UPDATE or a DELETE with no WHERE clause means updating or deleting all the rows from the table. Better make sure it is the expected treatment and not a mistake!
SAP provides a buffer that allow to read a table directly from it and not from the database, with a gain of performance. Treatments such as JOIN or calculation functions involving aggregation (COUNT, AVG, MAX, MIN, etc..) prohibit the use of this buffer. And this, the programmer does not always know!
A fortiori, a treatment that ‘bypasses’ the buffer – that is to say deliberately avoid it – will need to be seriously justified.
Finally, the use of a LIKE clause does not allow, once again, to predict the number of records returned, especially when the table grows over time. And nested queries (SELECT … IN SELECT …) should be avoided in all languages.
There are still other critical rules that we have not seen, simply because I have not encountered this type of defects in the code that I analyzed. For example,
- Avoid SQL queries are joining too many tables
- Avoid using GROUP BY in queries
But the goal is not to list all the ‘bad practices’ possible in SQL programming. However, let’s see some critical violations that are specific to ABAP. For example: “Avoid too many functions in function pool”.
A ‘function pool’ is a group of functions used (called) by a program. During this call, all the functions are loaded into memory. And for the entire lifetime of the program. So the more features include this module, the more time is needed to load it into memory and the more space it will take in it, which can cause a performance problem. SONAR automatically lists all function pools with more than 15 functions.
We also have some ‘best practices’ which do not relate to performance, but like the ‘CASE OTHERS …’, create a risk to the robustness of the application:
- Forbid use of INSERT/DELETE REPORT/TEXTPOOL
- Forbid use of GENERATE REPORT / SUBROUTINE POOL / DYNPRO
These instructions are prohibited because reserved for SAP (because they have access to kernel data that may disappear in future versions).
The last rule “Prevent use of EDITOR-CALLS” focuses on an instruction that calls the SAP text editor. But this instruction does not control user rights, as it is the case of any SAP transaction. In other words, the programmer can give edit access to someone who does not have this access. This statement is abandoned in favor of other more controlled treatments.
What justifies that a rule is of type ‘Blocker’ or ‘Critical’? After all, one might consider that some of the violations found in this post are serious enough to join the category of defects blockers. Yes, but the difference is that we will not accept any exception for a ‘Blocker’ while this may be the case for ‘Critical’ rules. Of course, this exception must always be justified, but you can accept that this code goes into production while you decide whether a correction should be made or not.
We will conclude in the next post the presentation of the ABAP rules in our SONAR Quality Profile.
This post is also available in Leer este articulo en castellano and Lire cet article en français.