Database performance Optimization Three: program operation optimization Improve performance _mssql

Database optimization includes the following three parts, database optimization, database table optimization, program operation optimization. This article is part III

Overview: Program Access optimization can also be considered to access the SQL statement optimization, a good SQL statement can reduce a lot of program performance, the following list of common error habits, and propose the corresponding solutions

first, operator optimization
1. In, not in operator
In and exists performance are distinguished by external and internal tables, but in large data tables it is recommended to use exists instead of in.
No in the index is absolutely not available, you can use not EXISTS instead
2. Is null or is not NULL operation
Indexes are not indexed to null values, so such operations cannot use indexes, and can be handled in other ways, such as numeric types, judgment greater than 0, string type setting a default value, and determining whether the default value is equal to
3. <> operator (not equal to)
Not equal to the operator is never used in the index, so processing it will only produce a full table scan. Replace with other operational operations of the same function, such as a<>0 change to a>0 or a<0a<> ' instead of a> '
4. Search text data with Full-text search instead of like search
Full-Text search is always superior to like search:
(1) Full-text search allows you to implement a complex search that you like cannot complete, such as searching a word or a phrase, searching for a word or phrase similar to another word or phrase, or searching for synonyms;
(2) It is easier to realize full text search than to implement like search (especially complex search);

second, SQL statement optimization
1, in the query do not use SELECT *
Why can not use, the Earth people know, but many people are accustomed to this use, to understand can save province, and so query the database can not use the "overlay index"
2. Write WHERE clause as far as possible
Try not to write an SQL statement without a where
3. Note the WHERE clause after the 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 can cause the system table to lock up for a long time, blocking other processes.
4. For aggregate queries, you can further qualify the returned rows with the HAVING clause
5. Avoid using temporary tables
(1) Unless necessary, the use of temporary tables should be avoided as far as possible, instead of using table variables;
(2) Most of the time (99%), the table variable is stationed in memory, so the speed is faster than the temporary table, temporary table is stationed in the tempdb database, so the operation on the temporary table needs to communicate across the database, the speed is naturally slow.
6. Reduce the number of access to the database:
It is best to put some commonly used global variable tables in memory or otherwise reduce the number of accesses to the database.
7. Try to do less repetitive work
Minimize ineffective work, but this focus on the client program needs to be noted as follows:
A, control the multiple execution of the same statement, especially some of the basic data multiple execution is a lot of programmers pay little attention to
B, reduce the number of data conversion, may require data conversion is the problem of design, but the reduction of times is the programmer can do.
C, to eliminate unnecessary subqueries and join tables, subqueries in the execution plan generally interpreted as an external connection, redundant connection table brings additional overhead.
D, merging multiple updates for the same condition of the same table, such as
UPDATE EMPLOYEE SET fname= ' haiwer ' WHERE emp_id= ' vpa30890f '
UPDATE EMPLOYEE SET lname= ' yang ' WHERE emp_id= ' vpa30890f '
The two statements should be merged into one of the following statements
UPDATE EMPLOYEE SET fname= ' haiwer ', lname= ' Yang '
WHERE emp_id= ' vpa30890f '
E, update operations do not split into the delete operation +insert operation form, although the same function, but the performance difference is very large.
F, do not write a number of meaningless queries, such as
SELECT * from EMPLOYEE WHERE 1=2

Iii. where to use the principle
1 in the following two SELECT statements:
SELECT * FROM table1 where field1<=10000 and field1>=0;
SELECT * FROM table1 where field1>=0 and field1<=10000;
If the data in the datasheet is Field1 >=0, the first SELECT statement is much more efficient than the second SELECT statement because the first condition of the second SELECT statement consumes a significant amount of system resources.
The first principle: in the WHERE clause, the most restrictive condition should be placed at the front.
2 in the following SELECT statement:
SELECT * from tab where a= ... and b= ... and c= ...;
In the case of indexed index (A,B,C), the order of the fields in the WHERE clause is consistent with the order of the fields in the index.
The second principle: the order of fields in the WHERE clause is consistent with the order of fields in the index.
The following assumes that there is a unique index I1 on the field1 and a field2 index I2.
3 Select Field3,field4 from TB where field1= ' SDF ' fast
SELECT * from TB where field1= ' SDF ' is slow,
Because the latter is a step rowid table access after the index scan.
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% ' quick
Select Field3,field4 from TB where field2 like '%R ' slow,
Because the latter does not use an 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 that you do not use a function; If a table has more than 50,000 records, it is strictly forbidden to use the function! 20,000 records are not restricted below.