1.SQL Optimization Series--Expert detailed SQL performance Optimization 10 experience

1. Fuzzy Matching of queries

Try to avoid using the like '%parm1% ' in a complex query--red identifies the location of the percent sign to cause the index of the related column to be unusable, preferably not used.

Workaround:

In fact, the script only needs to be improved slightly, the query speed will be raised nearly hundred times. Here's how to improve it:

A, modify the foreground program-the query condition of the Supplier Name column from the original text input to the drop-down list, the user fuzzy input supplier name, directly in the foreground to help locate the specific supplier, so that in the call daemon, this column can be directly associated with equals.

b, directly modify the background-according to the input criteria, first identify the qualified suppliers, and keep the relevant records in a temporary table, and then use the temporary tables to do complex association

2. Indexing issues

In the performance tracking analysis process, often found that there are many background program performance problems due to the lack of suitable indexes, and some tables even an index did not. This often happens because the index is not defined when the table is designed, and in the early stages of development, because the table records are few, the index is created or not, and the developers are not paying much attention to the performance. Once the program is released into the production environment, the table records more and more over time

When the index is missing, the impact on performance is getting bigger.

This issue requires the attention of database designers and developers

Rule: Do not do the following on the data column of the index you are building:

Avoid calculating operations on indexed fields

Avoid using not,<>,!= on indexed fields

Avoid using is null and is not NULL on indexed columns

Avoid data type conversions on indexed columns

Avoid using functions on indexed fields

Avoid using null values in indexed columns.

3. Complex operations

Part of the update, SELECT statement is very complex (often nested multi-level subqueries)--can be considered appropriate to split into a few steps, Sir into some temporary data tables, and then related operations

4.update

The modification of the same table appears dozens of times in a process, such as:

Update table1 set col1= ... where col2=...; Update table1set col1= ... where col2= .....

Like this kind of script can be easily integrated in an UPDATE statement to complete (some time ago in assisting the XXX project to do performance analysis, it is found that this situation exists)

5. In the statement that the Union all can be used, the Union

Union because it compares the records of each subset of queries, it is usually much slower than union all. In general, if you use union all to meet the requirements, be sure to use union ALL. There is a situation that you may ignore, that is, although a few subsets of the Union need to filter out duplicate records, but because of the specificity of the script, it is not possible to duplicate records, then you should use the union all, such as XX module of a query program has existed this situation, see, due to the specificity of the statement, The records of several subsets in this script are absolutely impossible to repeat, so you can use union ALL instead.

6. In the where statement, try to avoid the calculation of the indexed fields

This common sense believes that most developers should know, but still a lot of people use it, and I think one of the main reasons may be to write simple and damage the performance, it is not taken

September in the performance analysis of XX system found that there are a large number of background programs have similar usage, such as:

...... where trunc (create_date) =trunc (:d ate1)

Although the Create_date field has been indexed, the index cannot be used because of the addition of trunc. The correct wording here should be

where create_date>=trunc (:d ate1) and Create_date

or a

where create_date between trunc (:d ate1) and trunc (:d ate1) +1-1/(per * )

Note: The range of between is a closed interval (greater than or equal to low value and less than or equal to high value.),

Therefore, the strict sense should subtract a decimal that tends to 0, here for the moment set to minus 1 seconds (1/(24*60*60)), if not required so accurate, can be omitted this step.

7. The law of the WHERE statement

7.1 Avoid using in,not in,or or having in the WHERE clause.

You can use exist and not exist instead of in and not.

You can use table links instead of exist. Having can be replaced by where, if not replaced, can be handled in two steps.

Example

SELECT * from  ORDERS WHERE customer_name not in (SELECT customer_name from CUSTOMER)

Optimization

SELECT * from  ORDERS WHERE customer_name not exist (select Customer_name from CUSTOMER)

7.2 Do not declare numbers in character format, you want to declare character values in numeric format . (The same date) otherwise invalidates the index, resulting in a full table scan.

Examples use:

7369= '7369'

8. The rule of the SELECT statement

Restrict the use of select * from table in applications, packages, and procedures. Look at the following example

' 7369 ' ' 7369 '

9. Sorting

Avoid the use of resource-intensive operations, SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine execution, resource-intensive sorting (sort) function. Distinct requires a sort operation, while others need to perform at least two sorting

10. Temporary tables

Careful use of temporal tables can greatly improve system performance

{reprinted from: http://database.51cto.com/art/200904/118526.htm}