SQL Server stored procedure writing and optimization

1. Suitable readers: Database development programmers, project developers who have a large amount of database data and are involved in the optimization of SP (stored procedures), and people who have a strong interest in databases.

2. Introduction: In the process of database development, complex business logic and database operations are often encountered. At this time, SP will be used to encapsulate database operations. If there are many SPs in the project and there is no certain standard for writing, it will affect the difficulty of future system maintenance and the difficulty of understanding the logic of large SPs. In addition, if the amount of database data is large or the project has high performance requirements for SPs, optimization problems will be encountered, otherwise the speed may be very slow. According to personal experience, an optimized SP is even hundreds of times more efficient than an SP with poor performance.

3. Contents:

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 "databse.dbo.table_name" directly, because sp_depends cannot display the cross-library tables or views used by the SP, which is inconvenient for verification.

2. Developers submit

Before executing the command, you must have used set showplan on to analyze the query plan and performed your own query optimization checks.

3. To improve program running efficiency and optimize application programs, the following points should be noted during SP writing:

a) SQL usage specifications:

i. Avoid large transaction operations as much as possible and use the holdlock clause with caution to improve the system's concurrency capabilities.

ii. Try to avoid repeatedly accessing the same table or several tables, especially tables with large amounts of data. You can consider extracting data into a temporary table based on conditions and then join it.

iii. Avoid using cursors as much as possible, as cursors are less efficient. If the data being operated by a cursor exceeds 10,000 rows, it should be rewritten. If a cursor is used, avoid table joins in the cursor loop as much as possible.

iv. Pay attention to the writing of the where clause. The statement order must be considered. The order of the 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 the range from large to small.

v. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" 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 to count the number of rows in a table, and count(1) is more efficient than count(*).

vii. Try to use “>=” instead of “>”.

viii. Note the substitutions between some or clauses and union clauses

ix. Pay attention to the data types when connecting between tables and avoid connections between different types of data.

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

xi. Pay attention to the amount of data in 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 from page-level lock to table-level lock.

b) Index usage specifications:

i. The creation of indexes should be considered in conjunction with the application. It is recommended that a large OLTP table should not have more than 6 indexes.

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

iii. Avoid table scans when querying large tables, and consider creating new indexes when necessary.

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

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

c) Tempdb usage specifications:

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

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

iii. When creating a new temporary table, if the amount of data to be inserted at one time is large, select into can be used instead of create table to avoid log and increase the speed. If the amount of data is not large, in order to ease the resources of the system table, 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 make good use of the index of the temporary table.

v. If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the stored procedure, first truncate table, then drop table, to avoid locking the system table for a long time.

vi. Be cautious when using large temporary tables to query and modify other large tables to reduce the burden on system tables, because such operations will use the tempdb system tables multiple times in one statement.

d) Reasonable use of algorithms:

Based on the SQL optimization techniques mentioned above and the SQL optimization content in the ASE Tuning manual, combined with actual applications, multiple algorithms are used for comparison to obtain the method with the least resource consumption and the highest efficiency. Specific ASE tuning commands that can be used include: set statistics io on, set statistics time on, set showplan on, etc.