Shulou(Shulou.com)06/03 Report--

1. Fuzzy matching of query

Try to avoid using the percent sign of the Like'% parm1%'-- red location in a complex query to make the index of the related column unusable. 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:

1. 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 enters the supplier name vaguely, he will help locate the specific supplier directly in the foreground. In this way, when calling the background program, this column can be directly used to associate.

2. 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 make complex associations.

II. 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 much 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.

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

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

Avoid calculating index fields

Avoid the use of notdiary field on the index field

Avoid using IS NULL and IS NOT NULL on index columns

Avoid data type conversions on index columns

Avoid using functions on index fields

Avoid using null values in indexed columns.

III. Complex operation

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

IV. UPDATE

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

Update table1

Set col1=...

Where col2=...

Update table1

Set col1=...

Where col2=...

......

Scripts like this can be easily integrated into a single update statement (this is sometimes found when assisting XXX projects in performance problem analysis)

5. UNION is used in statements where UNION ALL can be 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 ignore, that is, although the union of several subsets needs to be filtered and re-recorded, because of the particularity of the script, there can be no duplicate records, so you should use UNION ALL. If a query program of the XX module once existed, because of the particularity of the statement, it is absolutely impossible to repeat the records of several subsets in this script, so you can use UNION ALL instead.

In the where statement, try to avoid calculating all 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 for the sake of simplicity, 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 [tr]

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.