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.