SQL Server stored procedure Authoring and optimization measures

During the development of the database, the complex business logic and the operation of the database are often encountered, and the SP is used to encapsulate the database operation. If there are more SPS in the project, writing and there is no specification, will affect the future system maintenance difficulties and large SP logic is difficult to understand, in addition, if the data volume of the database or the performance requirements of the SP is very high, you will encounter optimization problems, or speed may be very slow, after personal experience, An optimized SP is hundreds of times times more efficient than a poorly performing sp.
Detailed content:

1, if developers use other libraries of table or view, it is necessary to establish a view in the current library to achieve cross library operations, it is best not to directly use "Databse.dbo.table_name", because sp_ Depends cannot display the cross Library table or view used by the SP, which is inconvenient to verify.

2. Before submitting the SP, the developer must have already analyzed the query plan using SET SHOWPLAN on, and has done its own query optimization check.

3, high program operation efficiency, optimize the application, in the SP writing process should pay attention to the following points:

(a) Usage specification for SQL:

I. Avoid big business operation, use HOLDLOCK clause carefully, improve system concurrent ability.

Ii. try to avoid repeated access to the same or several tables, especially the large data table, you can consider the conditions to extract data to the temporary table, and then do the connection.

Iii. avoid the use of cursors, because the cursor is inefficient, if the cursor operation of more than 10,000 rows of data, it should be overwritten, if you use a cursor, you should try to avoid the cursor loop in the table join the operation.

Iv. Note where the sentence is written, must consider the order of the sentence, should be based on the index order, range size to determine the order of the conditional clauses, as far as possible to make the field order and the index order consistent, ranging from large to small.

V. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the system may not use the index correctly.

VI. Try to use exists instead of select COUNT (1) to determine whether a record exists, the Count function is only used in all the rows in the statistics table, and COUNT (1) is more efficient than count (*).

VII. Try to use ">=" and do not use ">".

Viii. note the substitution between some or clauses and the Union clause

IX. note the data types of the connections between tables to avoid connections between different types of data.

X. Note the relationship between parameters and data types in stored procedures.

XI. Note the amount of data in the INSERT, update operation to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400k), the system will lock up and the page level lock will be upgraded to a table-level lock.

1 2 Next page > full text reading tips: Try "←→" button, turn the page more convenient Oh!