Ten experience in SQL Performance Optimization

1. Fuzzy match of the query

Try to avoid using LIKE '% parm1 %' in a complex query -- the percentage sign of the red flag will make the index of the relevant column unusable.

Solution:

In fact, you only need to slightly improve the script, and the query speed will be improved by nearly times. The improvement method is as follows:

A. Modify the foreground program -- change the supplier name column of the query condition from the original text input to the drop-down list. When you enter the Supplier name in Fuzzy mode, locate the specific supplier directly at the front-end, so that this column can be directly associated with equals when the background program is called.

B. directly modify the backend -- Based on the input conditions, first identify qualified suppliers, and save relevant records in a temporary table header, and then use a temporary table for complex association

2. index problems

During performance tracking and analysis, we often find that many background program performance problems are caused by the lack of appropriate indexes, and some tables or even one index. This is often because the index is not defined when designing a table. in the initial stage of development, the performance may not be affected due to the small number of table records and whether the index is created, developers did not pay much attention to this. However, once the program is released to the production environment, more and more table records will be recorded over time.

If the index is missing, the impact on performance will become greater and greater.

This issue needs to be shared by database designers and developers.

Rule: do not perform the following operations on the created index data column:

◆ Avoid calculation of index fields

◆ Avoid using not on index fields. <> ,! =

◆ Avoid using is null and is not null in the index Column

◆ Avoid data type conversion in index Columns

◆ Avoid using functions on indexed fields

◆ Avoid using NULL values in indexed columns.

3. complex operations

Some UPDATE and SELECT statements are very complex and often nested multi-level subqueries)-You can consider splitting them into several steps and converting them into some temporary data tables before joining them.

4. update

Modifications to the same table appear dozens of times in a process, for example:

update table1 set col1=... where col2=...; update table1 set col1=... where col2=... ......

Such scripts can be easily integrated into an UPDATE statement to help xxx project analyze performance issues)

5. The union all statement is used.

Because UNION compares records of query subsets, the speed of UNION is usually much slower than union all. In general, if union all can meet the requirements, you must use union all. In another case, you may ignore it, that is, although the Union of several subsets needs to filter out duplicate records, it is impossible to have duplicate records due to the particularity of the script, in this case, union all should be used. For example, a query program in xx module once had this situation. For details, the records of several subsets in this script cannot be repeated due to the special nature of the statement, therefore, you can use union all instead)

6. Avoid Calculation of index fields in the WHERE statement.

This common sense is believed to be something most developers should know, but there are still many people using it. I think one of the most important reasons may be that the performance is compromised by the simplicity of writing.

During performance analysis on the XX system in March September, it was found that a large number of background programs have similar usage, such:

...... where trunc(create_date)=trunc(:date1)

Although you have created an index for the create_date field, the index cannot be used due to the addition of TRUNC. Which of the following statements is true?

where create_date>=trunc(:date1) and create_date

Or

where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)

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

So strictly speaking, we should subtract a decimal point that tends to be 0. Here we will set it to subtract 1 second 1/(24*60*60). If this is not required, you can skip this step.

7. Rules for Where statements

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

You can use exist and not exist to replace in and not in.

You can use table links instead of exist. Having can be replaced by where. If it cannot be replaced, it can be processed 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, but declare character values in digit format. The same date). Otherwise, the index will be invalid and a full table scan will be generated.

Example:

SELECT emp. ename, emp. job FROM emp WHERE emp. empno = 7369; Do not use: SELECT emp. ename, emp. job FROM emp WHERE emp. empno = '20140901'

8. Select statement rules

Restrict the use of select * from table in applications, packages, and processes. See the following example.

Use SELECT empno, ename, category FROM emp WHERE empno = '000000' Instead of using SELECT * FROM emp WHERE empno = '20140901'

9. Sort

Avoid resource-consuming operations. SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by enable SQL engine execution and resource-consuming sorting (SORT. DISTINCT requires a sorting operation, while other operations require at least two sorting operations.

10. Temporary table

Careful use of temporary tables can greatly improve system performance

1. How to optimize SQL Server
2. SQL Server database optimization experience
3. Technical Analysis on Oracle SQL statement Optimization