SQL Server Performance Tuning 4 write-efficient queries

Limit query rows and Leleti high-performance

This rule is very simple, and here is no detail.

Use Search to parameterize (sargable conditions) to improve performance

Sargable by the search ARGument Able shorthand, literally means that the searching can be parameterized? is still more obscure ah ...

In summary, using sargable judgment can help the query optimizer make more efficient use of the index, and increase the likelihood of using index seek, we first split all the operators into groups.

• Sargable operator
  • =
  • >
  • >=
  • <
  • <=
  • Between
  • Like (wildcard must appear at the end, e.g. ' r% ')

• Non-sargable operator
  • !=
  • !>
  • !<
  • <>
  • Not EXISTS
  • Inch
  • Not in
  • Between
  • Like (wildcards appear in front, such as '%R ')
  • Not-like
  • Functions for a column

Knowing this, you can use the sargable operation as much as possible (for example, with multiple OR to replace in) to skillfully use the calculation operation to improve performance in the decision.

The calculation of the column in the WHERE clause results in non-sargable, which indirectly causes the index on the column to not be effectively exploited, so it should be avoided as much as possible. Unfortunately there is no automatic way to help you find these places, you must manually check the query scripts and use the performance tools to locate the problem.

We use two tables from the ADVENTUREWORKS2012 database to do an example:

• [AdventureWorks2012]. [HumanResources]. [Employee]
• [AdventureWorks2012]. [HumanResources]. [EmployeePayHistory]

Our first query extracts the basic information of the Employee table and obtains the latest data in the corresponding EmployeePayHistory table, and satisfies the hourlyrate*8<=152

SELECT  E.loginid,  e.jobtitle  , E.birthdate  , E.maritalstatus  , E.gender  , E.hiredate  , Ep. Hourlyrate  , EP. Ratechangedatefrom [AdventureWorks2012]. [HumanResources]. [Employee] As Ejoin (  Select    Max (BusinessEntityID) as BusinessEntityID    , Max (ratechangedate) as Ratechangedate    , rate as Hourlyrate  From    [adventureworks2012].[ HumanResources]. [EmployeePayHistory]  GROUP by rate    ) as EPON E.businessentityid=ep. Businessentityidwhere EP. hourlyrate*8<=152

The above query does a numeric calculation (*8) of the column before it is <= in where, and the index on that column is not valid.

A slight modification to the WHERE clause avoids this problem:

.... WHERE EP. Hourlyrate<=152/8

The index on the column will work if the column is not manipulated before <= judgment is modified. Look at the performance indicators before and after:

The previous query took up 53% of the overhead, the latter taking up 47%, the magnitude of the difference is not very large, but as the table data increases, the difference will gradually expand.

Improve performance by not performing function operations on columns in a decision

As with the previous paragraph, the function action for a column in the WHERE clause results in "non-sargable", which results in a performance degradation. There is no repetition of examples here.

Improve performance with definition of referential integrity

Defining referential integrity (declarative referential Integrity), or DRI, refers to the use of primary and foreign keys to ensure database integrity/consistency.

Often, the developer creates a primary key in the primary table and uses the primary key to go to the child table to fetch the associated data, but does not create a foreign key in the child table. Actually setting up a foreign key not only helps you to ensure the integrity/consistency of the database, but also improves performance when querying associated data, and we use an example to verify the argument:

1. We create a new two-sheet

IF object_id (' Productdemo ') is not a null  DROP TABLE productdemogoif object_id (' Productmodeldemo ') is not null  DROP TABLE Productmodeldemogoselect * into Productmodeldemo from Production.productmodelselect * to Productdemo from Pro Duction. Product WHERE ProductModelID is not Nullgo

2. Declare ProductModelID as a non-empty field on the child table Productdemo and establish the primary key

ALTER table Productdemoalter COLUMN productmodelid INT not nullgoalter table Productdemo ADD CONSTRAINT [PK_PRODUCTDEMO_PR Oductid]primary KEY CLUSTERED (  [ProductID] ASC) GO

3. Create a primary key on the main table Productmodeldemo:

ALTER TABLE productmodeldemo ADD CONSTRAINT [pk_productmodeldemo_productmodelid] PRIMARY KEY CLUSTERED (  ProductModelID ASC) GO

4. Execute a test query

SELECT  p.productid  , P.productmodelidfrom  Productdemo as Pjoin  Productmodeldemo as Pmon  P. PRODUCTMODELID=PM. Productmodelidwhere  P.productid=680go

The implementation plan is as follows:

5. Establishing an external association between child Fathers

ALTER TABLE productdemowith checkadd CONSTRAINT  fk_productdemo_productmodeldemo_productmodelidforeign KEY  ( ProductModelID) REFERENCES  Productmodeldemo (productmodelid) GO

To perform the 4th step query again, the execution plan is as follows:

The modified query only needs to perform an index search on the Productdemo table.

The not NULL is defined on the Foreign key field, which guarantees that the data in the child table must have association information in the parent table, and the optimizer "trusts" the child table (without having to access/check the parent table without retrieving the parent table data).

"Trust" foreign key for performance improvements

Use the Sys.foreign_keys system view to obtain information about the foreign key, and the Is_not_trusted field indicates whether the foreign key is "trusted".

To establish a foreign key that is trusted, you can use the with CHECK parameter in the Create script, as described in the preceding script.

Note

Finally add a few more:

• Specify as many query filter criteria as possible
• You can use no ORDER by
• The items in the GROUP by clause are as few as possible and are taken from the same table as possible
• Use numeric type fields as much as possible in the GROUP by clause to avoid text
• The fields in GROUP by and ORDER by are taken from the same table as possible