Database SQL statement optimization

Why optimize:

With the launch of the actual project, after the database has been running for a period of time, the initial database settings will have some differences with the actual database performance. At this time, we need to make an optimization adjustment.

The topic of database optimization is relatively large and can be divided into four categories:

• 》Host performance
• 》Memory usage performance
• 》Network transmission performance
• 》SQL statement execution performance [Software Engineer]

Here are some database SQL optimization solutions:

(01) Choose the most efficient table name order (often asked in the written test)

The database parser processes the table names in the FROM clause from right to left. The last table in the FROM clause will be processed first. If the FROM clause contains multiple tables, you must select the table with the least number of records and put it last. If there are more than three tables in the query, you need to select the table referenced by other tables and put it last.

For example: query employee number, name, salary, salary grade, department name

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade, dept, emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

1) If the three tables are completely unrelated, write the table with the least records and column names last, and so on.

2) If the three tables are related, put the table with the most references at the end, and so on.

(02) Join order in the WHERE clause (often tested in the written exam)

The database parses the WHERE clause from right to left. According to this principle, the connection between tables must be written to the left of other WHERE conditions, and those conditions that can filter out the maximum number of records must be written to the right of the WHERE clause.

For example: query employee number, name, salary, department name

select emp.empno,emp.ename,emp.sal,dept.dname
from emp,dept
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

(03) Avoid using the asterisk (*) in the SELECT clause

During the parsing process, the database will convert * into all column names in sequence. This work is done by querying the data dictionary, which means it will take more time.

select empno,ename from emp;

(04) Use TRUNCATE instead of DELETE

(05) Use COMMIT as much as possible

Because COMMIT will release the rollback point

(06) Replace the HAVING clause with the WHERE clause

WHERE is executed first, HAVING is executed later

(07) Use internal functions more often to improve SQL efficiency

(08) Using table aliases

salgrade s

(09) Using column aliases

ename e

In short, database optimization is not a one-day topic. You have to conduct repeated tests and summaries in long-term work practice. I hope that students will understand it well in the future.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links