Optimize the database of the database: ================= 1, the key field establishes an index. 2. Use the stored procedure that makes SQL more flexible and efficient. 3, back up the database and clear spam. 4. Optimization of SQL statement syntax. (You can use Sybase's SQL EXPERT, but I didn't find the serial number of UNEXPIRED) 5, clean up the log. The principle of SQL statement optimization: ============================================================================================================================================================================================================================================================ The index established by default is a non-clustered index, but sometimes it is not optimal. Under the non-clustered index, the data is physically randomly stored on the data page. Reasonable index design is based on analysis and prediction of various queries. Generally, 1. There is a large number of repetition values, and often have a range query (Between,>, <> =, <=) and the column that occurred in the Order By, which can consider establish a cluster index; 2. Always save Take more columns, and each column contains a repetition value to consider establish a combined index; 3. Combined index should try to make a critical query to form an index coverage, and the leading list must be the most frequent column. Although the index helps to improve performance but not indexes, the better, just the opposite index will cause the system to inefficient. Users are added to each index in the table, and the maintenance index collection must do corresponding updates. 2, is NULL and IS NOT NULL cannot use NULL to index, and any columns containing NULL values will not be included in the index. Even if the index has multiple columns, as long as there is a null in these columns, the column is excluded from the index. That is to say, if there is a null value, even if the index of the index is not improved. Any statement optimizer using is NULL or IS NOT NULL in the WHERE clause is not allowed to use indexes. 3, IN and EXISTS EXISTS are far more efficient than IN. Inside is related to Full Table Scan and Range Scan. Almost all In operator sub-queries are rewritten to subquery using Exists. 4. Try to transform less than the format when query. 5. In SQL Server 2000, if the stored procedure has only one parameter, and is an Output type, you must give this parameter an initial value when calling this stored procedure, otherwise the call error will occur. 6. ORDER BY and GROPU BY uses ORDER BY and Group BY phrases, any index helps SELECT performance improvement. Note If there is a NULL value in the index column, Optimizer will not be optimized. 7, any of the listed operations will cause a table scan, including database functions, calculation expressions, and so on, move as much as possible to the right right when query. 8, in, OR clauses often use worksheets to make indexs. If a large number of repetition values are not generated, you can consider unpacking the clause. An index should be included in the unpublished clause. 9. SET Showplan_all ON View executions. DBCC Checks database data integrity. DBCC (Database Consistency Checker) is a set of programs for verifying SQL Server database integrity. 10. Cautious use of the cursor in some cases that must use the cursor, consider transferring the eligible data line into the temporary table, and then operates the temporary table definition cursor, so that performance is significantly improved. Summary: The so-called WHERE clause utilizes an index, and a table scan or additional overhead occurs. Experience shows that the maximum improvement of SQL Server performance benefits from logical database design, index design, and query design.
Conversely, the biggest performance problem is often caused by the shortcomings of these in the same aspects. In fact, the essence of SQL optimization is the statement that can be identified with an optimizer, reducing the number of I / O times the table scan, and try to avoid the occurrence of table search. In fact, the performance optimization of SQL is a complex process. These are only an embodiment of the application level, and in-depth studies will also involve the resource allocation of the database layer, the flow control of the network layer and the overall design of the operating system layer. Source: Master
