Advantages and disadvantages of Stored Procedures

Some time ago, although a company's project was maintained a few years ago, it was not a long history, but it was still a few years ago. The three-tier architecture, coupled with the old architecture of stored procedures, was adopted, this architecture is no problem. Most systems do this. However, due to the misuse of the storage process of this system, it is difficult to maintain it in the future, any adjustments involving the database must be made to the stored procedures, Entity classes, SQL operations, and other related areas. It can be said that the stored procedure is very hurt, so today we will summarize the advantages and disadvantages of the stored procedure.

Advantages

　　1. Running Speed: for simple SQL statements, stored procedures have no advantages. For complex business logic, the database has been parsed and optimized when a stored procedure is created. Once a stored procedure is executed, a stored procedure is retained in the memory, so that you can directly call the stored procedure from the memory the next time you execute the same stored procedure, therefore, the execution speed is faster than that of normal SQL statements.

2. reduce network transmission: the storage process runs directly on the database server, and all data access is performed inside the database server, without the need to transmit data to other servers, therefore, network transmission is reduced. But there are not multiple data interactions in the stored procedure, so the actual amount of network transmission is the same as that of direct SQL. In addition, our application server is usually in the same Intranet as the database. The Bottleneck of big data access will be the speed of the hard disk, not the speed of the network.

3. maintainability: the stored procedure is easier to maintain than the program sometimes, because the stored procedure of the database can be updated in real time. Some bugs can be solved by directly modifying the business logic in the stored procedure.

4. Enhanced Security: Improves code security and prevents SQL injection. This SQL statement can also be used.

5. Scalability: applications and database operations are separated and performed independently, instead of being separated. This facilitates future expansion and DBA maintenance and optimization.

Disadvantages 

1. SQL itself is a structured query language, but it is not object-oriented. It is essentially a procedural language. In the face of complicated business logic, procedural processing will be very difficult. At the same time, SQL is good at data query rather than business logic processing. If you put all the business logic in the stored procedure, this principle is violated.

2. if you want to modify the input stored procedure parameters or change the data returned by the stored procedure, you still need to update the code in the Assembly to add parameters, update calls, and so on, at this time, it may be complicated.

3. complex development and debugging. Due to ide problems, development and debugging of stored procedures are more difficult than general programs.

4. No way to apply cache. Although global temporary tables and other methods can be used for caching, they also increase the burden on the database. If the cache concurrency is serious and locks are often required, the efficiency is worrying.

5. clusters are not supported, database servers cannot be horizontally scaled, or database cutting (horizontal or vertical cutting ). After the database is cut, the stored procedure does not know which database the data is stored in.

Summary

1. Proper use of stored procedures can improve the performance of SQL queries,

2. The stored procedure should not be used or abused on a large scale.

3. With the emergence of many Orm, many advantages of stored procedures are no longer obvious.

4. the biggest disadvantage of SQL is the limitation of the SQL language itself-SQL itself is a structured query language, we should not use stored procedures to process complex business logic-so that SQL can return to its "Structured Query Language" function. The complicated business logic should be handed over to the Code for processing.