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. Four, content: 1. If the developer uses other library Table or View, be sure to create a view in the current library to implement cross-library operations, it is best not to use "Database.dbo.table_name" directly, because sp_depends cannot display it The span Table or View used by SP 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.
