1. Fuzzy matching of queries
Try to avoid using LIKE ‘%parm1%’ in a complex query – the percentage sign in red will cause the index of the related column to be unusable and should not be used.
Solutions:
A slight improvement to the script would have sped up queries nearly a hundredfold. The improvement method is as follows:
A. Modify the foreground program — change the column of supplier name of the query condition from the original text input to a drop-down list. When users blur the supplier name, they can directly locate specific suppliers in the foreground, so that when calling the background program, this column can be directly associated with equal.
B, directly modify the background — according to the input conditions, find out the suppliers that meet the conditions first, and save the relevant records in a temporary table, and then use the temporary table to do complex association
2. Index problems
In the process of performance tracking analysis, it is often found that many background program performance problems are caused by the lack of appropriate index, some tables even have no index. This is often the case because indexes are not defined at table design time, and because the table records are few, index creation may have little impact on performance at the beginning of development, so developers do not pay much attention to index creation. However, once the program is released into production, more and more table records are recorded over time
In this case, the lack of indexes will have an increasing impact on performance.
This is an issue that needs to be addressed by both database designers and developers
Rule: Do not perform any of the following operations on an index column:
◆ Avoid calculating index fields
◆ Avoid using not, <>,! =
◆ 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 null values in indexed columns.
3. Complex operations
Some UPDATE and SELECT statements are complex (often nested with multiple levels of subqueries) – consider breaking them down into temporary tables and associating them
4.update
Changes to the same table occur dozens of times in a process, such as:
update table1
set col1=…
where col2=… ;
update table1
set col1=…
where col2=…
.
Scripts like this can be easily integrated into an UPDATE statement (as I discovered when I was assisting the XXX project with performance analysis).
5. In the statement that can use the UNION ALL, use the UNION
A UNION is usually much slower than a UNION ALL because it compares records from a subset of queries. In general, use UNION ALL if it satisfies the requirements. There is another situation that people may ignore, that is, although the UNION of several subsets is required to filter out repeated records, it is impossible to have repeated records due to the particularity of the script, so UNION ALL should be used. For example, a query program of XX module once had such situation. See, due to the particularity of the statement, Several subsets of records in this script can never be repeated, so use UNION ALL instead.
6. Avoid calculating index fields in the WHERE statement
This is a common sense that most developers should be aware of, but many still use it, and I think one of the biggest reasons is that it’s not desirable to write easily at the expense of performance
During the performance analysis of XX system in September, it was found that a large number of background programs had similar uses, such as:
.
where trunc(create_date)=trunc(:date1)
Although the create_date field was indexed, it was not used because TRUNC was added. The correct way to write this is
where create_date>=trunc(:date1) and create_date
Or is it
where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)
Note: Since the range between is greater than or equal to low value and less than or equal to high value.
So technically you should subtract another decimal as it approaches 0, so I’m going to subtract 1 second (1/(24*60*60)) for the moment, but you can skip this step if you don’t want to be so precise.
7. Rules for Where statements
7.1 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 with where. If it cannot be replaced, it can be handled in two steps.
example
SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN
(SELECT CUSTOMER_NAME FROM CUSTOMER)
To optimize the
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. (same date) otherwise invalidates the index, resulting in a full table scan.
Examples use:
SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
SELECT emp.ename, emp.job FROM emp WHERE empno = ‘7369’
8. Rules for Select statements
Restrict the use of SELECT * from Table in applications, packages, and procedures. Look at the following example
SELECT empno,ename,category FROM emp WHERE empno = ‘7369 ‘
SELECT * FROM emp WHERE empno = ‘7369’
9. The sorting
Avoid expensive operation, with a DISTINCT, UNION, MINUS, intersects, the ORDER BY the SQL statement will start the SQL execution engine, cost resources sorting (SORT) function. DISTINCT requires one sort operation, while others require at least two sorts
10. The temporary table
Careful use of temporary tables can greatly improve system performance