SQL Server Performance Tuning 4: Writing efficient queries, SQL Server Tuning

SQL Server Performance Tuning 4: Writing efficient queries, SQL Server Tuning
Restrict query rows and columns to improve performance

This rule is very simple and will not be detailed here.

Use search to optimize the performance by parametric judgment (sargable conditions)

Sargable is short for Search ARGument Able, which literally means Search parameterization? Still relatively obscure...

In short, the use of Sargable can help the query optimizer to use indexes more effectively and improve the possibility of using index seek. We will first divide all operators into groups.

• Sargable Operator
  • =
  • >
  • > =
  • <
  • <=
  • BETWEEN
  • LIKE (the wildcard must appear at the end, for example, 'r % ')

• Non-Sargable Operator
  • ! =
  • !>
  • ! <
  • <>
  • NOT EXISTS
  • IN
  • NOT IN
  • BETWEEN
  • LIKE (wildcard appears before, for example, '% R ')
  • NOT LIKE
  • Functions for a column

With this knowledge, you can try to use the Sargable operation (for example, replacing IN with multiple OR operations) to skillfully use the computing operation IN the judgment to improve the performance.

The column calculation operation in the WHERE clause will result in non-Sargable, which indirectly leads to the failure of column indexes to be effectively used. Therefore, we should avoid this as much as possible. Unfortunately, there is no automatic way to help you find these places. You must manually check the query script and Use Performance Tools to locate the problem.

Let's use two tables in the AdventureWorks2012 database as an example:

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

The first query extracts the basic information of the Employee table and obtains the latest data in the corresponding Employee History Table, meeting 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 performed a numerical calculation (* 8) on the column before the WHERE clause was judged, and the index on the column became invalid.

You can avoid this problem by slightly modifying the WHERE clause:

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

After modification, the column is not operated before <= judgment, so the column index will take effect. Take a look at the performance indicators before and after:

The previous query consumes 53% of the overhead, and the latter occupies 47%. Although the difference between the two is not very large, the difference will gradually expand as the table data increases.

If the column is not determined, function operations are performed on the column to improve the performance.

Similar to the principle in the previous section, function operations on columns in the WHERE clause may result in "non-Sargable", resulting in performance degradation. Here we will not repeat the examples.

Improve Performance with definition reference integrity

Declarative Referential Integrity (DRI) refers to the use of primary keys and Foreign keys to ensure database Integrity/consistency.

This is often the case where the developer creates a primary key in the primary table and uses this primary key to fetch associated data from the subtable, but does not create a foreign key in the subtable. In fact, setting up a foreign key not only helps you ensure database integrity/consistency, but also improves the performance when querying associated data. We use an example to verify this statement:

1. Create two tables

IF OBJECT_ID('ProductDemo') IS NOT NULL  DROP TABLE ProductDemoGOIF OBJECT_ID('ProductModelDemo') IS NOT NULL  DROP TABLE ProductModelDemoGOselect * into ProductModelDemo from Production.ProductModelselect * into ProductDemo from Production.Product WHERE ProductModelID is not nullGO

2. Declare the ProductModelID field in the ProductDemo sub-table as a non-empty field and create it as a primary key.

ALTER TABLE ProductDemoALTER COLUMN ProductModelID INT NOT NULLGOALTER TABLE ProductDemo ADD CONSTRAINT [PK_ProductDemo_ProductID]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 test Query

SELECT  P.ProductID  ,P.ProductModelIDFROM  ProductDemo AS PJOIN  ProductModelDemo AS PMON  P.ProductModelID=PM.ProductModelIDWHERE  P.ProductID=680GO

The execution plan is as follows:

5. Establish external associations between child and parent

ALTER TABLE ProductDemoWITH CHECKADD CONSTRAINT  FK_ProductDemo_ProductModelDemo_ProductModelIDFOREIGN KEY  (ProductModelID)REFERENCES  ProductModelDemo(ProductModelID)GO

Execute the query in step 1 again. The execution plan is as follows:

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

The foreign key field defines not null to ensure that the data in the sub-table must have association information in the parent table, the optimizer then "trusts" the sub-table (without retrieving the parent table data, you do not need to access/check the parent table again ).

"Trust" foreign key for performance improvement

The System View of sys. foreign_keys can be used to obtain information about the foreign key. The is_not_trusted field indicates whether the foreign key is "trusted ".

To create a foreign key that is "trusted", you can use the with check parameter in the creation script. For more information, see the preceding script.

Remarks

Finally, add the following points:

• Specify as many filter conditions as possible
• Order by is not required.
• The group by clause contains as few items as possible and is retrieved from the same table as much as possible.
• Use numeric fields as much as possible in the group by clause to avoid text
• Fields in group by and order by should be retrieved from the same table as much as possible.

SQL Server 2005 performance optimization 1. index function enhanced online index options allow concurrent modification of base table or clustered index data and any associated indexes when the index data definition language is executed. Then, users can intervene to add indexes when accessing tables or their existing indexes.
2. shrinking improves query performance for large tables and index partitions.
3. Table and index partitioning. The database is smaller and easier to manage blocks, which simplifies the management of large data.
4. Enhanced Transact-SQL
How can I optimize the performance of SQL statements ?? There are several common causes of slow query speed:

1. No index or no index (slow query FAQ program design defects)

2. Low I/O throughput results in bottlenecks

3. query optimization due to the absence of computing Columns

4. Sufficient memory

5. slow network speed

6. The queried data volume is too large (using multiple queries to reduce the data volume)

7. Lock or deadlock (also query slow FAQ program design defects)

8. sp_lock, sp_who, view by the active user, read and write competing resources for reasons

9. Required rows and columns are returned.

10. The query statement is not optimized.