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 difficult to understand, and if the data volume of the database is large or the performance requirements of the project have, 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.
four,
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, optimization applications, should pay attention to the following: a) SQL usage specification: i. Try to avoid big transaction operations, use the Holdlock clause to increase 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 that some OR clauses and UNION clauses are replaced between IX. Note that the data type connected is connected to avoid connections 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) Normative specification of the index: i. Creation of the index To consider the application, it is recommended that the big OLTP table should not exceed 6 indexes. II. Use the index field as a query condition, especially the cluster index, can force the specified index III by index index_name if necessary. Avoiding a Table Scan for a large table query, consider a new index when 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's usage specification: i. Try to avoid using Distinct, Order By, Group By, Having, Join, 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.
