Summary of SQL query efficiency considerations. For more information, see.
Summary of SQL query efficiency considerations. For more information, see.
1. the query conditions are accurate.
Ii. SQL logic execution sequence
FROM --> JOIN --> WHERE --> GROUP --> HAVING --> DISTINCT --> ORDER --> TOP
Iii. Horizontal
Query required fields
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column, this reduces the parsing time and syntax errors caused by Column ambiguity.
4. less repetitive work
It is seldom noticed by many programmers to control multiple executions of the same statement, especially the multiple executions of some basic data.
Reduce multiple data conversions
Eliminate unnecessary subqueries and connection tables. subqueries are generally interpreted as external connections in the execution plan, resulting in additional overhead for redundant connection tables.
V. Zero-time table # And table variable @
If the statements are complex and have too many connections, you can use temporary tables and table variables for step-by-step execution.
If you need to use the same part of the data of a large table multiple times, consider using temporary tables and table variables to save this part of data.
If you need to combine the data of multiple tables to form a result, you can use temporary tables and table variables to summarize the data of these tables step by step.
With regard to the selection of temporary tables and table variables, the speed of temporary tables is faster when there is a large amount of data.
Select into is faster than the create table + insert into method, but select into locks system tables SYSOBJECTS, SYSINDEXES, and SYSCOLUMNS of TEMPDB. In multi-user concurrent environments, it is easy to block other processes.
6. subquery
Subqueries can be introduced using IN, not in, EXISTS, NOT EXISTS
For subqueries not in and not exists, use left join instead.
If the subqueries are not repeated, the subqueries related to IN and EXISTS can be replaced by inner join.
IN subqueries are replaced by EXISTS.
VII. Index
Avoid Calculation of index fields
Select id from t where num/2 = 100
Should be changed:
Select id from t where num = 100*2
Avoid using NOT, <> ,! =
Avoid using is null and is not null in the index column.
Avoid data type conversion on index Columns
Avoid using functions on indexed fields
Avoid using NULL values in indexed Columns
Do not connect multiple fields in the index.
Where fame + '.' + LNAME = 'haiwei. yang'
Should be changed:
Where fname = 'haiwei' and lname = 'yang'
8. Multi-table join
When connecting multiple tables, the connection conditions must be fully written. It is better to repeat the conditions and avoid missing data.
Use clustered indexes as much as possible in connection conditions
9. Others
In a statement that can use union all, use UNION ALL
Avoid using IN, not in, or in the WHERE clause
Avoid resource-consuming operations. SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by enable SQL engine execution and resource-consuming sorting (SORT. DISTINCT requires a sorting operation, while other operations require at least two sorting operations.
LIKE '% 100' does not reference the index, while LIKE 'x5400%' references the range index.
