Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 279

INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
When you run these two statements, examine the two plans. You'll see that adding the Join to the view
increases the number of reads from 1600 page reads to 2179 pages. The plan shows that the Employee
table is indeed examined twice when the view is joined back to the Employee table.
|--Clustered Index Scan(OBJECT:([Employee].[PK_Employee_EmployeeID] AS [e]))
|--Clustered Index Seek(OBJECT:([Employee].[PK_Employee_EmployeeID] AS [emp]))
This shows that while using Views is conceptually no different from coding the statement fully, this is an
instance where the optimizer can't figure out our intentions. The Optimizer doesn't parse the view SQL
and our additional SQL to combine for further optimization. A good rule of thumb is that if you need a
significantly narrower result set or to add columns from an additional relationship, seriously consider
creating a new view for this purpose. If you are just adding a new Join table not already in the view
definition as a filter, using the view adds no significant performance penalty.
ComplexViews
Complex views that include aggregates, common table expressions, or even TOP statements can
present an unexpected challenge. The performance issue here is that certain predicates can't be
applied early enough in the planning process. Aggregates that include grouping operations use
equivalency operations, so applying predicates that use different comparison rules must be delayed as
filter operations later in the planning process. Applying the predicate too early could change the base
view results, which violates the definition of views being resolved as if they were a virtual table. Take,
for example, an aggregate query as a view.
--First, Create this view
CREATE VIEW vSalesOrderHeaderByAccountNumber
AS
SELECT AccountNumber, count(*) As OrderCnt
FROM Sales.SalesOrderHeader
GROUP BY AccountNumber
Then run a comparison using an account number predicate against the view and against the statement
outside of the view.
--Against the view
SELECT *
FROM vSalesOrderHeaderByAccountNumber
WHERE AccountNumber LIKE '10-4020-000210'
--Statement separated from the view
SELECT AccountNumber, count(*) As OrderCnt
FROM Sales.SalesOrderHeader
WHERE AccountNumber LIKE '10-4020-000210'
GROUP BY AccountNumber
When you run these two comparisons, you'll notice that the estimated costs for the query against the
complex view is 98 percent of the batch cost compared to 2 percent of the straight statement. This is an
example of how a view can impact performance negatively. The plans tell the whole picture. The plan for