Database Reference
In-Depth Information
Implementing query hints for optimized
performance
In continuation of the previous recipe, the query hints are useful when you are in control on
table design and data, and not to mention about data distribution, which is a critical aspect to
obtain optimized performance. However, the query hints implementation is not a compulsion
to use at all times. The hints will always provide a short-term result - that satisfies the
existing result set - which may not be efficient over a period of time due to the data changes
on the table. A new enhancement has been added to the OPTIMIZE FOR UNKNOWN query
hint to include new statistical data to determine values for local variables during the query
optimization instead of default values. Similarly, if the data is spread across multiple tables
(skewed data distribution), the OPTIMZIZE FOR clause could be used to optimize for a
generic value. In such situations, this query hint might provide reasonable performance for a
wide range of parameter values.
In this recipe, we will go through the high-level demonstration of implementing 'essential'
query hints for the optimized performance of a data platform. The essential query hints used
in this recipe are OPTIMIZE FOR UNKNOWN and RECOMPILE , which are enhanced in SQL
Server 2008. We will use AdventureWorks2008R2 database in the code examples that are
used to generate conditional basis queries.
How to do it...
In this recipe, we will use a query for a report to find names of employees who have
sold a product. The following steps are required in implementing query hints for
optimized performance:
1.
Use the following TSQL statement along with the query statistics for the
preceding requirement:
SET STATISTICS IO ON
GO
USE AdventureWorks2008R2;
GO
SELECT DISTINCT p.LastName, p.FirstName,e.LoginID
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
WHERE p.BusinessEntityID IN
(SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID FROM Production.Product p
WHERE ProductNumber NOT like 'BK-R%')));
GO
 
Search WWH ::




Custom Search