9 things to remember about SQL command optimization _mssql

The basic language for interacting with a database is SQL, and many steps are required to parse and execute SQL statements every time a database is executed. In SQL Server, for example, when a database receives a query, the parser scans the SQL statement and divides it into logical units (such as keywords, expressions, operators, and identifiers) and builds the query tree, and the last query optimizer analyzes all the methods that can access the source table of the database. Select a set of steps from which to return the result set that is the fastest and less resource consuming. The query tree is updated to accurately record this step, then the database engine starts execution, and then returns the query results to the user. It is obvious that the database engine executes SQL commands every time there is a large overhead, if the quality of the submitted SQL is not high or even logic errors can cause unnecessary overhead and waste of time. To avoid this, you should be aware of the following guidelines when using SQL commands:

1, the field extraction according to "How much, how much" principle, avoid "select *", try to use "Select field 1, Field 2, Field 3 ...". Practice has proved that the extraction speed of database will be improved with each less extraction of one field. The speed of Ascension is determined by the size of the field you have discarded.

2, try to use exists instead of SELECT COUNT (*) to determine whether there is a record. The optimizer supports short-circuit functionality when optimizing the EXISTS predicate. As long as you find a line, you can determine whether the table is covered by a row without having to scan the other rows. The Count function is used only for the number of rows in all rows in the statistics table.

3, try to use (not) exists instead of (not) in operation, in the SQL performance is always lower.

  --Statement

  Select Dname,deptno from dept where Deptno is 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, as far as possible using not in, you can use the left outer join replace it.

5, try not to use or, use or will cause full table scan, will greatly reduce query efficiency

6, note the way clause, must consider the order of the sentence, should be based on the index order, range size to determine the order of the conditional clauses, as far as possible to make the field order and the index order, ranging from large to small.

7, try to use ">=", Do not use ">"

8, understand the table's index structure before writing SQL statements. Effective use of the index can avoid unnecessary full table scan and shorten the query time. You should avoid using is NULL in the WHERE clause, <>,! =, not, don't exist, not in, and not, and so on, they usually cause a full table scan to invalidate the index.

9. In the WHERE clause, any action (function, calculation, etc.) on the column causes the index to fail, and these operations should be moved as far as possible to the right side of the equal sign, where substring (id,1,1) = ' A ' should be written as where id like ' a% '; where Result*10> 30 should be written as where result >30;

The basic principle of optimizing SQL commands is to minimize type conversions and calculations, take advantage of table indexes, and reduce the number of total table scans.