Let’s talk about the use of stored procedures. Have you learned it?

Influenced by Internet companies, the use of stored procedures in enterprise-level application development in recent years has been subject to certain restrictions. Some companies even explicitly prohibit stored procedures in IT technology application specifications. In fact, more than 20 years ago, the use of stored procedures was a very important technical means for Oracle database optimization, which was very helpful for batch business processing. Because the database CPU, memory, and IO resources were relatively tight at the time, and there were many bottlenecks in network bandwidth and latency. Using stored procedures can encapsulate some work in the internal execution unit of a database, reduce the interaction between the foreground process and the RDBMS kernel, and thus achieve higher efficiency. Based on this, many banks are still using stored procedures extensively.

Twenty years ago, a Huawei project team removed all stored procedures when transforming an application to achieve database independence. After the application went online, they found that the performance of the transformed application had deteriorated significantly. I helped analyze the situation and found that because the database and application modules were deployed across data centers, the SQL interaction delay on the network seriously affected the performance. Therefore, under the conditions at the time, it was difficult for them to give up stored procedures, so they eventually gave up multi-database support and chose to return to stored procedures.

With the success of Internet companies in IT in recent years, many companies are also learning Internet architecture. A large number of applications no longer use stored procedures, and the business logic of applications is increasingly extracted from the database and placed in the application system. The application's dependence on the database is reduced, and the migration of applications between different types of databases has become simpler. At the same time, because more business logic is migrated to the application server, the resource consumption of the database server has also decreased, and the bottleneck of the database server has also been alleviated. Therefore, the use of stored procedures has become popular in many companies in recent years, and the use of stored procedures has also dropped significantly.

However, some companies have found that after removing stored procedures and putting business logic into applications, application quality control has become more difficult. In the past, those who developed stored procedures in a research and development team were veterans who had a deep understanding of business logic and good database skills. Although the stored procedures written by these people were complex, the quality was still very good. Even if there were some problems, they could be optimized by focusing on optimizing the PL/SQL code. Now that business logic is dispersed in applications and developed by developers with different levels, the quality of applications has become more difficult to control and the difficulty of optimization has also increased.

In fact, most traditional industry companies lack Internet genes. The biggest difference between Internet companies and traditional companies is the difference in IT investment. Internet companies can put all logic into applications, not because of how excellent the architecture of Internet companies is, but because they can invest heavily in IT and have a large number of excellent developers to complete this work. The IT investment of traditional industry companies cannot be compared with that of Internet companies. The salary income of IT employees is much lower, and the quality of IT department personnel is definitely much lower than that of Internet companies. In this case, it is often difficult for the R&D team to control the application of Internet architecture.

Enterprise-level relational databases all have the function of stored procedures, which is designed to simplify application development and improve application efficiency. Using stored procedures in applications can reduce the cost of application software development and maintenance and improve the performance of batch processing in the system. In addition to the guidance of Internet companies, another factor that has led to the sparse use of stored procedures in enterprise applications recently is to reduce dependence on a certain database. In fact, in the process of de-IOE in previous years, many companies have felt the pain of database migration and the trouble of rising database usage costs after being bound to Oracle databases.

Another reason why many companies decided to learn from the Internet architecture was that many application developments were centered on databases, which were not able to scale horizontally, so databases often became the biggest bottleneck in application systems. Instead of expanding the capacity of expensive minicomputers, it was better to transfer part of the application load to relatively cheap application servers that were easier to scale horizontally.

Some companies have succeeded in their application architecture transformation, but after many companies have solved the database server bottleneck problem, they have encountered a new challenge - the cost of application development is too high. Compared with the traditional IOE architecture, the current application architecture introduces too many complex components, which increases the cost of application development, lengthens the development cycle, and greatly increases the difficulty of operation and maintenance. Some companies are even reflecting on whether every system needs to adopt such a complex architecture.

Today, with the replacement of domestic databases, I have seen a very interesting phenomenon, that is, most domestic databases provide relatively good compatibility support for Oracle PL/SQL. And the PL/SQL syntax supported by most domestic databases is definitely not comprehensive, but because the easier-to-implement parts of PL/SQL are supported by domestic databases, the PL/SQL syntax of domestic databases is relatively close. It is very easy to migrate PL/SQL stored procedures between domestic databases. After using a domestic database, if you want to switch to another domestic database, it can basically be replaced.

A few days ago, when I was talking about this issue with a domestic database manufacturer, I suddenly thought, in the era of domestic databases, can we return to using a large number of stored procedures to reduce the cost of application development and maintenance? It seems that this is feasible. This was the case when JAVA first became popular and replaced C. Because of the existence of the powerful weapon of stored procedures, enterprises can use experts who are familiar with business logic and database architecture to encapsulate the core business logic in stored procedures, and then arrange a large number of JAVA programmers with average technical level to solve the problem of usability of front-end applications, thus lowering the threshold for information system development by several orders of magnitude.

I have been away from development for many years, so I am not sure what application architects are focusing on now. When I was an application architect, I always pursued simplification and tried to turn front-line developers into tool people. But it seems that the trend has changed a bit now, and application development has become quite complicated. It is also a huge challenge for domestic databases to return some enterprise-level applications to the database as the core. Only when domestic databases can really compete can this wish be realized. However, for enterprise-level applications, appropriately returning to the use of stored procedures may be able to solve some current problems.

Let’s stop here for today. Tomorrow we will analyze the PL/SQL compatibility of domestic databases and where we need to focus our efforts, or from what perspectives users should consider the compatibility of stored procedures when selecting a database.