9 points to be remembered for SQL command optimization and 9 points to SQL command Optimization

9 points to be remembered for SQL command optimization and 9 points to SQL command Optimization

The basic language for interaction with databases is SQL. Each time the database parses and executes SQL statements, many steps are required. Take SQL server as an example. When a database receives a query statement, the syntax analyzer scans the SQL statement and divides it into logical units (such as keywords, expressions, operators, and identifiers) and generates a query tree, finally, the query optimizer analyzes all the methods for accessing the source table of the database, and selects a group of steps that return the fastest result set and consume less resources. The query tree is updated to accurately record this step, and then executed by the database engine. Then, the query results are returned to the user. It can be seen that each time the database engine executes SQL commands, there will be a lot of overhead. if the quality of the submitted SQL statements is not high or even there are logical errors, it will cause unnecessary overhead and a waste of time. To avoid this situation, pay attention to the following principles when using SQL commands:

1. Field extraction should follow the principle of "How much is required and how much is requested" to avoid "select *". Try to use "select Field 1, Field 2, Field 3 ...". Practice has proved that the database extraction speed will be improved for every few fields extracted. The speed of improvement depends on the size of the discarded field.

2. Use exists instead of select count (*) to determine whether a record exists. The optimizer supports short-circuit when optimizing exists predicates. As long as a row is found, you do not need to scan other rows to determine whether the table contains rows. The count function is only used to count the number of rows in a statistical table.

3. Try to use (not) exists instead of (not) in operation. The SQL Performance of in is always relatively low.

-- Statement select dname, deptno from dept where deptno not in (select deptno from emp where dept. deptno = emo. deptno) -- Statement select dname, deptno from dept where not exists (select deptno from emp where dept. deptno = emo. deptno)

4. Try not in and use left outer join instead.

5. Try not to use or. Using or will cause full table scanning, which will greatly reduce the query efficiency.

6. Pay attention to the writing of the where clause. The order of statements must be taken into account. The order of the condition clause should be determined based on the index order and range, and the field order should be consistent with the index order as much as possible, the range is from large to small.

7. Try to use "> =", do not use ">"

8. Understand the index structure of the table before writing SQL statements. Effectively using indexes can avoid unnecessary full table scans and shorten the query time. Avoid using is null, <>,! In the where clause ,! =, Not, not exist, not in, not like, and other commands, they usually cause full table scan, resulting in invalid indexes.

9. In the where clause, any Column Operations (functions, calculations, and so on) will cause index failure. These operations should be moved to the right of the equal sign as much as possible, such as where substring (id) = 'A', which should be written as where id like 'a % '; where result * 10> 30 should be written as where result> 30;

The basic principle for optimizing SQL commands is to minimize type conversion and computation, make full use of table indexes, and reduce the number of full table scans.

SQL command Optimization

Select * from emp where ename = 'Smith'
This statement is equivalent to directly finding the data with ename = 'Smith.

Select * from emp where ename like '% H'
This is equivalent to comparing each entry.

Which is fast? Obviously

A complete list of basic Access SQL commands

Single query:
(1) select * from Table Name
(2) select Field 1, Field 2, Field 3... from Table Name
(3) select * from table name where Condition
(4) select Field 1, Field 2, Field 3... from table name where Condition
(5) select top 5 field from Table Name (display the first few items of a field)
(6) select distinct field from Table Name (fields are not repeatedly displayed)
(7) select * from table name where age between 20 and 30 (between...) Multi-Table query --- find the relationship between the table and the table
Eg: Student table (course number, name, age, gender, course number) (course number, course name)
(8) select Table 1. field 1, table 1. field 2, table 2. field 1, table 2. field 2... from table 1, Table 2 where table and table relationship and condition
Eg: select student table. Name, curriculum. Course name from student table, curriculum where student table. Course number = curriculum. Course number
(9) select T. field 1, T. field 2, S. field 1, S. field 2... from table 1 as T, Table 2 as S where table and table relationship and condition
Eg: select T. Name, S. Course name from student table as T, curriculum as S where T. course No. = S. course No.
(10) select Table 1. field 1, table 1. field 2, table 2. field 1, table 2. field 2... from table 1 inner join table 2 on table and table relation where Condition
Eg: select student table. Name, curriculum. Course name from student table inner join Course table on student table. course No. = curriculum. course No. (data of the table and table is displayed)
(11) select Table 1. field 1, table 1. field 2, table 2. field 1, table 2. field 2, table 3. field 1, table 3. field 2 from table 1, Table 2, Table 3 where relationship between table and relationship between table eg: select student table. name, course schedule. course name, system table. department name from student table, curriculum, Department table where student table. course No. = course schedule. course number and student table. student ID = system table. student ID
(12) select Table 1. field 1, table 1. field 2, table 2. field 1, table 2. field 2, table 3. field 1, table 3. field 2 from (Table 1 inner join table 2 on table and table relationship) inner join table 3 on table and table relationship
Eg: select student table. name, course schedule. course name, system table. system name from (student table inner join Course table on student table. course No. = course schedule. course No.) inner join table on student table. student ID = system table. student ID