Advantages and disadvantages of stored procedures

The author thinks that the stored procedure is a bunch of SQL merging. The middle adds a bit of logic control.

However, stored procedures are more practical when dealing with complex business.
For example, a complex data operation. If you're working at the front desk. Multiple database connections may be involved. But if you use stored procedures. It's only once. There are advantages in response time.
This means that stored procedures can give us the benefit of increased operational efficiency.
In addition, the program prone to BUG instability, and stored procedures, as long as the database does not appear to be a problem, basically there is no problem. In other words, a system that uses stored procedures is more stable in terms of security.

Small amounts of data, or money-related projects without stored procedures can also function properly. MySQL's stored procedures are yet to be tested. If it is a formal project, it is recommended that you use SQL Server or Oracle stored procedures. The process of dealing with data is much faster than the program. The interviewer asked if there was any storage, and actually wanted to know if the programmer who came to the interview had done a large amount of data. If the training comes out, or small projects small companies out of the storage is definitely less contact.

Therefore, in order to enter a large company, no rich experience in the storage process, is not.

So when can I use storage? Is there no need for small projects where the volume of data is not very large and the business process is not very complex?
Wrong. Stored procedures are not just for large projects, but for small and medium-sized projects, it is also necessary to use stored procedures. Its power and advantages are mainly reflected in:
1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can increase the speed of the database execution.
2. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database. These operations, if done with a program, become a single SQL statement that may be connected to the database multiple times. Instead of storage, you only need to connect to the database once.
3. Stored procedures can be reused to reduce the workload of database developers.
4. High security, can be set only a certain user has the right to use the specified stored procedure.

This article from Csdn Blog, reproduced please indicate the source:http://blog.csdn.net/defonds/archive/2009/07/15/4349922.aspx

The use of stored procedures seems to have been an argument.
I'm not inclined to use stored procedures as much as possible, so I think:
1. Operating speed: Most advanced database systems have statement cache, so the cost of compiling SQL has no effect. However, executing a stored procedure is more expensive than executing SQL directly (checking permissions, etc.), so there is no advantage to the stored procedure for very simple SQL.
2. Network Load: If there are no multiple data interactions in the stored procedure, the network traffic is actually the same as the direct SQL.
3. Team development: Unfortunately, compared to the mature IDE, there is no good storage procedure for IDE tools to support, i.e., these must be done manually.
4. Security mechanism: For the traditional C/s structure, the user connecting the database can be different, so the security mechanism is useful; but in the three-tier architecture of the Web, the database user is not for the user, so basically, there is only one user with all the permissions (there is at most one development user). At this point, the security mechanism is a bit superfluous.
5. User satisfaction: In fact, this is only to access the database interface Unified, is the use of stored procedures, or EJB, not much of a relationship, that is, in the three-tier structure, the design of a separate data access layer, the same can achieve this goal.
6. Development and debugging: As a result of the IDE's problems, the development and debugging of stored procedures is more difficult than the general program (old version DB2 can only use c write stored procedures, but also a disaster).
7. Portability: Forget it, this does not need to mention, anyway the general application is always bound to a database, otherwise you can not rely on the optimization of database access to improve performance.
8. maintainability: Indeed, stored procedures are sometimes easier to maintain than programs, because they can update the DB-side of the stored procedures in real-time, but in the 3-tier structure, updating the server-side data access layer can achieve this goal, but now many platforms do not support real-time updates.

From the above can be known that the use of stored procedures can not be dead (full use, or all do not), previously terminal-server, Client-db Way is outdated, many advantages of the stored procedure is not obvious.
Now, I think the principle is: all data access in the application layer is encapsulated as the data access layer, where, if SQL is simple, directly with SQL, if SQL is complex, or data interaction is many and intermediate data is not used at last, use stored procedures. Other by experience.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/zy1691/archive/2009/01/09/3742780.aspx

A stored procedure is a encapsulated process consisting of some SQL statements and control statements that reside in a database, can be called by a client application, or can be called from another procedure or trigger. Its parameters can be passed and returned. Similar to function procedures in an application, stored procedures can be called by name, and they also have input and output parameters.

Depending on the type of return value, we can divide the stored procedure into three categories: a stored procedure that returns a recordset, a stored procedure that returns a value (also known as a scalar stored procedure), and a behavior stored procedure. As the name implies, the execution result of a stored procedure that returns a Recordset is a recordset, a typical example of retrieving a record from a database that matches one or several conditions; Returns a value after the stored procedure for the value is executed, such as executing a function or command with a return value in the database; The behavior stored procedure is used only to implement a function of the database, and there is no return value, such as update and delete operations in the database.

Benefits of using Stored procedures

Invoking a stored procedure directly in an application with respect to directly using SQL statements has the following benefits:

(1) Reduce network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, the performance is definitely much higher than a single call to the SQL statement.

(2) Execution speed is faster. There are two reasons: first, when the stored procedure is created, the database has been parsed and optimized once. Second, once the stored procedure is executed, a copy of the stored procedure is kept in memory so that the next time the same stored procedure is executed, it can be called directly from memory.

(3) Stronger adaptability: Because the stored procedure accesses the database through stored procedures, the database developer can make any changes to the database without altering the stored procedure interface, and these changes do not affect the application.

(4) Fabric work: The coding of applications and databases can be done independently, without suppressing each other.

Advantages:
1. The T-SQL process code becomes more complex as the application changes over time, adding and removing functionality, and StoredProcedure provides a replacement location for encapsulating this code.

2. Execution plan (the stored procedure will be compiled on first run, which results in an execution plan-in fact, a record of the steps Microsoft SQL Server must take to get the results specified by T-SQL in the stored procedure. ) cache to improve performance.
........ However, the new version of SQL Server, the execution plan has been cached for all T-SQL batches, regardless of whether they are stored in the process, so there is no comparative advantage.

3. Stored procedures can be used to reduce network traffic and stored procedure code is stored directly in the database, so there is no code traffic for a large number of T-SQL statements.

4. Using stored procedures enables you to enhance the reuse of execution plans, thereby improving performance by using remote Procedure calls (RPC) to process stored procedures on the server. The RPC encapsulation parameters and the way the server-side procedure is invoked make it easy for the engine to find a matching execution plan and simply insert the updated parameter values.

5. With high maintainability, updating stored procedures typically requires less time and effort than changing, testing, and redeploying assemblies.

6. The code is streamlined and consistent, and a stored procedure can be used in different locations of the application code.

7. Better version control, by using Microsoft Visual SourceSafe or some other source control tool, you can easily revert to or reference an older version of a stored procedure.

8. Enhanced Security:
A, by granting users access to stored procedures (rather than tables), they can provide access to specific data;
b, improve code security, prevent SQL injection (but not completely resolved, for example, the data manipulation language--dml, attached to the input parameters);
The C, SqlParameter class specifies the data type of the stored procedure parameter, which, as part of a deep-seated defensive strategy, validates the user-supplied value type (but is not foolproof or should be passed to the database for additional validation).

Disadvantages:

1. If you change the scope to make changes to the parameters of the input stored procedure, or if you want to change the data returned by it, you still need to update the code in the assembly to add parameters, update the GetValue () call, and so on, which is more tedious to estimate.

2. Portability is poor

Because stored procedures bind applications to SQL Server, using stored procedures to encapsulate the business logic limits the portability of the application. If the portability of your application is important in your environment, encapsulating the business logic in an RDBMS-neutral middle tier might be a better choice.


The stored procedure can be used by all programming languages and programs that connect to this database, and the custom function cannot.

The difference is two, stored procedures can have database management software modification, so that multi-layered structure program adjustment system logic, do not need to compile and distribute programs.

The difference is that the stored procedure executes without causing network traffic and does not occupy the memory and CPU resources of the program server.

Advantages and disadvantages of stored procedures