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 accessSQL Statement optimization, a good SQL statement 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. The <> operator (not equal to) is not equal to the operator and 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 a complex search like a single word or phrase, search for a word or phrase similar to another word or phrase, or search for synonyms ;

　　(2) implementing a full-text search is easier than implementing a like search ( 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 up some system tables, and if the WHERE clause returns too much data or is too slow, it can cause the system table to lock for a long time and plug other processes. 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, 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. Try to minimize duplication of effort to minimize ineffective work, but this point in the client program, you need to pay attention to the following: A, control the execution of the same statement multiple times, especially the number of basic data execution is seldom 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, such as update employee set fname= ' haiwer ' WHERE emp_id= ' vpa30890f ' Update employee set Lnam  E= ' 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 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 tableField1are>=0,The first articleSelectThe statement is better than the second one. statement efficiency is much higher because the second selectselect select * from  　　tab where a=... and b=... and c=...; If there is an index index (a,b,c) wherewhere

field1 There is a unique index on Span style= "Font-family:times New Roman;" >i1field2 i2 　　rowid table access.

Select Field3,field4 from TB where field1>= ' SDF ' fast select Field3,field4 from TB where field1> ' SDF ' slow because ago 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 index is not used for the latter.

4) Use functions such as: Select Field3,field4 from TB where upper (field2) = ' RMN ' does not use the 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.

Database performance Optimization Three: program operation optimization