Do applications need to use stored procedures?

Question proposal

When you are developing a database-based application, you may wonder where the transaction operations related to the database should be stored? Is stored in the database as a stored procedure, or is the query and related operations embedded in the application? To answer this question, you first need to understand the advantages and disadvantages of the stored procedure and embedded T-SQL and their respective application scenarios. Especially in the new. NET development environment, it is critical to select the correct solution.

Why use stored procedures )?

You may already have some experience in database programming and are familiar with sqlcommand objects. But have you ever wondered whether your database-related operations comply with the optimization principles? Should those relatively complex data processing be embedded in the application, or should they be encapsulated in the stored procedure and placed on the Database End?
Before further discussion, let's briefly review the concept of stored procedure.
Stored procedures are a set of T-SQL statements that are stored together to form a T-SQL program. During running, you can pass in some parameters. You can get either result set or output parameters ), return Value ). When a stored procedure is executed for the first time, the database system must first analyze and compile it. After compilation, an execution plan is obtained ). The execution plan is the process record of the specific steps in which the database executes the stored procedure. The compiled execution plan is placed in the cache pool of the database for future use. If the stored procedure is called again in the future, the database will retrieve the execution plan from the cache pool for running. This avoids repeated analysis and compilation of the stored procedure, thus improving the database performance. (The execution plans in these cache pools will be kept until the database is restarted or the system memory is insufficient and the cache pool is cleared ).
Whether to use stored procedures can be analyzed in the following three aspects.

1. Performance)
In the past, stored procedures had performance advantages over queries. The reason is that the database caches the execution plan of the stored procedure ). However, in Versions later than MySQL 7.0, the query execution plan is also stored in the cache pool by the database. In this way, the traditional advantages of stored procedures no longer exist. As long as your query statement is static and you often use it, it will not be cleared out of the cache pool by the database. If its execution plan is reused, there is no performance difference between queries and stored procedures theoretically. Note that the query statement must be static, therefore, the matching execution plan may not be found in the cache pool. The query has to be re-compiled by the database, which will lead to performance loss.
However, in terms of network transmission, stored procedures are still advantageous over queries. Because to use stored procedures, you only need to pass the name and necessary parameters of the stored procedure to the database, instead of transmitting all query statements as you would in the query. If the query logic is complex, the query statement size will also be considerable. In addition, designing a proper stored procedure can reduce the number of rounds between the client and the database, or even once.
In addition, the reuse of execution plans can be enhanced by executing database Stored Procedures Using Remote Procedure Calls (RPC) to improve performance. When you specify sqlcommand. commandtype as storedprocedure, the stored procedure is executed through rpc. RPC wraps parameters and then calls the database stored procedure to make it easy for the database engine to find matching execution plans. You can use different parameters when calling the stored procedure. The database system will use the same execution plan.
When deciding whether to use stored procedures, you also need to determine whether your specific operations take advantage of the advantages of stored procedures. Overall:

Set-based computing is the strength of T-SQL.
"Row-based and string-based operations (string manipulation) are not the strengths of T-SQL. You should avoid this operation at least before the next version of Yukon comes out.

That is to say, some operations are performed in the advanced language of the application, which is more effective than accessing the database. For example, complex string processing. At this time, putting all the operations into the stored procedure is not an optimal solution. You may need to divide the tasks reasonably so that the databases and applications can complete the tasks they are good.

2. maintainability and abstract action)
Another potential benefit of using stored procedures is good maintainability. Although we hope that the database structure will never change and the transaction processing rules will never change, in fact this is unlikely to happen. For many changes, you may only need to change the specific implementation of the stored procedure. All client programs that use it do not need to be modified, debugged, or compiled. In this way, many changes are transparent to the customer program ). In most cases, this approach is often the most effective and simple.
In addition, by abstracting the specific implementation (implementation) and placing the T-SQL statement in the stored procedure, any client caller can access the data in a unified form. Globally, there is only one implementation of a data operation, which is placed in one place. In this way, changes and maintenance are very convenient. Different users will always get the same result.
Another advantage of maintaining stored procedures is that you can have better program version control. You can use version control software to help you maintain the stored procedure, just as you maintain other source programs. For example, you can use Microsoft Visual sourcesafe? To help you do this. In this way, you can easily retrieve any previous version of the stored procedure.
It should be noted that using stored procedures cannot prevent you from modifying the database structure and transaction processing rules. If the changes are relatively large and you need to redesign the input parameters or return values, you will need to modify the program segment that the client calls these stored procedures.
You should consider that using stored procedures to encapsulate your transaction processing logic will affect the portability of applications. Stored procedures are bundled with SQL databases. If you want to change the database platform, you may need to rewrite these stored procedures. If portability is critical to your application, it is better to place the transaction processing logic in the middle layer (middle-tier) of the database system neutral (RDBMS-neutral.

3. Security)

The last reason for using the stored procedure is that it enhances the security of the database system.
From the perspective of managing user access information, it ensures that users can access specific data by allowing users to access certain stored procedures. This is an indirect data access, instead of opening a data warehouse table directly to users. In fact, we can imagine the stored procedure as a view of the database system. The only difference is that stored procedures can change parameters to dynamically change the results.
Stored procedures can also improve program security. It can guard against an attack called SQL injection (SQL injection attacks)-This attack mainly uses the and or operator to concatenate commands after valid input parameters. Stored procedures can also hide transaction processing rules on the database, rather than on the client program. In some cases (such as intellectual property rights), such hiding is very important.
In addition, using Stored Procedures allows you to use the sqlparameter class provided by ADO. net. You can use this class to describe the specific parameter type. This makes it easier for you to verify that the parameters entered by the user are valid. Parameters are equally important for stored procedures and in-line queries. They can reduce user input to a very small range.
Of course, using stored procedures does not mean you can rest assured about security. In fact, poor programs and database management vulnerabilities can still put you under possible attacks. If the role of SQL database is created and improperly authorized, users can access data that they should not have accessed. Similarly, using stored procedures alone cannot completely prevent SQL injection attacks.
In addition, the use of sqlparameter class to verify user input is not absolutely safe, whether in the background T-SQL write stored procedures or embedded in the application of the query, all user input, in particular, string-type data must be validated before being processed by the database engine.

Does Stored Procedure apply to you?

To sum up, using stored procedures has the following advantages:

"Improved new capabilities and reduced network traffic
"Single Point of maintenance)
"Abstract and generalize business logic, enhancing consistency and security
"Reduces the chance of some possible malicious attacks
"Encourage Execution Plan re-use)

If your application can effectively take advantage of the above advantages of stored procedures, you should try your best to use them. However, if your application requires high portability or the database structure changes greatly and cannot be relatively stable, you may have to try other methods. For example, if you are developing an early feasibility verification program for users in the SQL database, and users are likely to use MySQL, Oracle, and other databases in the future, you should avoid using the stored procedures of the SQL database, use the database operation statements embedded in the program. In this way, when you change the database platform, you can greatly ensure that the program is not affected.
In addition, you need to consider the technical issues of using stored procedures. Maybe you and your staff are very unfamiliar with Stored Procedure programming and have no time to master it quickly. You also need to consider these factors. As mentioned above, database stored procedures are good at set-based operations, rather than Row-based operations. If you do not have a good understanding of the stored procedure, improper use of it may lead to poor execution performance. So if you decide to use a stored procedure, it is necessary to spend more time learning it.