SQL Basic Series (4)-Performance tuning recommendations

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