Two days ago when I was shopping in Zhihu, I saw such a topic and answered it and shared it here. If you like, let’s discuss it together.
Thousands of rows of SQL stored procedures, common in older development architectures, have their roots in the C/S era. It is often the case that the front end is not designed with the proper framework, leaving all the application logic to the database development. Of course, the advantage of this kind of design is fast online, very short time can take the project. The disadvantage is that it is difficult to expand, especially after the database development once job-hopping, leaving behind is often not understand the ancient ancestral code, at this time to expand the system architecture to support business needs, it is difficult.
As a responsible database developer, you can’t leave thousands of lines of SQL code untouched.
First, understand the code.
The longer the SQL, the more you need to understand it. Development in order to save trouble (the project leader to review) with the head think where, where to write, without overall consideration of the rationality of logic and the readability of the program. Sometimes an operation that could have been done with an update is clumsily broken up many times, probably because of logging, or multiple conditions that do not know how to merge. The former is a design error, the latter is the essence of SQL understanding is not in place.
Before starting to change the code, be sure to understand the code thoroughly, do not rush. Often, SQL application logic like this kind of coupling degree is used in several places, and the change does not necessarily cause a bug where.
Next, split the code.
On the basis of a thorough understanding of the business logic, the whole block of code to be broken down and aggregated. The key to split and aggregate is to control transactions. Whether transactions at the primary table level and sub-table level can be processed separately or must be processed jointly. Consider formatting code with multiple child stored procedures to make it more readable and logically understandable.
The advantage of breaking up code is that it allows you to quickly grasp the business logic, understand each key business point, and develop business sensitivity.
Next, rewrite the code.
Once the code sections have been split and merged according to the business logic, the next step is to optimize at the SQL detail level. At this time, we should first consider the characteristics of SQL language, the idea of aggregation. If you have a Rubik’s cube, pick it up, SQL deals with faces and relationships between faces.
If you want to select all the red squares, some developers will separate the filter conditions of the first, second and third rows, and then plug them into the temporary table to do aggregation. The correct way is to aggregate and merge the filter conditions of 1, 2, and 3 first. Use a SELECT or Update statement to complete the otherwise redundant code.
Strict checks should be made on naming conventions, Magic numbers, and inefficient SQL coding to prevent code corruption. Bad SQL code habits see a lot, write inserts directly… Select * from /update/delete are strictly prohibited; Using temporary tables to load large amounts of data to meet reporting requirements in a massively concurrent system is not to be overdone. OLTP and OLAP are strictly separate libraries, and this coexisting library architecture still exists in many organizations today.
Finally, save the code.
Any Code needs to enter Source Code Version Control. Whether Git/SVC/TFS, for legacy code, new code should carry out complete source code version control, so as to achieve active traceability.
Why are there thousands of lines of SQL code? My guess is there are two reasons:
1 project rush, time is tight, all above the line priority. It is often impossible to assume that you will change your code once you go live. Even if you want to, the demands of the project will SAP your motivation. Once the project is over and you jump ship and double your salary, there’s no going back.
2 Write SQL instead of draft. This is a habit that most people might find strange, writing code and writing drafts? Code, like writing, is expression. Haruki Murakami writes his novels in rough draft and revises them more than once or twice. Borges and other great writers, for revision is very persistent. Not four or five times to modify, are embarrassed to see people. Some of our programmers are just too greedy, too eager to get things done and never look back. See the business want to pick up, this is not bad, but this is a summary of knowledge to improve efficiency, to steadily improve the craft.
How many friends do you have? Pivot’s failure to write well boils down to not summarizing the code you’ve written, and writing a draft gives you the process of summarizing.