Storage process writing experience and optimization measures

1. Suitable for readers: database developers, who have a large amount of data in the database and who are interested in optimizing the SP (stored procedure.

II. Introduction: complex business logic and database operations are often encountered during Database Development. In this case, SP is used to encapsulate database operations. If there are many SP projects and there is no certain specification for writing, it will affect the difficulties of system maintenance and the difficulty of understanding the big SP logic in the future, in addition, if the database has a large amount of data or the project has high performance requirements for the SP, you will encounter optimization problems. Otherwise, the speed may be slow. After hands-on experience, an optimized SP is hundreds of times more efficient than an optimized SP with poor performance.

III. Content:
1. If developers use tables or views of other databases, they must create a View in the current database to perform cross-database operations. It is best not to directly use "databse. dbo. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

2. Before submitting the SP, the developer must have used set showplan on to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:
A) SQL usage specifications:
I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

Iii. avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop.

Iv. note that when writing where statements, the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

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

Vi. use exists instead of select count (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

Vii. Try to use "> =" instead of "> ".

Viii. Note the replacement between the or clause and the union clause.
Ix. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.