Views and Query Performance

Views typically do not add any additional overhead to query performance. A SELECT statement against a view is just as fast as the underlying query used to define the view. When a view is combined with other search arguments or join conditions in a query, the query optimizer combines the source of the view and the SQL statement into a single execution plan. Since it doesn't generate separate query plans for the view and the SQL statement, the view result doesn't have to be materialized first before it can resolve the rest of the query. Consider the first query shown in Listing 27.11 that references the High_Sales_View view, which was defined in Listing 27.6. The query referencing the view generates the same execution plan as the second query shown in Listing 27.11, which contains a three table join.

Listing 27.11 Queries That Generate the Same Execution Plan

-- Query that references the High_Sales_View
SELECT v.orderid, v.customerid, v.quantity, v.productid
   FROM High_Sales_View v
   JOIN Customers c
     ON v.customerID = c.customerID
   WHERE c.city = 'London'
go

-- Query that references the actual tables
SELECT o.orderid, o.customerid, od.quantity, od.productid
   FROM orders o
   JOIN [order details] od
     ON o.orderid = od.orderid
   JOIN Customers c
     ON o.customerID = c.customerID
   WHERE c.city = 'London'

One exception to this optimization strategy is when the view contains an aggregate function and the query contains a search argument against the aggregate column. Because the resultset for the view is essentially the contents of a worktable, the view must be materialized first to generate this worktable before a search can be performed on the aggregate column, or before it can be joined with other tables outside of the view definition. Listing 27.12 presents a view that calculates a grouped SUM aggregate. The first query that references the total_customer_sales view generates a different execution plan than the second query, which references the base tables only. The execution plan for the query using the view is less efficient than the three-table join as it first calculates the sum of the quantity field for all orders before joining that resultset with the Customers tables and evaluating whether the Customer city is "London." The second query first finds the orders for the customers from London and then calculates the sum of quantity for only those orders.

Listing 27.12 Queries That Generate Different Execution Plans Due to Aggregation in View Definition

CREATE VIEW total_customer_sales
AS
SELECT o.customerid, sum(od.quantity) as total_sales
   FROM orders o
   JOIN [order details] od
     ON o.orderid = od.orderid
   group by o.customerid
go

-- Query that references the High_Sales_View
SELECT v.customerid, v.total_sales
   FROM total_customer_sales v
   JOIN Customers c
     ON v.customerID = c.customerID
   WHERE c.city = 'London'
     and v.total_sales > 100
go

-- Query that references the actual tables
SELECT o.customerid, sum(od.quantity) as total_sales
   FROM orders o
   JOIN [order details] od
     ON o.orderid = od.orderid
   JOIN Customers c
     ON o.customerID = c.customerID
   WHERE c.city = 'London'
   group by o.customerid
   having sum(od.quantity) > 100

For more information on Query Optimization, see Chapter 35.