The result set from these two queries is the same. However, the view query gathers data from five tables but needs data from only the Customers and Orders tables. Because the joins to Order Details, Products, and Categories only further describe the data, the view's inner join doesn't change the number of rows in the join. In fact, this schema is called a snowflake schema. Imagine a snowflake comprised of the query's components. The largest table — Order Details — is the fact table. This fact table branches out to descriptor tables, or lookup tables (e.g., Products), which in turn branch out to additional descriptor tables (Categories). When you're looking only for details about an existing Order Details row (the join to the Products table), your result set doesn't increase or decrease in size; you only further describe the Order Details data. For example, in the Order Details table, you can see information about products. Each Order Details row has a specific ProductID ordered, and each Order Details row only exists with a valid ProductID. The database design (which shows that the ProductID column doesn't allow NULLs) and referential integrity constraints (which tell you that a foreign key is defined from [Order Details].ProductID to Products.ProductID) give you this information. Therefore, every Order Details row produces exactly one valid ProductID. In this case, the number of rows joining from Order Details to Products doesn't change. When you use the view query, you force the five-table join and cause a severe performance hit. However, the base table query accesses only two tables and promotes smooth performance. So why join five tables when you can join only two?
Let's look at the example query in a different way. When you want to access detailed information about the category (such as CategoryName) and information about the product (such as ProductName), you must apply the join to the Order Details, Category, and Products tables. If some products don't have a CategoryID, the join eliminates Order Details rows that lack a CategoryID. In the Northwind database, however, all products have a valid CategoryID, and the Products and Category tables are used only as supporting or descriptor tables. In this database, queries that join to the Products and Category tables will only detail the data and not increase or decrease the number of rows that the set defines.
So where's the problem? Listing 3 shows the view query, which uses a WHERE clause against the five-table join view, and the simplified two-table base-table query. Figure 2, page 14, shows their respective execution plans. The five-table join against the view is the first (top) plan, and the rewritten two-table join is the second (bottom) plan. The first thing you notice is the difference in the plans. The first plan is more complex (it contains more items) than the second. The Query cost (relative to the batch) numbers are also significant. By running both queries at the same time, you can determine their cost relative to the batch as well as their cost relative to each other. The first plan costs 82 percent of the batch plan, and the second plan costs a little more than 18 percent of that plan. These results show that the first plan is more than four times as expensive in resource consumption as the second plan. What started as a seemingly simple view to hide the complexity of a five-table join was abused. Instead of querying the data directly, the user incorrectly queried the view. This behavior is typical: Users who write their own queries often become comfortable with one or two important views, which they query for all the data they need. Poor performance follows. In this case, to access a list of customers who've placed an order and whose name begins with B, you need a view that accesses only the two relevant tables — Orders and Customers.
Views are flexible and can limit the data returned to the user. By working closely with users to determine the information they need, developers can maintain better control of the requested data and keep the server running smoothly. Here are a few questions developers can ask users to determine whether they're using the views for other than their intended purposes:
- Do you use DISTINCT a lot? This question should generate a "no" answer. If users constantly filter out duplicate rows, they might be querying the wrong view for the information. Returning excess data from the wrong view requires users to eliminate the rows that the join gathered.
- Do you always ask for SELECT * against your views, or do you often eliminate the columns from the view query? The preferred answer to this question is choosing SELECT *, which is a sign that users are querying the view correctly.
- Do you use different views, or does one view seem to support most of your queries? Developers hope to hear that the user relies on a variety of views. Sometimes, users find a view that answers most of their data needs. Then, they continue to use that view for all their queries, constantly tweaking the view to return the exact data they require. More than likely, though, the tweaking slows query performance — and the server.
Views are powerful tools — when you use them properly. To create the views that match user requirements, developers need to discuss with their users what their needs are and create lots of views to cover all their data requests. Then, to prevent misuse, developers need to document the views and constantly work with users to monitor the slowest-performing and highest-priority views. Well-designed views simplify access and improve performance. In a future article, I'll describe which types of SELECT statements you can't include in a view, why you can't include them, and how else you can retrieve the required data. In the meantime, check out the Web sidebar "T-SQL Tutor Project," InstantDoc ID 27361, for a homework assignment that will help you hone your skills for creating and accessing views.
End of Article
Prev. page
1
[2]
next page -->
