Analyzing the usage scenarios for stored procedures

A stored procedure is considered when a transaction involves multiple SQL statements or when it involves the operation of multiple tables; It is important to consider the complexity of business logic when a transaction is completed (for example, the operation of multiple data, the determination of multiple states, and so on), as well as the more complex statistics and summaries. However, excessive use of stored procedures can reduce the portability of the system.

For easy control of the system, for example, when the system is tuned, it is only necessary to make changes to the background stored procedures without having to change the client program. There is no need to reinstall the client application.

Stored procedures are not just for large projects, but for small and medium projects, it is also necessary to use stored procedures. Its power and advantages are mainly embodied in:

1. Stored procedures are compiled only when created, and each time a stored procedure is executed without recompiling, and the general SQL statements are compiled every time, so using stored procedures can improve database execution speed.

2. When complex operations are performed on the database (for example, when multiple tables are update,insert,query,delete), this complex operation can be encapsulated in a stored procedure and used in conjunction with transaction processing provided by the database. These operations, if done with a program, become a line of SQL statements that may have to connect to the database multiple times. Instead of storage, you just need to connect to a database once.

3. Stored procedures can be reused to reduce the workload of database developers.

4. High security, you can set only a certain user to have the right to use the specified stored procedures.

Advantages:

1. Fast speed. Especially for the more complex logic, reduce the consumption between network traffic

I have the process and function to reach hundreds of lines, a miniature compiler, I believe that the use of the program is even more troublesome.

2. Write program is simple, use stored procedure call class, call any stored procedure as long as 1-2 lines of code.

3. Easy to upgrade and maintain

4. Debugging is actually not troublesome, you can use Query Analyzer

5. If all the data logic is placed in the stored procedure, then asp.net only need to be responsible for the display of the interface ah what, the most likely error is in the stored procedure. That's the kind of situation that I've met in general.

Disadvantages:

1. Portability is poor, I have been using SQL Server development, but if you want to sell their own things, found that they are simply in the help of Ms selling things, hehe. Want to replace MySQL, do transplant trouble.

2. Using a stored procedure call class, you need to perform two call operations, one from SQL Server to take the parameters of the process and establish parameters, and the second is to call this procedure. One more consumption.

However, this shortcoming can improve performance by pouring all process parameter information into an XML file after project development is complete and process parameters are fully determined.

It is more appropriate to use stored procedures when a business is processing multiple tables at the same time.

Using stored procedures generally improves performance because the database optimizes the data access plan for the stored procedure and applies caching to facilitate subsequent queries;

Stored procedures are protected separately from the database. The client can get permission to execute the stored procedure without having to set other access rights on the underlying concrete table;

Stored procedures make maintenance easier because it is often easier to modify a stored procedure than to modify it in a published component.

Stored procedures Add an extra layer of abstraction to the underlying data format. Enables the client using the stored procedure to isolate the implementation details of the stored procedure and the underlying data format;

Stored procedures can mitigate network bandwidth because you can execute SQL statements in bulk rather than sending a request for a overload from the client.

Complex data processing using stored procedures, such as some report processing, multiple conditions, multiple table joint query, and do pagination processing.