SQL Tuning "II"

1. Database design (whether the composite paradigm, whether reasonable archiving, zoning, sub-table, etc.)

2. Hardware infrastructure (device specifications, hardware performance, load balancing, disaster tolerance, etc.)

3.QL statement notation, indexing and statistics, transactions and locks, application access code (too many connections, frequent switches, etc.)]

1, the design of the field

A field is the most basic unit of a database, and its design has a significant impact on performance. The following should be noted:

A, the data type as far as possible with the digital type, the digital type comparison is much faster than the character type.

B, the data type is as small as possible, where the minimum is to meet the foreseeable future needs of the premise.

C, try not to allow NULL, unless necessary, you can use not null+default instead.

D, less with the text and image, binary field read and write is relatively slow, and, read the method is not much, most of the case is best not.

E, self-increment field to use with caution, not conducive to data migration.

Design of the Index

In the design phase, a preliminary index design can be performed based on functional and performance requirements, where the index needs to be designed based on the estimated amount of data and queries, and may differ from what is actually used in the future.

2, regarding the choice of index, should change the idea:

A, according to the amount of data to determine which tables need to increase the index, the small amount of data can only the primary key.

B, depending on the frequency of use to determine which fields need to be indexed, select the fields that are frequently used as join conditions, filter criteria, aggregate queries, and sort as candidates for the index.

C, combine the fields that often appear together, make up the combined index, the field order of the combined index is the same as the primary key, also need to put the most common fields in front, the low repetition rate of the field in front.

D, a table does not add too many indexes, because the index affects the speed of insertions and updates.

F, do not operate on indexed fields, but try to change them.

G, do not format the indexed fields

H, do not use functions on indexed fields

I, do not make multi-field connections to indexed fields

3. Return only the data you need

The return data to the client needs at least the database extracts data, the network transmits the data, the client receives the data as well as the client processing data and so on, if returns the unnecessary data, will increase the server, the network and the client invalid labor, its harm is obvious, avoids this kind of event to need to notice:

A, in the horizontal view, do not write the SELECT * statement, but choose the field you need.

B, vertically, write the WHERE clause, and do not write a SQL statement without where.

C, note the WHERE clause of the SELECT INTO, because the select into inserts data into the temporary table, this process locks some system tables, if the WHERE clause returns too much data or too slow, it will cause the system table long-term lock, plug other processes.

D, for aggregate queries, you can further qualify the returned rows with the HAVING clause.

4, try to do less repetitive work

A, control the execution of the same statement multiple times, especially the number of basic data execution is rarely noticed by many programmers.

B, reduce the number of data conversion, may require data conversion is a design problem, but the reduction is the programmer can do.

C, eliminate unnecessary sub-queries and join tables, sub-query in the execution plan is generally interpreted as an external connection, redundant connection table brings additional overhead.

D. Merge multiple update for same condition on same table

E, update operation do not split into the form of delete operation +insert operation, although the function is the same, but the performance difference is very large.

F, do not write a few meaningless queries

5. Attention to transactions and locks

The transaction is the database application and the important tool, it has the atomicity, the consistency, the isolation, the persistence four properties, many operations we all need to use the transaction to guarantee the data correctness. In the use of transactions we need to try to avoid deadlocks and minimize blocking. Special attention needs to be paid to the specific following:

A, the transaction operation process to be as small as possible, can split the transaction to split apart.

B, the transaction operation process should not have interaction, because when the interaction waits, the transaction does not end, possibly locking a lot of resources.

C, the transaction operation procedure to access the object in the same order.

D, improve the efficiency of each statement in the transaction, using indexes and other methods to improve the efficiency of each statement can effectively reduce the execution time of the whole transaction.

E, try not to specify the lock type and index, SQL Server allows us to specify the type of lock and index used by the statement, but in general, the SQL Server optimizer chooses the lock type and index is optimal under the current data volume and query conditions, we have specified may be only in the current situation more, But the amount of data and the distribution of data will change in the future.

F, the query can use a lower isolation level, especially when the report query, you can choose the lowest isolation level (READ UNCOMMITTED).

6. Note the use of temporary tables and table variables

In complex systems, temporary tables and table variables are difficult to avoid, and for the use of temporal tables and table variables, it is important to note:

A, if the statement is complex and there are too many connections, consider stepping through the temporary table and table variables.

B, if you need to use the same part of a large table multiple times, consider staging this part of the data with temporary tables and table variables.

C, if you need to synthesize data from multiple tables to form a result, consider using temporal tables and table variables to summarize data for these tables in steps.

D, in other cases, you should control the use of temporary tables and table variables.

E, about the choice of temporary tables and table variables, many of the statements are table variables in memory, fast, should be preferred table variables, but in the actual use of the choice is mainly to consider the amount of data to be placed on the temporary table, in the case of large amounts of data, the temporary table speed is faster instead.

F, about temporary table generation using SELECT INTO and CREATE TABLE + INSERT into selection

7, pay attention to the wording of the connection conditions

The connection condition of the multi-table connection is of great importance to the selection of the index, so we need special attention when we write the condition of the connection.

A, multi-table connection, the connection conditions must be written in full, rather repeat, do not leave gaps.

B, connection conditions use clustered index as far as possible

C, note the difference between the on-part condition and the where part condition

SQL Tuning "II"