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

This article mainly introduces "what measures are there for SQL performance optimization". In daily operation, I believe many people have doubts about what measures SQL performance optimization has. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what measures are there for SQL performance optimization?" Next, please follow the editor to study!

1. Fuzzy matching of query

Try to avoid using the percent sign in LIKE'%parm1%'-- red to identify the location in a complex query, which will make the index of the related column unusable, and it is best not to use it.

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, directly modify the background-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 do complex correlation.

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, there are more and more table records over time. At this point, the lack of indexes will have a greater impact on performance.

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

Avoid calculating index fields avoid using ISNULL and ISNOTNULL on index columns avoid data type conversions on index columns avoid using functions on index fields avoid using null values in indexed columns

3. Complex operation

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

4.update

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

Updatetable1setcol1=...wherecol2=...;updatetable1setcol1=...wherecol2=.

Such scripts can actually 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 UNIONALL, UNION is used

Because UNION compares the records of each query subset, it is usually much slower than UNIONALL. In general, be sure to use UNIONALL if you can use UNIONALL to meet the requirements. There is another situation that you may ignore, that is, although you need to filter out duplicate records for the union of several subsets, because of the particularity of the script, there can be no duplicate records, so you should use UNIONALL, such as a query program of the xx module. See, due to the particularity of the statement, it is absolutely impossible to repeat the records of several subsets in this script, so you can use UNIONALL instead.

What are the measures for SQL performance optimization?

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

This common sense is believed to be known to most developers, but it is still used by many people. I think one of the main reasons may be that it harms performance in order to write and write simply, which is not desirable. During the performance analysis of the XX system in September, it was found that there are a large number of daemons that have similar uses, such as: wheretrunc (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 it here should be wherecreate_date > = trunc (: date1) andcreate_date or wherecreate_datebetweentrunc (: date1) andtrunc (: date1) + 1-1 / (24060060).

Note: because the range of between is a closed interval (greaterthanorequaltolowvalueandlessthanorequaltohighvalue.), strictly speaking, you should subtract a decimal that tends to zero, which is temporarily set to minus 1 second (1 / (240606060)). If you don't need to be so precise, you can omit this step.

7. Rules for Where statements

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

You can use exist and notexist instead of in and notin.

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

Exampl

SELECT*FROMORDERSWHERECUSTOMER_NAMENOTIN (SELECTCUSTOMER_NAMEFROMCUSTOMER)

Optimize

SELECT*FROMORDERSWHERECUSTOMER_NAMEnotexist (SELECTCUSTOMER_NAMEFROMCUSTOMER)

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:

SELECTemp.ename,emp.jobFROMempWHEREemp.empno=7369

Do not use:

SELECTemp.ename,emp.jobFROMempWHEREemp.empno='7369'

8. Rules for Select statements

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

-use th

SELECTempno,ename,categoryFROMempWHEREempno='7369'

-- instead of using

SELECT*FROMempWHEREempno='7369'

9. Sort

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

10. Temporary watch

Careful use of temporary tables can greatly improve system performance.

At this point, the study on "what measures are there for SQL performance optimization" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.