Database performance optimization 3: program operation optimization to improve performance

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

Overview: program access optimization can also be considered as the optimization of access SQL statements. A good SQL statement can reduce the performance of a lot of programs. The following lists common error habits, and propose corresponding solutions

I. Operator Optimization
1. IN and not in Operators
The performance of IN and EXISTS is distinguished by the external and internal tables. However, we recommend that you use EXISTS instead of IN large data tables.
Not IN cannot be used without indexing. It can be replaced by not exists.
2. is null or is not null operation
Indexes do not have null values, so indexes cannot be used for such operations. You can use other methods to handle such operations. For example, if the number type is determined to be greater than 0, you can set a default value for the string type, determine whether it is equal to the default value.
3. <> operator (not equal)
The non-equals operator will never use the index, so the processing of it will only generate a full table scan. Use other operation operations with the same function. For example, change a <> 0 to a> 0 or a <0a <> ''To a>''
4. Use full-text search to search for text data, replacing like search
Full-text search is always better than like search:
(1) full-text search allows you to implement complex searches that cannot be completed by like, such as searching for a word or phrase and searching for a word or phrase that is similar to another word or phrase, or search for synonyms;
(2) Implementing full-text search is easier (especially complicated search) than implementing like search );

Ii. SQL statement Optimization
1. Do not use select * in queries *
Why can't I use it? Everyone on Earth knows it, but many people are used to it. They need to understand that it can save time, and in this way, they cannot use "overwrite indexes" to query databases.
2. Try to write the WHERE clause
Try not to write SQL statements without WHERE
3. Note the WHERE clause after select.
Because select into inserts data INTO a temporary table, this process locks some system tables. If the data returned by this WHERE clause is too large or slow, the system table will be locked for a long time, other processes.
4. For aggregate queries, you can use the HAVING clause to further limit the returned rows.
5. Avoid using temporary tables
(1) Avoid using temporary tables unless necessary. Instead, use table variables;
(2) Most of the time (99%), the table variables are in the memory, so the speed is faster than the temporary table, and the temporary table is in the TempDb database, therefore, operations on temporary tables need to communicate across databases, and the speed is naturally slow.
6. Reduce the number of database accesses:
In programming, it is best to put some common global variable tables in the memory or use other methods to reduce the number of database accesses.
7. Try to do less repetitive work
Try to reduce ineffective work, but the focus is on the client program. Note the following:
A. control multiple executions of the same statement, especially the multiple executions of some basic data.
B. Data conversion may be designed to reduce the number of data conversions, but it can be done by programmers.
C. eliminate unnecessary subqueries and connection tables. subqueries are generally interpreted as external connections in the execution plan, resulting in additional costs for redundant connection tables.
D. Merge multiple updates for the same table with the same condition, 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 the next statement.
Update employee set fname = 'haiwer ', LNAME = 'yang'
WHERE EMP_ID = 'vpa30890f'
E. Do not split the UPDATE operation into the DELETE operation + INSERT operation. Although the functions are the same, the performance difference is great.
F. Do not write meaningless queries, such
SELECT * from employee where 1 = 2

III. where usage principles
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 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 lot of system resources.
First principle: In the where clause, place the most restrictive conditions at the beginning.
2) In the following select statement:
Select * from tab where a =... And B =... And c = ...;
If an index (a, B, c) exists, the field order in the where clause should be the same as that in the index.
The second principle: the field order in the where clause should be consistent with that in the index.
The following assumes that there is a unique index I1 on field1 and a non-unique index I2 on field2.
3) select field3, field4 from tb where field1 = 'sdf 'fast
Select * from tb where field1 = 'sdf 'slow,
Because the latter requires more ROWID table access after index scanning.
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 indexes.
4) use functions such:
Select field3, field4 from tb where upper (field2) = 'rmn 'No index is used.
If a table has 20 thousand records, we recommend that you do not use functions. If a table has more than 50 thousand records, you are strictly prohibited from using functions! There are no restrictions on the following 20 thousand records.