Database Reference
In-Depth Information
Now you can select data directly from the view, as shown in Listing 9-18.
Listing 19-18. Indexed views: Selecting data from the indexed view
select top 10 ProductId, ProductName, TotalQuantity
from dbo.vProductSaleStats
order by TotalQuantity desc
The execution plan shown in Figure 9-7 is much more efficient.
Figure 9-7. Execution plan of the query that selects the top-10 most popular products utilizing an indexed view
As always, “there is no such thing as a free lunch.” Now SQL Server needs to maintain the view. Each time you
insert or delete the OrderLineItem row or, perhaps, modify the quantity or product there, SQL Server needs to update
the data in the indexed view in addition to the main table.
Let's look at the execution plan of the insert operation, as shown in Figure 9-8 .
Figure 9-8. Execution plan of the query that inserts data into OrderLineItems table
The part of the plan in the highlighted area is responsible for indexed view maintenance. This portion of the
plan could introduce a lot of overhead when data in the table is highly volatile, which leads us to a very important
conclusion. That is, indexed views work the best when the benefits we get while selecting the data exceed the
overhead of maintaining the view during data modifications. Simply said, indexed views are most beneficial when
underlying data is relatively static. Think about Data Warehouse systems where a typical workload requires a lot of
joins and aggregations, and the data is updating infrequently, perhaps based on some schedule, as an example.
always test the performance of the batch data update when there is an indexed view referencing a table.
in some cases, it would be faster to drop and recreate the view, rather than keep it during such operations.
Tip
 
 
Search WWH ::




Custom Search