"SQL Server Primary" database performance Optimization three: program operation optimization

The database optimization consists of the following three parts, the optimization of database itself, the optimization of database table and the optimization of program operation. This article is Part IIIDatabase Performance Optimization Three: program operation optimizationOverview: Program Access optimizations can also be considered as accessSQLthe optimization of the statement, a goodSQLstatements can reduce a lot of program performance, the following list common error habits, and propose a corresponding solution

　　One, the operator optimization

1. In, not in operator

In and EXISTS properties are distinguished by appearance and inner table, but in large data volumes it is recommended to use EXISTS instead of in .

Not in does not walk the index is absolutely useless, you can use not EXISTS instead of

2. Is null or not null operation

Indexes are non-indexed, so the operation cannot use the index, can be handled in other ways, for example: number type, judgment greater than 0, String type set a default value, determine whether it is equal to the default value

3. <> operator (not equal to)
The non-equal operator is never indexed, so processing it only results in a full table scan. Replace with other operations of the same function, such as a<>0 instead of a>0 or a<0 a<> "to a>"

4. Search text data with full-text search instead of like search

Full-Text Search is always better than like search:

　　(1) Full-Text search allows you to implement like A complex search that cannot be completed, such as searching for a word or phrase, searching for a word or phrase similar to another word or phrase, or searching for synonyms ;

　　(2) implement full-text search ratio like Search Easier ( especially for complex searches );

Second, SQL statement optimization

1. Do not use SELECT * in the query

Why not use, the Earth people know, but many people are accustomed to this use, to understand that can save the province, and so the query database can not take advantage of "overwrite index"

2. Write the WHERE clause as much as possible

Try not to write SQL statements without where

3. Note the WHERE clause after SELECT into

Because SELECT into inserts data into a temporary table, this process locks some system tables, and if the WHERE clause returns too much data or is too slow, it causes the system table to be locked for a long time. Other processes of the Cypriots.
4. For aggregate queries, you can further qualify the returned rows with the HAVING clause

5. Avoid using temporary tables

　　(1) you should avoid using temporary tables unless you need them, instead, you can use table variables instead ;

(2) most of the time (99%), table variables are stationed in memory, so faster than temporary tables, and temporary tables are stationed in the TempDb database, so operations on temporary tables require cross-database communication, which is naturally slow.

6. Reduce the number of Access databases:

In programming, it is best to put some common global variable tables in memory or to reduce the number of accesses to the database in other ways.

7. Do as few repetitions as possible
Minimize invalid work, but this point focuses on the client program, which needs to be noted as follows:
A   Controlling multiple executions of the same statement, especially the multiple execution of some underlying data, is a problem that many programmers rarely notice

B and reduces the number of data transformations that may require data conversion to be a design issue, But the number of reductions is what programmers can do.

C, eliminating unnecessary subqueries and join tables, the subquery is generally interpreted as an outer join in the execution plan, and extra overhead is incurred by the redundant join tables.

D, merging multiple update 　　For example
update employee set fname= ' haiwer '  where emp_id= '  vpa30890f '
Update employee set lname= ' YANG '  where emp_id= '  vpa30890f '
These two statements should be merged into one of the following statements
Update employee set fname= ' haiwer ', lname= ' YANG '   
where emp_id= '   vpa30890f '

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 some meaningless queries, such as
SELECT * from EMPLOYEE WHERE 1=2

Iii. where principles of use

1) in the following two articleSelectin the statement:
SELECT * FROM table1 where field1<=10000 and field1>=0;
SELECT * FROM table1 where field1>=0 and field1<=10000;
If the data in the data tablefield1All>=0,the first articleSelectThe statement is better than the second one .SelectThe statement is much more efficient because the second articleSelectThe first condition of a statement consumes a large amount of system resources.
The first principle:whereThe most restrictive conditions should be placed at the front of the clause.
2)in the followingSelectin the statement:
SELECT * from tab where a= ... and b= ... and c= ...;
If there is an indexindex (A,B,C), youwherethe order of the fields in the clause should match the order of the fields in the index.
A second principle:wherethe order of the fields in the clause should match the order of the fields in the index.

The following assumes field1 Has a unique index on the i1 field2 i2
3)  select field3,field4 from tb where field1= ' SDF '   fast
select * from tb where field1= ' sdf '   slow,
because the latter takes one more step after the index scan rowid table access.

Select Field3,field4 from TB where field1>= ' SDF ' fast
Select Field3,field4 from TB where field1> ' SDF ' slow
Because the former can quickly locate the index.

Select Field3,field4 from TB where field2 like ' r% ' fast
Select Field3,field4 from TB where field2 like '%R ' slow,
Because the latter does not use the index.

4) Use functions such as:
Select Field3,field4 from TB where upper (field2) = ' RMN ' does not use an index.
If a table has 20,000 records, it is recommended not to use a function; If a table has more than 50,000 records, the use of functions is strictly forbidden! 20,000 records there is no limit to the following.

The above may be part of the article for reference to other network articles, this article only for the study use, reproduced please indicate the source

--------------AK (old K): 2012-12-28

"SQL Server Primary" database performance Optimization three: program operation optimization