General experience in designing efficient SQL statements

1You do not needSQLThe statement uses the default reserved keywords.

2Try to useExistsAndNot existsReplaceInAndNot in

ThisSql2005Then, when the index and statistics are the same,Exists,InThe results are the same.

ToAdventureworksFor exampleHumanResources.EmployeeaddressAddresses availableEmployeeInformation,

UseInThe statement is as follows:

Set Statistics Io On

 

Select * FromHumanResources.Employee

WhereEmployeeidIn (SelectEmployeeidFromHumanResources.Employeeaddress EA)

 

Set Statistics Io Off

After execution, the message is as follows:

 

(290Rows affected)

Table 'Employeeaddress' . Scan count 1 , Logical read 4 Physical reads 0 Times, pre-read 0 Times, Lob Logical read 0 Times, Lob Physical read 0 Times, Lob Pre-read 0 Times.

Table 'Employee' . Scan count 1 , Logical read 9 Physical reads 0 Times, pre-read 0 Times, Lob Logical read 0 Times, Lob Physical read 0 Times, Lob Pre-read 0 Times.

Execution Plan

 

UseExists, The statement is as follows,

 

Set Statistics Io On

Select * FromHumanResources.Employee

Where Exists (SelectEmployeeidFromHumanResources.Employeeaddress EA

Where

HumanResources.Employee.Employeeid=EA.Employeeid)

 

Set Statistics Io Off

After execution, the message is as follows:

 

(290Rows affected)

Table 'Employeeaddress' . Scan count 1 , Logical read 4 Physical reads 0 Times, pre-read 0 Times, Lob Logical read 0 Times, Lob Physical read 0 Times, Lob Pre-read 0 Times.

Table 'Employee' . Scan count 1 , Logical read 9 Physical reads 0 Times, pre-read 0 Times, Lob Logical read 0 Times, Lob Physical read 0 Times, Lob Pre-read 0 Times.

Execution Plan

 

3Try not to useSelect * from .....,The field name must be written.Select field1, field2 ,...

There is nothing to say about this article, mainly for on-demand queries, do not return unnecessary columns and rows.

 

4InSQLIndex columns should be used as much as possible to speed up the query

5AnyOrderNon-index items or computed expressions of a statement can reduce the query speed.

6AnyWhereClauseIs nullOrIs not nullThe statement of is not allowed to use the index, which is less efficient.

7Wildcard%At the beginning of a word, the system does not use an index. When a wildcard appears at another location, the optimizer can use the index.

8InSQLUse as few formats as possible in the query statement

9Any operation on the column will cause the table to scan, including database functions and calculation expressions. During the query, try to move the operation to the right of the equal sign.

10 In,OrThe clause usually invalidates the index.

11Generally, the connection is more efficient than the subquery.