Sybase's experience and methodology in writing stored procedures

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