Programmer's experience in writing SQL statements

For management systems, both bs‑structured and CS-structured systems inevitably involve the design of database table structures and the compilation of SQL statements. Therefore, during system development, whether the table structure design is reasonable, whether SQL statements are standard, and whether the SQL Performance written is optimized is often measured by the company.ProgramTechnical standards of personnel.

Our programmers are not DBAs and do not need to keep an eye on the SQL running time. They try to optimize the table structure, storage space, and read speed. However, when developing the system, it is necessary to maintain a good style of writing SQL statements at all times, which is related to the reputation of individuals in the company...

The new programmer laruence, in a development team, needs to show his own level, lay the position in the company, and need to work hard to show, the simplest SQL statement is easy to express. Once upon a time, an old programmer was positioned to be a team leader. First, I tried to create a ticket-making module, in the list SQL, the old bird directly writes a SELECT statement from other tables, instead of associating them with other tables. This damages the brilliant image of the programmer.

To do the technology, you must pay attention to your own content and improve your internal strength. Haha.

To sum up the skills that programmers can easily write SQL statements:

1. No matter how many tables are involved in an SQL statement, two tables (result set) are used each time to obtain new results and then operate with the next table (result set.

2. Avoid getting field columns in select F1, (select F2 from tableb)... from tablea. Associate tablea with tableb to obtain a. F1, B. F2.

3. avoid implicit type conversion
for example,
select ID from employee where emp_id = '8' (incorrect)
select ID from employee where emp_id = 8 (right)
emp_id is an integer type. If '8' is used, type conversion is started by default, increasing the query overhead.
4. Avoid using regular expressions and avoid using wildcards.

5. use keywords to replace functions
For example:
select ID from employee where upper (Dept) Like 'tech _ db' (error)
select ID from employee where substr (Dept,) = 'tech '(error)
select ID from employee where dept like 'tech %' ()
6. do not use a conversion function on fields. Use
For example:
select ID from employee where to_char (create_date, 'yyyy-mm-dd') on constants whenever possible ') = '2017-10-31 '(incorrect)
select ID from employee where create_date = to_date ('2017-10-31', 'yyyy-mm-dd') (right)
7. do not use join for query
For example: Select ID from employee where first_name | last_name like 'jo % '(error)
8. avoid using wildcards
For example:
select ID from employee where dept like '% tech %' (error)
select ID from employee where dept like 'tech % '(pair)

9. determine the order of conditions
For example:
Select ID from employee where creat_date-30> to_date ('1970-10-31 ', 'yyyy-mm-dd') (error)
Select ID from employee where creat_date> to_date ('1970-10-31 ', 'yyyy-mm-dd') + 30 (on)

10. Try to use exists instead of in
Of course, the use of exists or in should also be determined based on the record. Generally, the use of exists
Select ID from employee where salary in (select salary from emp_level where...) (error)
Select ID from employee where salary exists (select 'x' from emp_level where...) ()
 
11. Use not exists instead of not in
Similar to the above

12. Reduce the number of records in the query table

13. Correct Use of Indexes
The indexing speed can be improved. Generally, the higher the selection degree, the higher the indexing efficiency.

14. Index type
Unique index: Use unique index whenever possible for the fields used for query.
There are other types, such as Bitmap indexes, which are used only for gender fields.

15. Create an index on a column that is frequently connected but not specified as a foreign key

16. Create an index on the columns that are grouped by frequent sorting, for example, fields that are frequently operated by group by or order.

17. You can create searches for columns with different values that are frequently used in conditional expressions. No indexes are created for columns with fewer values. For example, if the gender column contains only male and female values, you do not need to create an index (or create a bitmap index ). If an index is created, the query efficiency is not improved, but the update speed is greatly reduced.

18. When order by is performed for a field with a relatively small value, record disorder may occur during page turning. Order by should be performed with the ID field.

19. Do not use an empty string for query.
For example:
Select ID from employee where emp_name like '%' (error)

20. Try to index key fields that are often used as group by keywords.

21. Use table join correctly
The not in operation, which is inefficient at replacing external connections, greatly improves the running speed.
For example:
Select a. ID from employee a where a. emp_no not in (select emp_no from employee1 where job = 'sale') (error)

22. Use a temporary table
To reduce the number of reads, you can use an indexed temporary table to speed up when necessary.
For example:
Select E. ID from employee e, DEPT d Where E. dept_id = D. id and E. empno> 1000 order by E. ID (error)

Select ID, empno from employee into temp_empl where empno> 1000 order by ID
Select M. ID from temp_emp1 M, DEPT d Where M. empno = D. id ()




To optimize the performance of SQL statements with a large amount of data, we should hand over to the dBA for practice. Our programmers should do these basic skills well.

For management systems, both bs‑structured and CS-structured systems inevitably involve the design of database table structures and the compilation of SQL statements. Therefore, when developing a system, whether the table structure design is reasonable, whether the SQL statements are standard, and whether the SQL performance to be written is optimized often become the technical standard for the company to measure the programmer level.

Our programmers are not DBAs and do not need to keep an eye on the SQL running time. They try to optimize the table structure, storage space, and read speed. However, when developing the system, it is necessary to maintain a good style of writing SQL statements at all times, which is related to the reputation of individuals in the company...

The new programmer laruence, in a development team, needs to show his own level, lay the position in the company, and need to work hard to show, the simplest SQL statement is easy to express. Once upon a time, an old programmer was positioned to be a team leader. First, I tried to create a ticket-making module, in the list SQL, the old bird directly writes a SELECT statement from other tables, instead of associating them with other tables. This damages the brilliant image of the programmer.

To do the technology, you must pay attention to your own content and improve your internal strength. Haha.

To sum up the skills that programmers can easily write SQL statements:

1. No matter how many tables are involved in an SQL statement, two tables (result set) are used each time to obtain new results and then operate with the next table (result set.

2. Avoid getting field columns in select F1, (select F2 from tableb)... from tablea. Associate tablea with tableb to obtain a. F1, B. F2.

3. Avoid implicit type conversion
For example
Select ID from employee where emp_id = '8' (error)
Select ID from employee where emp_id = 8 (pair)
Emp_id is an integer type. If '8' is used, type conversion is started by default, increasing the query overhead.
 
4. Avoid using regular expressions and avoid using wildcards.

5. use keywords to replace functions
For example:
select ID from employee where upper (Dept) Like 'tech _ db' (error)
select ID from employee where substr (Dept,) = 'tech '(error)
select ID from employee where dept like 'tech %' ()
6. do not use a conversion function on fields. Use
For example:
select ID from employee where to_char (create_date, 'yyyy-mm-dd') on constants whenever possible ') = '2017-10-31 '(incorrect)
select ID from employee where create_date = to_date ('2017-10-31', 'yyyy-mm-dd') (right)
7. do not use join for query
For example: Select ID from employee where first_name | last_name like 'jo % '(error)
8. avoid using wildcards
For example:
select ID from employee where dept like '% tech %' (error)
select ID from employee where dept like 'tech % '(pair)

9. determine the order of conditions
For example:
Select ID from employee where creat_date-30> to_date ('1970-10-31 ', 'yyyy-mm-dd') (error)
Select ID from employee where creat_date> to_date ('1970-10-31 ', 'yyyy-mm-dd') + 30 (on)

10. Try to use exists instead of in
Of course, the use of exists or in should also be determined based on the record. Generally, the use of exists
Select ID from employee where salary in (select salary from emp_level where...) (error)
Select ID from employee where salary exists (select 'x' from emp_level where...) ()
 
11. Use not exists instead of not in
Similar to the above

12. Reduce the number of records in the query table

13. Correct Use of Indexes
The indexing speed can be improved. Generally, the higher the selection degree, the higher the indexing efficiency.

14. Index type
Unique index: Use unique index whenever possible for the fields used for query.
There are other types, such as Bitmap indexes, which are used only for gender fields.

15. Create an index on a column that is frequently connected but not specified as a foreign key

16. Create an index on the columns that are grouped by frequent sorting, for example, fields that are frequently operated by group by or order.

17. You can create searches for columns with different values that are frequently used in conditional expressions. No indexes are created for columns with fewer values. For example, if the gender column contains only male and female values, you do not need to create an index (or create a bitmap index ). If an index is created, the query efficiency is not improved, but the update speed is greatly reduced.

18. When order by is performed for a field with a relatively small value, record disorder may occur during page turning. Order by should be performed with the ID field.

19. Do not use an empty string for query.
For example:
Select ID from employee where emp_name like '%' (error)

20. Try to index key fields that are often used as group by keywords.

21. Correct Use of table Association
the not in operation with low replacement efficiency of external connections greatly improves the running speed.
example:
select. ID from employee a where. emp_no not in (select emp_no from employee1 where job = 'sale') (error)
22. use a temporary table
if necessary, you can use a temporary table that has been indexed to speed up reading.
example:
select e. ID from employee e, DEPT d Where E. dept_id = D. ID and E. empno & gt; 1000 order by E. ID (error)
select ID, empno from employee into temp_empl where empno> 1000 order by ID
select M. ID from temp_emp1 M, DEPT d Where m. empno = D. ID (yes)
if you want to optimize the performance of SQL statements with a large amount of data, you can leave it to dBA for practice, our programmers should do well in these basic skills.