Stored procedure (stored procedure) Writing experience and optimization
First, in the foreword: After a period of storage process development, some summary and experience were shared with everyone, I hope to be beneficial to everyone, mainly for Sybase and SQL Server databases, but other databases should have some commonality.
Second, suitable for the reader object: Database development programmer, database data volume involves an optimized project developer for SP (stored procedures), people with strong interest in the database.
Third, introduction: In the development of the database, complex business logic and the operation of the database are often encountered. At this time, the database operation is encapsulated. If the project's SP is more, there is no specification, and it will affect the future system maintenance difficulties and the difficult to understand of the large SP logic. In addition, if the data volume of the database is large or the project's performance requirements of the project, it will encounter Optimization issues, otherwise the speed may be very slow, after personal experience, an optimized SP is more than one hundred times higher than the efficiency of the Poor SP.
Fourth, content:
1. If the developer uses the Table or View used to other libraries, it is important to create a cross-library operation in the current library. It is best not to use "Database.dbo.table_name" directly because sp_depends cannot display the SP. Cross library Table or View, it is inconvenient to check.
2. Developers must have used SET SHOWPLAN ON to analyze the query plan, and have been used to optimize inspection.
3, high program operation efficiency, optimize applications, should pay attention to the following points during SP writing:
a) SQL usage specification:
i. Try to avoid big business operations, use the HOLDLOCK clause to improve the system.
II. Try to avoid repeated access to the same or several tables, especially those with large data volume, can consider extracting data into the temporary table according to the conditions, and then connect.
III. Try to avoid using a cursor, because the efficiency of the cursor is poor, if the data of the cursor exceeds 10,000, then it should be rewritten; if a cursor is used, it is necessary to avoid the operation of the table connection in the cursor cycle.
IV. Note that WHERE Form Writings must consider the order of statement, and should determine the front-rear order of the condition clauses according to the index order and range size, as much as possible to make the field order consistent with the index sequence, and the range is from large to small.
v. Do not perform functions, arithmetic operations, or other expression operations on the "=" on the WHERE clause, otherwise the system may not use indexes correctly.
Vi. Try to use exists instead of Select Count (1) to determine whether there is a record, the count function is only used when all the number of rows in the statistics table, and count (1) is more efficient than count (*).
VII. Try to use "> =", do not use ">".
VIII. Note some alternatives between OR clauses and UNION clauses
IX. Note that the data type connection between the tables is avoided between different types of data.
x. Note the relationship between parameters and data type during storage.
Xi. Note the amount of data for INSERT, UPDATE, prevent conflicts with other apps. If the amount of data exceeds 200 data pages (400K), the system will be upgraded, and the page lock will be upgraded to a table-level lock. b) Norm specification of the index:
i. The creation of indexes should be considered with the application, and the big OLTP table should not exceed 6 indexes.
II. Use the index field as much as possible as query conditions, especially clustered indexes, if necessary, can force the specified index by index index_name.
Iii. Avoiding Table Scan when querying a big table, consider new indexes if necessary.
IV. When using the index field as a condition, if the index is a federated index, the first field in this index must be used as a condition to ensure that the system can be used to use the index, otherwise the index will not be used.
v. Pay attention to the maintenance of the index, periodically reconstruct the index, recompile the stored procedure.
c) Tempdb usage specification:
i. Try to avoid using Distinct, ORDER BY, GROUP BY, HAVING, JOIN, and Cumpute, because these statements will increase the burden of TEMPDB.
II. Avoid frequently create and delete temporary tables to reduce the consumption of system table resources.
III. When new temporary table is new, if the amount of data in one time is large, you can use Select Into instead of CREATE TABLE, avoid log, increase the speed; if the data is not large, in order to mitigate the resources of the system table, it is recommended to first create Table first. Then INSERT.
IV. If the data volume of the temporary table is large, the index needs to be established, then the process of creating a temporary table and the establishment index should be placed in a single sub-storage process so that the system can be used very well to the index of the temporary table. .
v. If you use a temporary table, you must explicitly delete all temporary tables, first truncate Table, then Drop Table, which avoids longer-time locking of the system table.
Vi. Careful use of large temporary tables with other big tables of connection query and modification, reduce system table burden, because this operation will use Tempdb system tables many times in a statement.
d) Reasonable algorithm:
According to the SQL optimization techniques described above and the SQL optimization content in the ASE Tuning manual, combine the actual application, using a variety of algorithms to compare the minimum, and the highest efficiency is the highest. Specific ASE Tune Command: Set Statistics Io ON, SET STATISTICS TIME ON, SET SHOWPLAN ON, etc.
