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.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net