10.1 Order problems for connecting query tables
The parser for SQL Server processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (the underlying table driving tables) will be processed first, and in the case of multiple tables in the FROM clause, the tables with the fewest number of record bars must be selected as the underlying table. When SQL Server processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and sort the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records from the first table
If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which is the table referenced by the other tables
10.2 The order of where conditions
SQL Server parses the WHERE clause in a bottom-up order, and according to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause
Avoid using ' * ' in the 10.3 select clause.
When listing all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column, which unfortunately is a very inefficient approach. In fact, SQL Server will convert ' * ' to all column names in the process of parsing, which is done by querying the data dictionary, which means more time is spent
10.4 Decrease number of queries 10.5 replace the HAVING clause with a WHERE clause 10.6 reduce query on table 10.7 Replace in with exists
In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition, in which case the use of EXISTS (or not EXISTS) will usually improve the efficiency of the query
Low efficiency
SELECT *
FROM dbo. Orders
WHERE id_p>0 and Id_p in (SELECT Id
FROM dbo. Persons
)
Efficient
SELECT *
From Orders
WHERE id_p >0 and EXISTS (SELECT Id
From Persons WHERE persons.id = orders.id_p
)
10.8 Replace not in with not exists
In a subquery, the NOT IN clause performs an internal sort and merge, in either case, not in is the least effective because it performs a full table traversal of the table in the subquery, and in order to avoid using not, it can be written as an outer join (Outer Joins) or not EXISTS
Low efficiency
SELECT *
FROM dbo. Orders
WHERE id_p>0 and Id_p not in (SELECT Id
FROM dbo. Persons
)
Efficient
SELECT *
From Orders
WHERE id_p >0 and not EXISTS (SELECT Id
From Persons WHERE persons.id = orders.id_p
)
10.9 replacing exists with a table connection
10.10 Replace distinct with exists
Low efficiency
SELECT DISTINCT id_p
From Orders, Persons
WHERE orders.id_p = persons.id
Efficient
SELECT id_p
From Orders
WHERE EXISTS (SELECT ID
From Persons
WHERE persons.id = orders.id_p);
10.11 improve efficiency with indexes
Do not use fuzzy queries on indexes
Do not calculate on an indexed column
Indexed columns do not add is null or is not NULL
10.12 Use Union All and the Union
The content of this article is learning
Http://www.cnblogs.com/zhougb/archive/2009/05/05/1449708.html.
If you are suspected of infringing your rights, please contact me promptly
SQL Basic Series (4)-Performance tuning recommendations