The experience of writing SQL statements by veteran programmers

The management system, whether it is a BS structure or CS structure, is inevitably related to the database table structure design, SQL statement writing and so on. Therefore, in the development of the system, the table structure design is reasonable, whether the SQL statement is standard, write the SQL performance optimization will often become the company to measure the level of programmer's standards.

We programmers are not DBAs, do not need to pay attention to SQL Runtime, find ways to optimize the table structure, storage space, optimize table read speed and so on, but in the development of the system, always keep good writing SQL statement style is very necessary, it is related to personal reputation in the company, hey, you understand ...

New to the programmer of old birds, in a development team, need to show their own level, lay in the company's position, the need to work hard to show a, the simplest from the SQL statement written is easy to show, once, an old programmer, the above positioning is to do a team leader, first experience to do a single system of modules, List SQL there's a column. The old bird directly wrote a SELECT statement from the other table, instead of using the association between the table to get the image of destroying their own programmer old birds Glorious.

Do the technology or to pay attention to their own connotation, enhance the internal strength, haha.

Gossip less, summed up a bit of programmer veteran write SQL handy:

1. Regardless of how many tables are involved in one SQL, each time you use two tables (result sets), you get new results, and then you work with the next table (result set).

2. Avoid the field column in select F1, (select F2 from TableB) ... from TableA. Directly with TableA and TableB Association to get A.F1,B.F2 on it.

3. Avoid implicit type conversions
  such as  
 select ID from employee where emp_id= ' 8 '   (wrong)
 select ID from Employee where emp_id=8    (pair)
 emp_id is an integer type, with ' 8 ' will start the type conversion by default, increasing the cost of the query.
 
4. Try to minimize the use of regular expressions and try not to use wildcard characters.

5. Use keywords instead of functions
   such as:
   select ID from Employee where UPPER (dept) like ' tech_db '   (wrong)
   Select ID from Employee where SUBSTR (dept,1,4) = ' TECH '      (wrong)
   Select ID from the employee where dept like ' tech% '           (pair)
 
6. Do not use a conversion function on a field, try to use
  as:
  Select ID from employee where TO_CHAR (create_date, ' yyyy-mm-dd ') = ' 2012-10-31 '   (wrong)
  Select ID from Employee where create_date=to_date (' 2012-10-31 ', ' yyyy-mm-dd ')      (to)
  
7. Do not use joins to query
  such as: Select ID from employee where first_name | | last_name like ' Jo % '   (wrong)
 
8. Try to avoid using wildcards before and after
  such as:
  Select ID from the employee where dept like '%tech% ' (wrong)
&N Bsp Select ID from the employee where dept like ' tech% ' (pair)

9. Judging the order of conditions
Such as:
Select ID from Employee where creat_date-30>to_date (' 2012-10-31 ', ' yyyy-mm-dd ') (wrong)
Select ID from Employee where creat_date >to_date (' 2012-10-31 ', ' Yyyy-mm-dd ') +30 (yes)

10. Try to use exists instead of in
Of course this also depends on the record of the situation to decide whether to use exists or in, the usual situation is to use exists
Select ID from the employee where salary in (select salary from Emp_level where ...) Wrong
Select ID from the employee where salary exists (select ' X ' from Emp_level where ...) To

11. Using not exists instead of not
and similar to the above

12. Reduce the range of records in the query table

13. Correct use of the index
Indexes can improve speed, in general, the higher the selection, the more efficient the index.

14. Index Type
A unique index, where possible, to use a unique index for the fields used by the query.
There are also some other types, such as bitmap indexes, that are used in the gender field, only for men and women fields.

15. Indexing on a column that is frequently connected but not specified as a foreign key

16. Indexes on columns that are frequently sorted, such as fields that are frequently group by or order by operations.

17. Create a search on columns that are often used in conditional expressions with a higher number of values, and do not index on columns with fewer values. There is no need to index (or create a bitmap index) if there are two different values for male and female on the gender column. If you build an index, it will not improve the query efficiency, but can seriously reduce the update speed.

18. When you make an order by in a field with a lower value, the page turns on a record-disordered issue, with the ID field in order by.

19. Do not query with an empty string
Such as:
Select ID from the employee where Emp_name like '% ' (wrong)

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

21. Correct use of table associations
The use of external connections to replace the inefficient not-in operation, greatly improve the speed of operation.
Such as:
Select a.ID from employee a where a.emp_no not in (select Emp_no from employee1 where job = ' SALE ') (wrong)

22. Using Temporary tables
If necessary, to reduce the number of reads, you can use an indexed temporary table to speed up.
Such as:
Select E.id from Employee E, Dept D where E.dept_id=d.id and e.empno>1000 order by e.id (wrong)

Select Id,empno from employee to Temp_empl where empno>1000 order by ID
Select M.id from Temp_emp1 m,dept D where m.empno=d.id (pair)




For large data volume SQL statement performance optimization More work is given to the DBA to practice, and our programmers are good at doing these basic skills.

Transfer from http://www.cnblogs.com/webreport/archive/2012/10/11/2720240.html

The programmer's experience in writing SQL statements