Shulou(Shulou.com)05/31 Report--

This article shares with you about SQL performance optimization techniques. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Fuzzy matching of query

Try to avoid using the percent sign of LIKE'% parm1%'-- red to identify locations in a complex query, which will make the index of related columns unusable.

Solution:

In fact, with only a slight improvement to the script, the query speed will be nearly a hundredfold faster. The improvement methods are 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 the user vaguely enters the supplier name, he will help locate the specific supplier directly in the foreground, so that when calling the background program, this column can be directly used to associate.

B, modify the background directly-according to the input conditions, first find out the qualified suppliers, and save the relevant records in a temporary table, and then use the temporary table to make complex relationships.

two。 Index problem

In the process of performance tracking analysis, it is often found that the performance problems of many daemons are caused by the lack of a suitable index, and some tables do not even have an index. This is often because the index is not defined when designing the table, and in the early stage of development, because there are few table records, whether the index is created or not may have no impact on performance, so developers do not pay more attention to it. However, once the program is released to the production environment, more and more table records will be recorded over time.

At this point, the lack of indexes will have a greater impact on performance.

This problem requires the common attention of database designers and developers.

Rule: do not do the following on the data column of the index:

◆ avoids calculating index fields

◆ avoids using notline field on index field.

◆ avoids using IS NULL and IS NOT NULL on index columns

◆ avoids data type conversions on index columns

◆ avoids using functions on index fields

◆ avoids using null values in indexed columns.

3. Complex operation

Some UPDATE and SELECT statements are very complex (often nested multi-level subqueries)-- you can consider breaking them into several steps, turning them into some temporary data tables, and then associating them.

4.update

Changes to the same table occur dozens of times in the same process, such as:

Update table1set col1=...where col2=...;update table1set col1=...where col2=.

Scripts like this can be easily integrated into a UPDATE statement (this was found earlier when assisting xxx projects in performance problem analysis)

5. In statements that can use UNION ALL, UNION is used

Because UNION compares the records of each query subset, it is usually much slower than UNION ALL. In general, be sure to use UNION ALL if you can meet the requirements with UNION ALL. There is another situation that you may overlook, that is, although the union of several subsets is required to filter out duplicate records, because of the particularity of the script, there can be no duplicate records, so UNION ALL should be used. For example, a query program of the xx module once existed. See, because of the particularity of the statement, the records of several subsets in this script can never be duplicated, so you can use UNION ALL instead)

6. In WHERE statements, try to avoid calculating index fields.

This common sense is believed to be known to most developers, but many people still use it. I think one of the main reasons may be that it harms performance in order to write and write simply, which is not desirable.

In a performance analysis of the XX system in September, it was found that a large number of daemons had similar uses, such as:

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

Although the create_date field has been indexed, the index cannot be used because of the addition of TRUNC. The correct way to write here should be

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 range of between is a closed interval (greater than or equal to low value and less than or equal to high value.)

Therefore, strictly speaking, you should subtract a decimal that tends to zero, which is temporarily set to minus 1 second (1 / (2406060)). If you don't need to be so precise, you can omit this step.

7. Rules for Where statements

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

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

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

Examples

SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER)

Optimize

SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER)

7.2 do not declare numbers in character format, declare character values in numeric format. (the date is the same) otherwise the index will be invalidated and a full table scan will occur.

Examples use:

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

8. Rules for Select statements

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

Use SELECT empno,ename,category FROM emp WHERE empno = '7369' instead of SELECT * FROM emp WHERE empno =' 7369'

9. Sort

Avoid using resource-consuming operations, SQL statements with DISTINCT,UNION,MINUS,INTERSECT,ORDER BY will start the SQL engine execution, resource-consuming sorting (SORT) function. DISTINCT requires one sort operation, while the others need to perform at least two sort operations

10. Temporary watch

Careful use of temporary tables can greatly improve system performance.

Thank you for reading! This is the end of this article on "what are the SQL performance optimization skills?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.