I guess that my favorite type of work and, moreover, the type of work that was especially required for me to fulfill during my last 10 years or more was the work of refactoring. As I am a pure database developer, that type of SQL developer specialized in dealing with data manipulation in various relational databases, I was often in the position to try to improve things. By improving new things means, in general, trying to find better ways in various part of the system, generally places related to data processing, obviously.

Of course, one of the first reasons for refactoring is performance, the need for a better performance. When mentioning better performance there are many things that can be said and mentioned. Refactoring is one of them and that is, in general, the last solution to be used. The DBA is trying to tune the system and, eventually, try to add all kinds of features available for the database system. The DBA is setting up new parameters in the environment and the work is more or less done. Sometimes the results are great and sometimes not. Anyway, the Dba’s intervention is just one part of the problem, and not always the most important one.

The architect is trying to find the best structure for one system or another. This is critical and this is, obviously, the foundation for any system. However, the time for the logic itself and development will follow. And, even if is a new system, the style of programming in various places of the system is an important component. So, even for a new system, the way we develop things matters.

What about an existing system that is working for years and people working there are gone for a long time? This system is experiencing severe performance issues and needs improvements. Maybe even a new system is required to replace the old one and perhaps very often this would be the best solution. But this is not always an acceptable solution for the software company or for the customer that is handling its own systems. Sometimes the systems need to be adjusted and the logic needs just to be improved, because the company is not in the position to change completely the system. To conclude, sometimes the logic needs to be refactored or even rewritten and the system require this.

Refactoring a system, when building a new one is not possible, is a hard task and requires a lot of patience and trust, a lot or work and concentration. The programmer in the position to refactor needs to be able to understand the existing logic, at quite a good level. The developer needs to be able to understand logics written many years ago and change them in a, let’s say, modern manner that is in general more suitable. Very often, the reason for refactoring is performance but not only. Others reasons might come into game like portability and maintainability. After many years of usage, some systems are almost impossible to read and understood by newcomers, becoming almost unintelligible and incomprehensible. Consequently, maintainability is one other major reason for refactoring. Moreover, a system is not simply maintainable anymore and requires major adjustments.

Portability is also a common reason for refactoring. Especially for software companies that needs to maintain the same system in various databases need to take into account the portability and needs to try to maintain the SQL code in such a manner that this can be easily maintained in various systems with a reasonable amount of work.

These concepts, performance, portability or maintenance are notions that apply to the entire infrastructure of a system or another. My concern is the database and the relational database, at least in the beginning, and, of course SQL. My goal is to try to show some ways in which the SQL code can be more readable, more portable and with a better performance. I would consider the third component as the major one! From this point of view, refactoring or rewriting the SQL code is one solution that can bring huge improvements for all the three.

Why I am talking about this here and what has to do with my Simply SQL site? Because one of the common topics of the site is related to refactoring. The process of refactoring consists, quite often, in a transformation. These exercises that will promote a certain way to refactor the SQL code are somehow a continuation of my book in Apress (https://www.amazon.com/Relational-Database-Programming-Set-Oriented-Approach-ebook/dp/B01HRB5MW0/). In two words, this book describes an opposition between two styles of programming in the relational database, the atomic approach specific to application developers versus the holistic (set based) approach specific to SQL developers. One major way to refactor the SQL code part of a system is to change the style and to rewrite from the atomic approach to the holistic, set based, approach. A lot of old systems are still written in this style and, moreover, even new systems may be written in this manner if the programmers do not have the SQL vision of the data set and think too atomically, using the facilities that are used with in the database too.

One of the topics that will be covered in my site will continue to insist in this opposition and to explicitly promote the holistic, set  based approach that is required in the relational database. One of the first things in the refactoring process is the change of the style of programming and there will be a lot of SQL exercises and practices that will illustrate this opposition.