Advantages and disadvantages of using stored procedures in system development

In system development, it is common to use stored procedures to complete some functions. Because of high performance requirements or large data volume operationsProgramming LanguageDevelopment has considerable performance advantages. I have seen that the stored procedures used by a system can be described in many ways. Basically, all operations are stored in databases. When I first came into contact with the system, I admired the system's designers and DBAs. He has been playing with SQL. He writes SQL directly in notepad, and then writes hundreds of lines of scripts. Basically, he does not need to debug and run the script.

However, when I understand the business of the system and perform secondary development, I find it very painful. Because errors occur during the storage process, they cannot be easily tracked as in Visual Studio, you can skip this step. We can only print or raiserror ('',) to solve this problem. It is a matter of patience. At the same time, I also found that the bug in the stored procedure is easy to release once it is solved, and no release is required.Program.

The advantages of listing stored procedures are as follows:

1. High execution efficiency.

2. Good security performance.

3. It is very easy to implement requirements for some occasions.

Disadvantages:

1. Poor maintainability.

2. Poor readability.

When we are modifyingCodeWe can search all through vs to confirm that unnecessary code can be deleted, but the stored procedure is not that easy. Of course, you can search all the stored procedures of the database, however

Those database jobs or other databases may use this stored procedure. You are not so easy to confirm.

Therefore, I personally think that the storage process should not be abused during system development, and it is troublesome to use more maintenance later, some operations that can be implemented in the Code and have little impact on the system performance do not need to be written in the stored procedure, and

To solve the issue of easy release, you can consider completing some business operations on the server and replacing some stored procedures with services.