Benefits and precautions of Stored Procedures

Advantages of stored procedures and some precautions advantages and some precautions of stored procedures Author: xue560 Source: blog 2011-09-2613: 51 stored procedures are used every day, the debate on the use of SQL statements in stored procedures has been ongoing. I personally think that using stored procedures is better than using SQL statements. I have sorted out some instructions: stored procedures are made up of some SQL statements and control languages.

Advantages of stored procedures and some precautions advantages of stored procedures and some precautions Author: xue560 Source: blog, the stored procedure is used every day, the debate on the use of SQL statements in stored procedures has been ongoing. I personally think that using stored procedures is better than using SQL statements. I have sorted out some instructions: stored procedures are made up of some SQL statements and control languages.

Benefits and precautions of Stored Procedures

Benefits and precautions of Stored Procedures

Author: xue560 Source: blog

Stored procedures are used every day. The debate on SQL statements used in stored procedures is also ongoing. I personally think that using stored procedures is better than using SQL statements:

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

Depending on the type of the returned value, we can divide the stored procedure into three types: the stored procedure of the returned record set, the stored procedure of the returned value (also known as the scalar stored procedure), and the behavior stored procedure. As the name suggests, the execution result of the stored procedure that returns the record set is a record set. A typical example is to retrieve records that meet one or more conditions from the database; after the stored procedure of the returned value is executed, a value is returned. For example, a function or command with a returned value is executed in the database. Finally, the stored procedure is only used to implement a function of the database, there is no return value, for example, update or delete operations in the database.

Benefits of using Stored Procedures

Compared with using SQL statements directly, calling stored procedures in applications has the following benefits:

(1) reduce network traffic. The network traffic for calling a stored procedure with few rows of data may not be significantly different from that for directly calling SQL statements. However, if the Stored Procedure contains hundreds of rows of SQL statements, therefore, its performance is definitely much higher than that of a single SQL statement.

(2) Faster execution. There are two reasons: first, the database has been parsed and optimized when the stored procedure was created. Second, once the stored procedure is executed, a stored procedure will be retained in the memory, so that the next time you execute the same stored procedure, you can directly call it from the memory.

(3) better adaptability: Because stored procedures access databases through stored procedures, therefore, database developers can make any changes to the database without modifying the stored procedure interface, without affecting the application.

(4) protocol work: the coding work of applications and databases can be performed independently without mutual suppression.

Description on msdn

Considerations for using Stored Procedures

Maybe you have written a T-SQL that uses SqlCommand objects in multiple places, Hong Kong space, but you have never considered whether there is a better place than incorporating it into data access code. Because the application adds some functionality over time, it may contain some complex T-SQL Process Code internally. Stored Procedures provide a replacement location for encapsulating this code.

Most people may already know stored procedures, but for those who do not know stored procedures, Stored Procedures refer to a group of T-SQL statements that are stored together in the database as a single unit of code. You can use input parameters to pass in runtime information and retrieve data that is used as a result set or output parameter. The stored procedure will be compiled at the first run. This generates an execution plan-a record of the steps that Microsoft SQL Server must take to get results specified by the T-SQL during a stored procedure. Then, the execution plan is cached in the memory for future use. This will improve the performance of stored procedures, because SQL Server does not need to re-analyze the code to determine how to process it, but simply reference the cache plan. The cache plan remains available until SQL Server restarts or until it overflows memory due to low usage.

Performance

The cache execution plan has made the stored procedure more advantageous than the query. However, for several latest versions of SQL Server, the execution plan has been cached for all T-SQL batches, regardless of whether they are stored in the stored procedure. Therefore, the performance based on this function is no longer a selling point of stored procedures. Any T-SQL batch processing that uses static syntax and is committed enough to prevent execution plan overflow of memory will have the same performance benefit. The "static" part is critical; any changes, even insignificant changes such as adding comments, will lead to a failure to match the cached plan, and thus will not be able to reuse the plan.

However, stored procedures can still provide performance benefits when they can be used to reduce network traffic. You only need to send the EXECUTE stored_proc_name statement over the network, rather than the entire T-SQL routine, which is widely used in complex operations. A well-designed stored procedure can simplify many round-trips between the client and the server into a single call.

In addition, stored procedures allow you to enhance reuse of execution plans, thereby improving performance by using Remote Procedure Call (RPC) to process stored procedures on the server. When StoredProcedure's SqlCommand. CommandType is used, the stored procedure is executed through RPC. RPC encapsulates parameters and calls server-side processes so that the engine can easily find matching execution plans and insert updated parameter values.

Consider using stored procedures to improve performance, and finally consider whether to make full use of the advantages of T-SQL. Consider how to process data.

Do you want to use collection-based actions or perform other actions that are fully supported in the T-SQL? Therefore, the stored procedure is an option, and the inline query can also be used.

Are you sure you want to perform row-based operations or complex string processing? You may have to reconsider this kind of processing in the T-SQL, which does not include using stored procedures, at least after being published in Yukon and available in the Common Language Runtime Library (CLR) integration.

Maintainability and abstraction

Another potential advantage to consider is maintainability. Ideally, the database architecture is never changed and the business rules are not modified, but in the real environment, the situation is completely different. In this case, you can modify the stored procedure to include data in the new X, Y, and Z tables to support new sales activities, instead of changing this information somewhere in the application code, maintenance may be easier for you. Changing this information during stored procedures makes updates transparent to applications-you still return the same sales information, even if the internal implementation of the stored procedure has been changed. Updating stored procedures usually requires less time and effort than changing, testing, and re-deploying an assembly.

In addition, through abstraction and saving this code in the stored procedure, any application that needs to access data can obtain consistent data. You can obtain consistent information without maintaining the same code in multiple locations.

Another maintainability advantage of storing T-SQL in stored procedures is better version control. You can control the version of scripts used to create and modify stored procedures, just as you can control the version of any other source code module. By using Microsoft Visual SourceSafe or another source code control tool, you can easily recover to or reference older stored procedures.

When using stored procedures to improve maintainability, it is worth noting that they cannot prevent you from making any possible changes to your architecture and rules. If the change range is large and the input stored procedure parameters need to be changed, the US space, or the data returned by the stored procedure needs to be changed, you still need to update the code in the Assembly to add parameters, update GetValue () calls, and so on.

Another issue that should be noted is that because stored procedures bind applications to SQL Server, encapsulating business logic using stored procedures will restrict the portability of applications. If the portability of applications is very important in your environment, it may be a better choice to encapsulate the business logic in an RDBMS-specific middle layer.

Security

The final reason to consider using stored procedures is that they can be used to enhance security.