10 Lessons on SQL performance optimization

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 eligible suppliers, and keep the relevant records in a temporary table header, and then use 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 ofthe 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 table1

Set 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

Because the UNION will compare the records of each subset of queries, it is usually much slower than unionall. In general, if you use UNION ALL to meet the requirements, be sure to use UNION ALL. There is also a situation that you may ignore, that is, although the union of a few subsets requires filtering out duplicate records, but because of the specificity of the script, there is no possibility of duplicate records, then you should use the unions all, such as xx One of the modules of a query program has this situation, see, because of the specificity of the statement, in this script a few subsets of the record is absolutely impossible to repeat, so you can use UNION all)

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

9 months 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) andcreate_date[tr]</trunc (:d ate1) +1

or a

where create_date between trunc (:d ate1) andtrunc (:d ate1) +1-1/(24*60*60)

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

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 , or or have 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)

Optimized

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:

SELECT emp.ename, emp.job from emp whereemp.empno = 7369;

Do not use:SELECT emp.ename, emp.job from emp WHERE emp.empno = '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

Use SELECT empno,ename,category from emp WHERE empno = ' 7369'

Instead of using the SELECT * from emp WHERE empno = ' 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 sequencing ( SORT) feature . DISTINCT requires a sort operation , while others need to perform at least two sorting

Temporary Tables

Careful use of temporal table can greatly improve the performance of the system Oh.

The above is brother Lian

Share 10 lessons on SQL performance optimization.

10 Lessons on SQL performance optimization