Ten experience in SQL Performance Optimization

ArticleDirectory

• Ten experience in SQL Performance Optimization

Master explain SQL Performance Optimization ten experience http://database.51cto.com Adair cnblogs I want to comment ( 1 )

• Abstract:These 10 pieces of experience are the results of the author's own summary.Code. I hope this article will give some inspiration to database administrators in terms of performance optimization.
• Tags:SQL optimization experience
• Limited time registration for "Oracle Global Conference · 2010 · Beijing" and "javaone and Oracle Developer Conference 2010"

 

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 foregroundProgram-- Change the supplier name field of the query condition from the original text input to the drop-down list. When you enter the Supplier name in Fuzzy mode, you can locate the specific supplier directly at the front-end, in this way, when the background program is called, this column can be directly associated with equals.

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 (Multi-Level subqueries are often nested)-You can consider splitting them into several steps, and then joining them into some temporary data tables.

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 (this problem was found some time ago when assisting XXX project in performance problem analysis)

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

Http://database.51cto.com Adair cnblogs I want to comment ( 1 )

• Abstract:These 10 pieces of experience are the results of the author's own summary and are explained with some code. I hope this article will give some inspiration to database administrators in terms of performance optimization.
• Tags:SQL optimization experience
• Limited time registration for "Oracle Global Conference · 2010 · Beijing" and "javaone and Oracle Developer Conference 2010"

 

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 (Multi-Level subqueries are often nested)-You can consider splitting them into several steps, and then joining them into some temporary data tables.

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 (this problem was found some time ago when assisting XXX project in performance problem analysis)

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

Http://database.51cto.com Adair cnblogs I want to comment ( 1 )

• Abstract:These 10 pieces of experience are the results of the author's own summary and are explained with some code. I hope this article will give some inspiration to database administrators in terms of performance optimization.
• Tags:SQL optimization experience
• Limited time registration for "Oracle Global Conference · 2010 · Beijing" and "javaone and Oracle Developer Conference 2010"

 

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 (Multi-Level subqueries are often nested)-You can consider splitting them into several steps, and then joining them into some temporary data tables.

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 (this problem was found some time ago when assisting XXX project in performance problem analysis)

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