Summary of performance optimization of relational database

For Web application development, most of the performance bottlenecks appear on the database, in addition to the use of distributed architecture or cloud processing (large companies are basically), more importantly, in peacetime programming to comply with some rules, fundamentally improve the performance of the system, the following summarizes a number of common rules and methods, for reference only, welcome to add ...

1, the data, logs, indexes on different I/O devices, increase the reading speed. The larger the amount of data (size), the more important it is to increase I/O.

2, vertical, horizontal partition table, reduce the size of the table, such as: Can be large data volume of the field to split the table.

3, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of the result set. Note that the fill factor is appropriate (preferably using the default value of 0). The index should be as small as possible, using a Lie Jian index with a small number of bytes, and not Lie Jian a single index on a limited number of values.

4. Words with or can be broken down into multiple queries, and multiple queries are linked through union. Their speed is only related to whether the index is used, and if the query requires a federated index, the union all executes more efficiently.

5. Avoid table scans by restricting the number of rows returned with a WHERE clause in the query SELECT statement. If unnecessary data is returned, the server's I/O resources are wasted, which increases the burden on the network and reduces performance. If the table is large, lock the table during the table scan, and prohibit other junctions from accessing the table, the consequences are serious.

6, pay attention to using distinct, do not use when not necessary, it will make the query slower than the union.

7, in the face value of the list, will appear the most frequent values in the first place, the least placed on the last side, reduce the number of judgments.

8, generally before the group by and having the clause can eliminate the extra rows, so try not to use them to do the culling line of work, that is, as far as possible in the Where to filter data.

9, try to put the data processing work on the server, reduce the network overhead, such as the use of stored procedures. Stored procedures are compiled, optimized, and organized into an execution plan, and the SQL statements stored in the database (stored procedure is a program on the database server side), is a collection of control flow language, of course, fast speed.

10, do not repeatedly use the same function in a sentence, wasting resources, put the results in a variable and then call faster.

11, for a large number of read-only query operations to optimize the method:

1) data with a small amount of data, can be considered not stored in the database, but through the method of program constants to solve.

2) data that needs to be stored in the database can be considered with materialized views (indexed views). When the DBA creates an index on the view, the view is materialized (executed), and the result set is persisted in a unique index and saved in the same way as a clustered index table. Materialized view minus the overhead of dynamically establishing a result set for a query referencing a view, the optimizer can use the view index in the query without having to specify the view directly in the FROM clause.

3) data storage can consider the appropriate data redundancy, to reduce the link between database tables, improve query efficiency.

4) for the characteristics of the data, take a specific index type. For example, bitmap indexing, and so on.

12, for the SQL statement when writing some suggestions:

1) Write a statement to determine the owner of the database object, as far as possible to bring the owner, such as:

SELECT * FROM dbo. Users

2) in the stored procedure, the parameter definition is best placed at the front, as defined as possible once, such as:

DECLARE @USER_ID INT , @USER_NAME VARCHAR (50) , @PASSWORD VARCHAR (50)

3) When assigning a value to a parameter, assign a value as soon as possible, such as:

SELECT @USER_ID = 1001 , @USER_NAME = ' Xiaojun.liu '

4) Use less cursors as much as possible

13, ...

Transferred from: http://www.cnblogs.com/liuxiaojun/archive/2010/12/09/database_performance_optimization.html

Summary of performance optimization of relational database