Stored procedure authoring experience and optimization measures

Stored Procedures | optimizing
First, preface: After a period of storage process development, write down some of the development of the summary and experience to share with you, hope for everyone's benefit, mainly for Sybase and SQL Server database, but other databases should have some commonality.

Second, suitable for the reader object: Database development programmer, database data volume, involving the SP (stored procedure) optimization of the project developers, the database has a strong interest in people.

Introduction: In the database development process, often encounter complex business logic and the operation of the database, this time will use the SP to encapsulate database operations. 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.

Four, 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.

b The usage specification of the index:

I. Index creation to be considered in conjunction with the application, it is recommended that large OLTP tables not exceed 6 indexes.

Ii. use indexed fields as much as possible as query criteria, especially clustered indexes, which can be enforced by index index_name if necessary

Iii. Avoid table scan when querying large tables and consider new indexes if necessary.

Iv. when using an indexed field as a condition, if the index is a federated index, the first field in the index must be used as a condition to ensure that the index is used by the system, otherwise the index will not be used.

V. To pay attention to the maintenance of the index, periodically rebuild the index, recompile the stored procedure.

(c) Specification for use of tempdb:

I. Try to avoid using distinct, order BY, group BY, have, join, ***pute, because these statements will aggravate the burden of tempdb.

Ii. avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

Iii. in creating a new temporary table, if the amount of data inserted at a time is large, you can use SELECT INTO instead of CREATE table, to avoid log, improve speed, if the amount of data is small, in order to ease the resources of the system table, we recommend create table first, and then insert.

Iv. if the data for a temporary table is large and needs to be indexed, the process of creating a temporary table and indexing should be placed in a separate child stored procedure to ensure that the system is able to use the index of the temporary table well.

V. If you use a temporary table, be sure to explicitly delete all temporary tables at the end of the stored procedure, truncate the table, and then drop the table, which avoids the longer locking of the system tables.

Vi. careful use of large temporary tables and other large table connection query and modification, reduce the burden of the system table, because this operation will be in a statement many times using the system table of tempdb.

d) Reasonable algorithm use:

Based on the SQL optimization described above and the SQL optimization content in the ASE Tuning manual, a number of algorithms are used to compare with each other in order to obtain the most efficient and least resource consuming method. Specific ASE tuning commands are available: SET STATISTICS IO on, SET statistics time on, set SHOWPLAN on, etc.