How to write and optimize SQL Server stored procedures

[Introduction] In the development process of database, we often encounter complex business logic and operations on the database. At this time, SP will be used to encapsulate the database operations. If there are many SPs in the project and there are no certain standards for writing, it will make it difficult to maintain the system in the future and make it difficult to understand the logic of the large SPs. In addition, during the development process of the database, complex problems will often be encountered. For business logic and database operations, SP will be used to encapsulate database operations at this time. If the project has many SPs and the writing is not standardized, it will make it difficult to maintain the system in the future and make it difficult to understand the logic of the large SPs. In addition, if the amount of data in the database is large or the project has high performance requirements for the SPs, you will encounter It is a problem of optimization, otherwise the speed may be very slow. Through personal experience, an optimized SP is even hundreds of times more efficient than a SP with poor performance.

Details:
1. If developers use Tables or Views from other libraries, they must create Views in the current library to implement cross-library operations. It is best not to use them directly. "databse.dbo.table_name", because sp_depends cannot display the cross-database table or view used by the SP, which is inconvenient for verification.

2. Before submitting SP, developers must have used set showplan on to analyze the query plan and conduct their own query optimization checks.

3. To improve program operation efficiency and optimize applications, you should pay attention to the following points during the SP writing process:

(a) SQL usage specifications :

i. Try to avoid large transaction operations and use the holdlock clause with caution to improve system concurrency.

ii. Try to avoid repeatedly accessing the same table or tables, especially tables with a large amount of data. You can consider extracting data into a temporary table based on conditions first, and then making a connection.

iii. Try to avoid using cursors, because cursors are less efficient. If the data operated by the cursor exceeds 10,000 rows, it should be rewritten; if a cursor is used, try to avoid cursor loops. Then perform the table join operation.

iv. Pay attention to the writing of where clauses. The order of statements must be considered. The order of conditional clauses should be determined according to the index order and range size. Try to make the field order consistent with the index order and range. From big 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 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 when counting all rows in the table, and count(1) is more convenient than count(*). Efficient.

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

viii. Pay attention to the replacement between some or clauses and union clauses

ix. Pay attention to the data types of connections between tables and avoid differences between different types of data. Connection.

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

xi. Pay attention to the data volume of insert and update operations to prevent conflicts with other applications. If the amount of data exceeds 200 data pages (400k), the system will upgrade the lock, and the page-level lock will be upgraded to a table-level lock.

(b) Index usage specifications:

i. The creation of indexes should be considered in conjunction with the application. It is recommended that large OLTP tables should not exceed 6 indexes.

ii. Use index fields as query conditions as much as possible, especially clustered indexes. If necessary, you can use index index_name to force the index to be specified

iii. Avoid pairing Perform table scan when querying large tables, and consider creating new indexes if necessary.

iv. When using an index field as a condition, if the index is a joint index, then the first field in the index must be used as the condition to ensure that the system uses the index, otherwise the The index will not be used.

v. Pay attention to index maintenance, periodically rebuild indexes, and recompile stored procedures.

(c) Tempdb usage specifications:

i. Try to avoid using distinct, order by, group by, having, join, cumute , because these statements will increase the burden on tempdb.

ii. Avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

iii. When creating a temporary table, if the amount of data inserted at one time is large, you can use select into instead of create table to avoid logs and improve speed; if the amount of data is not large, in order to ease the system For table resources, it is recommended to create table first and then insert.

iv. If the temporary table has a large amount of data and needs to be indexed, the process of creating the temporary table and indexing should be placed in a separate sub-stored procedure to ensure that the system can easily It is better to use the index of the temporary table.

v. If temporary tables are used, all temporary tables must be explicitly deleted at the end of the stored procedure. First truncate the table, and then drop the table. This can avoid long-term locking of system tables. .

vi. Use caution when querying and modifying connections between large temporary tables and other large tables to reduce the burden on system tables, because this operation will use the tempdb system table multiple times in one statement.

(d) Reasonable algorithm use:

Based on the SQL optimization technology mentioned above and the SQL optimization content in the ASE Tuning manual, combined with practical applications, multiple algorithms are used for comparison to obtain the method that consumes the least resources and is the most efficient. Specific ASE tuning commands are available: set statistics io on, set statistics time on, set showplan on, etc.

The above is the detailed content of How to write and optimize SQL Server stored procedures. For more information, please follow other related articles on the PHP Chinese website!