Database performance optimization 3: Program Operation Optimization

Database optimization includes the following three parts: Database optimization and database table optimization,ProgramOperation Optimization. This article is part 3 Database performance optimization 3: Program Operation Optimization Overview: program access optimization can also be considered as access SQL Statement optimization, a good SQL Statements can reduce the performance of many programs. The following lists common error habits and provides corresponding solutions.

 

I. Operator Optimization

1. in and not in Operators

InAndThe exists performance is distinguished by the external and internal tables. However, we recommend that you use exists instead of in for tables with large data volumes..

Not in cannot be used without indexing. It can be replaced by not exists.

2. Is nullOrIs not nullOperation

Indexes do not have null values. Therefore, indexes cannot be used for such operations. You can use other methods, such as numeric type, to determine whether the value is greater0, Set a default value for the string type to 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 operations with the same function, such as a <> 0ChangeA> 0 or a <0A <>''ChangeA>''

4. Use full-text search to search for text data, replacing like search

Full-text search is always betterLikeSearch:

(1)Full-text search allows you to implementLikeComplex searches that cannot be completed, such as searching for a word or phrase, searching for a word or phrase that is similar to another word or phrase, or searching for synonyms;

(2)Achieve full-text search RatioLikeEasier to search(Especially complex search);

 

Ii. SQLStatement 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 writeWhereClause

Try not to write noWhereOfSQLStatement

3. NoteSelectAfterWhereClause

BecauseSelectInsert data into a temporary table. Some system tables will be locked in this process.WhereToo much or too slow data is returned by the clause, which may cause the system table to be locked for a long time and cause other processes to be blocked.
4. For aggregate queries, you can useHavingClause further limits returned rows

5. Avoid using temporary tables

(1)Avoid using temporary tables unless necessary. Instead, use table variables instead.;

(2)Most of the time(99%)The table variables are in the memory, so the speed is faster than that of the temporary table.TempdbIn the database, therefore, operations on temporary tables need to be performed across DatabasesCommunication speeds are 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:
AControl multiple executions of the same statement, especially the multiple executions of some basic data.

BTo reduce the number of data conversions, data conversion may be a design issue, but the number of reductions is what programmers can do.

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 times for the same condition of the same tableUpdateFor 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,UpdateDo not split the operationDeleteOperation+ InsertAlthough the functions are the same, the performance varies greatly.

FDo not write meaningless queries, such
Select * from employee where 1 = 2

III. WhereUsage principles

1) in the following two Select 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 table Field1 Both > = 0, The first Select Statement Select The statement is much more efficient because the second statement Select The first condition of a statement consumes a lot of system resources.
First principle: Where Clause should put the most restrictive conditions at the beginning.
2) In the following Select Statement :
Select * From tab where a =... And B =... And c = ...;
If there is an index Index (A, B, C) , Then Where The order of fields in the clause must be the same as that in the index.
The second principle: Where The order of fields in the clause must be the same as that in the index.

in field1 There is a unique index I1 , in field2 there are non-unique indexes on it I2 .
3) Select field3, field4 from TB where field1 = 'sdf ' fast
select * from TB where field1 = 'sdf ' slow,
because the latter requires one more step after index scanning 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 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.

Possible partsArticleI have used other online articles for reference. This article is for learning purposes only. For more information, see the source.

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