10 Lessons on SQL performance optimization

1. fuzzy matching of queries

try to avoid using in a complex query Like '%parm1% ' -- a red-labeled percent sign will cause the index of the related column to be unusable and should not be used .

Solutions :

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 indexed index created :

Avoid calculating operations on indexed fields

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

avoid using on indexed columns is NULL and the is not NULL

Avoid data type conversions on indexed columns

Avoid using functions on indexed fields

Avoid using null values in indexed columns.

3. Complex Operations

Part UPDATE , SELECT Statement very complicated to write (often nested multi-level subqueries)--you can consider a few steps, sir. Make some temporary data tables, and then do the associated 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= ...

......

scripts like these can actually be easily integrated in a UPDATE statement to complete (some time ago in assisting XXX This is the case when the project does analysis of performance problems)

5. in the statement that the union all can be used , the Union

UNION because the records of each subset of queries are compared, UnionAll , the speed is usually much slower. In general, if you use Union all to meet the requirements, be sure to use Unionall. 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 unionsall, 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 UNIONall)

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 Month in pairs XX when the system performs performance analysis, it is found that there are a lot of background programs with similar usage, such as:

......

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

Although the create_date The field was indexed, but due to the addition of the TRUNC to make the index unusable. 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: Because between the range 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 tendency 0 the decimal, which is set aside for the moment minus 1 seconds ( 1/(24*60*60) ), you can skip this step if you don't want to be so precise.

7. The Law of the Where statement

7.1 avoid the WHERE clause is used in the inch , not in , or or having .

can use exist and the Not exist Replace inch and the not in .

You can use table links instead 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

restricting usage in applications, packages, and procedures select * FROM table this way. Look at the following example

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

and do not use SELECT * from emp WHERE empno = ' 7369 '

9. Sorting

avoid the use of resource-intensive operations, with Distinct,union,minus,intersect,order by of the SQL statement will start SQL engine execution, resource-intensive sequencing (SORT) function . 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.

above is Brother Lian

to share with you about SQL 10 experience in performance optimization.

10 Lessons on SQL performance optimization