Last post in our series on the analysis of the quality of PL/SQL code with SonarQube.
Assessing the quality of an application is not just doing a code analysis: anyone can do that. The work of a Quality consultant is based on answering the following questions: what, why, how, how much.
- What: analyze the results. Size, complexity and duplications, this is what we have seen in our previous posts. We examine the overall figures but also the average and the variance, as well as trends over time when there are several versions. Then we look at the main violations of best practices, focusing primarily on Blockers and Criticals.
- Why these results: investigating the causes of the analyzed measures, searching for the origin of the results.
- How to remedy: propose a plan of action. In fact, make several proposals. We shall see below that I propose different plans for the short, medium and long term.
- How much it costs : assessing the cost of each plan.
For example:
- What: We find a defect, critical for security.
- Why: It is likely that one or more people in the project team does not know this rule. Or on the contrary, the corresponding good practice is known, but a lack of attention is always possible.
- How to: The remediation may consist in a simple correction of the defect in the code or a training on this good practice.
- How much: This is where the SQALE plugin will be useful.
I will not explain in detail the SQALE method and the SQUALE plugin of SonarQube. Maybe I will have the opportunity to do so in a future post, but there is already enough information on the subject. You can have a look at:
For the estimation of our action plans, we will consider that the project team in charge of maintaining this SQL code consists of 3 people. In general, a Legacy application of this kind does not change very frequently, so we consider that this team delivers four versions per year.
Let’s remind that a man/year is equal to 52 weeks, less some holidays (and other absences), or 45 weeks or 225 days (in France). This figure may vary depending on your country, but not that much.
Technical debt and PL/SQL
Short Term Plan – Remove threats
The most important and first priority is to remove everything that constitutes a threat to the end user, as quickly and as soon as possible. This is the minimum level, thus essential in the short term.
We oriented our Quality Profile toward violations in Security , Robustness and Performance, which impact the users. Obviously the Blockers and Criticals defects are the most serious threats.
The SQALE plugin allows us to check the cost of these remediations: about 113 days. 
The short-term plan is therefore a refactoring of 6 man/months, or two months for the 3 persons of our project team.
When: as soon as possible, for the next version in 3 months. It is perfectly acceptable to propose to stakholders to postpone non-essential developments for a future version, so that the project team performs first the two months of refactoring, then works one month on the most essential functionalities. If they are too many, it is possible to play with the timing by waiting 4 months for the next version. Or make a ‘refactored’ release after 2 months, and the next one in the following 4 months.
Benefits: a safer application, more robust and more efficient, thanks to the elimination of the most urgent and serious threats.
Medium-term plan – Align IT targets with technical debt
Never forget that the IT strategy, and therefore the management of the application portfolio, is always aligned with the business strategy. A market is either:
- Mature, with a strategy of preserving market shares and financial margins, so compliance with budgets and costs will be a major objective of the IT strategy. For the project team, this means: focus on maintainability and capacity to implement changes into the application. Do not let the technical debt drift on these two factors.
- New, with a strategy of market shares conquest, and time-to-market for robust and efficient applications: this is the direction we have given to our Quality Profile and our analysis of the quality of this application.
Our medium-term plan will therefore aim to correct all defects affecting the safety, performance and reliability, and not just the Blockers and Criticals. The SQALE pyramid allows us to calculate the cost of this medium-term plan.
A total of 395.6 days. For 3 people, this is seven months of work. Difficult to paralyze the project for more than half a year.
However, this includes the previous 113 days from the short-term plan, so it is actually an additional 282 days, or 15 months of a person or five months for each of the three persons of the team. Various suggestions then come to mind:
- Limit the number of new features over the next five versions so that each team member can dedicate one month every quarter to focus on remediations. This is possible if this application does not longer evolves very much, and if users currently require improvements in robustness and performance, for example.
- If many new features are critical and the list of evolutions can not be reduced, then the stakeholders must be willing to pay to add a fourth person to the project team, who should focus solely on correcting these defects in the 5 upcoming releases.
As you can see, this is a medium term plan, for the next 18 months, including the short-term plan. Now it is possible to refine it in order to present different variants, more acceptable to the stakeholders and the IT Director, worrying about budgets. Again, the SQALE plugin allows us to go to the essential.
For instance, the SQALE Sunburst graph shows that 104 days are necessary to correct violation to a rule of performance, about datatypes.
I believe that it is Oracle 11g that introduced a new type of data that improved up to 50% the performance of some stored procedures. If the response time of the application is not a major problem for the users, then we can postpone the remediation of these defects and save 104 days, or 21 weeks, about 7 weeks for each member of the project team. Of course, the idea is not to reduce the medium-term plan to a short-term plan, but to concentrate on the most important, with the help of this graph that shows the distribution of the technical debt on different kinds of application risks.
Long-term plan – Align the technical debt with the application strategy
The long-term plan must address an obvious question: what to do with this monstruous component which integrates all the business logic of the application? The SQALE plugin shows that the technical debt for that one component is 1 431.9 days, or 75 % of the total technical debt for this application (more than 6 man-years).
In fact, the question then becomes: what to do with this application? Everything will depend on its level of criticality.
If an application with a very high technical debt is not critical, the solution is simple: drop it. Continue maintaining it will cost more than replacing it, either by a business solution or by a a new application developed with a more recent technology. Because the application is not critical, it is not essential to retain control of it or preserve knowledge of it, so you can either outsource this development to a provider or make a call for tenders to software vendors or integrators competent on this kind of solution. In all cases, the partner you select will maintain the solution, normally for a lesser cost than if you maintain it yourself, but with the added complexity of managing an external supplier.
If the application is critical, then you need to manage the problem yourself because you do not want to give up the management of the risk that this application can represent for your business. There are then two possibilities: a refactoring of the application or a complete rewriting.
In fact, correcting all the defects encountered in this monstrous component and leave it as it is does not make much sense. A new design of this application should be a priority, which actually promotes to rewrite it with a new technology.
We have seen that this database contained 687 tables, not counting the views, but with a significant duplication of data structures. Thus, my recommendation for a long-term plan would be to:
- Conduct a reverse documentation to list the different components present in the application and the links between them.
- Carry out a conceptual re-design to map the functional objects, initially on the same functional perimeter, then taking into account the functional changes desired by the users.
It is possible to outsource this work to a service provider, especially if the current project team has experienced some turnover during the life of this application and has lost some knowledge of it.
Nevertheless, this provider must be equipped to realize this service: even if 150,000 lines of code and comments is not very high, this work should be automated using a tool that allows to trace all components and their relationships.
Which actually is great: SonarSource has a project for such a tool in 2014. Our PL/SQL application will then be a good candidate for a test of this future SonarSource production.
This post is also available in Leer este articulo en castellano and Lire cet article en français.