Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Apple iPad


SQLServer Quiz 2011 - Advantage and disadvantage of using Indexed View in SQL Server

  • Using Indexed view in SQL Server comes with a cost. But there are places which it perfectly fits in and at the same time there are places where it create problem than solution. So, what all are the pros and cons of using Indexed view

    Posted on 03-29-2011 00:00 |
    madhunair
    321 · 0% · 135

8  Answers  

Subscribe to Notifications
  • Score
    8

    This answer is based on the follow SQL Server technical article: Improving Performance with SQL Server 2008 Indexed Views

    From the database management system (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for executing the query. The query optimizer searches though a set of possible execution plans for a query, and it chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

    In the case of a nonindexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view [Note1] . After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

    The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

    Pros

    Indexed views can increase query performance in the following ways:

    • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
    • Tables can be prejoined and the resulting data set stored.
    • Combinations of joins or aggregations can be stored.

    Secondary, nonclustered indexes on views can provide additional query performance. Similar to nonclustered indexes on tables, nonclustered indexes on views may provide more options for the query optimizer to choose from during the compilation process.

    Applications that benefit from the implementation of indexed views include:

    • Decision support workloads.
    • Data marts.
    • Data warehouses.
    • Online analytical processing (OLAP) stores and sources.
    • Data mining workloads.

    From the query type and pattern point of view, the benefiting applications can be characterized as those containing:

    • Joins and aggregations of large tables.
    • Repeated patterns of queries.
    • Repeated aggregations on the same or overlapping sets of columns.
    • Repeated joins of the same tables on the same keys.
    • Combinations of the above.

    Cons:

    Adding indexes to the schema increases the overhead on the database because the indexes will require ongoing maintenance.

    Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. In this case, not only are performance gains not realized, but the additional cost of disk space, maintenance, and optimization is incurred. Online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables.

    Indexes on tables and indexed views should be designed concurrently to obtain the best results from each construct. Because both indexes and indexed views may be useful for a given query, designing them separately can lead to redundant recommendations that incur high storage and maintenance overhead. While you tune the physical design of a database, tradeoffs must be made between the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design for indexed views is a challenging task, and the Database Tuning Advisor should be used wherever it is possible.

    Query optimization cost can increase substantially if there are many indexed views that the query optimizer may consider for a particular query. A query optimizer may consider all indexed views that are defined on any subset of tables in the query. Each view has to be investigated for the potential substitution before it is rejected. This may take some time, especially if there are hundreds of such views for a given query.

    Replied on Mar 29 2011 12:27AM  . 
    Igor Zakharov
    162 · 1% · 300
  • Score
    7

    There's not much to add to Igors post...

    But there's one additional scenario I'd like to cover: the pros and cons of an indexed view over a filtered index, since a filtered index can be an alternative.

    Both have the dependency on a single table in common. Hence, the advantage of prejoined tables does not apply to filtered indexes.

    The major advantage of an indexed view over a filtered index is the ability to return computed columns and/or include complex comparison logic (whereas such a simple thing as a LIKE operator is considered "complex"...). This allows to significantly reduce the number of rows and maybe even the number of columns leading to a much more narrow leaf page structure leading to improved performance.

    There are disadvantages, too. Indexed views

    • have increased index maintenance costs. For example, the query processor uses more CPU resources to update an indexed view than a filtered index.
    • are not available for online index rebuilds. But it's possible to rebuild filtered indexes while they are available for queries.
    • must be unique, whereas filtered indexes can be non-unique.

    If possible, I would use a filtered index instead of an indexed view due to the reasons given above.

    This answer is based on the Filtered Index Design Guidelines article.

    Replied on Mar 29 2011 4:25PM  . 
    lmu92
    0 · 0% · 0
  • Score
    6

    There are many restrictions on indexed views and they may not be as useful in certain editions of SQL Server, indexed views can provide a very significant improvement in performance. Getting around the restrictions may not be as difficult as it originally appears due to the optimizer's ability to make use of the index even if the query is not directly on the view being queried.

    Indexed Views are a great way to realize performance gains under the right circumstances. Be aware of the costs of creating indexed views in highly transactional environments. If your environment happens to be one of more querying than updating, however, indexed views might be just what the optimizer ordered.

    While the improvement in query performance can be substantial, the costs can be large enough to offset any benefits. Maintaining the index on a view has performance costs: it adds overhead to every insert, update or delete on a base table that participates in the view. This limits the applicability of indexed views to situations where data modifications are limited; preferably, where the data modification occurs in batches. Also, don't forget about the extra space consumed by the index. Applications that involve large proportions of decision support and analysis are the best candidates for using indexed views.

    Replied on Mar 29 2011 11:10PM  . 
    indika saminda kannangara
    188 · 1% · 251
  • Score
    4

    I am keeping it simple, In Indexed Views your result set will be persist which will increase efficiency (data fetching speed). Obviously as it is indexing (result) on view it will take some more space on disk and maintenance. Thanks Manoj

    Replied on Apr 7 2011 3:57AM  . 
    Manoj Bhadiyadra
    146 · 1% · 335
  • Score
    6

    An indexed view is a view that has been made physical (materialized) by adding an unique clustered index on it and it is stored in a database similar to a table.

    Pros: 1. Whenever the data in the underlying tables is modified, the indexed view is also modified, so it is always current. 2. As the the data is physical, much work like pre-joined, pre-calculated, pre-aggregated etc has already been done which eventually speed up some queries. 3.If required, an indexed view can also have non-clustered indexes. 4. Even if an indexed view is not explicitly named in a query, it will be considered by the query optimizer, just like a covering index (EE only).

    Cons: 1. It will occupy space. 2. Some overhead is required to maintain an indexed view as indexed views are updated when the data in their underlying tables are changed. 3. Sometime it can contribute blocking issues if the multiple base tables used in an indexed view are being updated at the same time.

    Replied on Apr 12 2011 6:28AM  . 
    Subhrajit
    680 · 0% · 49
  • Score
    7

    Here are a couple of caveats to the great answers supplied above.

    Indexed views also do not allow OUTER joins. If you need an OUTER JOIN, the design becomes more complex... sometimes more complex than it is worth.

    Indexed views will only automatically be considered in execution plans with SQL Server Enterprise Edition. When used in editions other than Enterprise Edition, you must use the WITH (NOEXPAND) hint.

    Try this:

    CREATE TABLE dbo.employees
        (
        	id int NOT NULL, 
        	name varchar(255) NOT NULL, 
        	CONSTRAINT PK_employees PRIMARY KEY (id), 
        	CONSTRAINT UQ_employees UNIQUE (name)
        )
    
    CREATE TABLE dbo.roles
        (
        	id int NOT NULL, 
        	name varchar(255) NOT NULL, 
        	CONSTRAINT PK_roles PRIMARY KEY (id), 
        	CONSTRAINT UQ_roles UNIQUE (name)
        )
    
    CREATE TABLE dbo.lnk_employee_roles
        (
        	employee_id int NOT NULL, 
        	role_id int NOT NULL, 
        	CONSTRAINT PK_lnk_employee_roles PRIMARY KEY (employee_id, role_id), 
        	CONSTRAINT FK_lnk_employee_roles__employees FOREIGN KEY (employee_id) REFERENCES dbo.employees (id), 
        	CONSTRAINT FK_lnk_employee_roles__roles FOREIGN KEY (role_id) REFERENCES dbo.roles (id)
        )
    
    INSERT INTO dbo.employees (id, name)
    VALUES 
        (1, 'Employee 1'), 
        (2, 'Employee 2'), 
        (3, 'Employee 3')
    
    INSERT INTO dbo.roles (id, name)
    VALUES 
        (1, 'Role 1'), 
        (2, 'Role 2'), 
        (3, 'Role 3')
    
    INSERT INTO lnk_employee_roles (employee_id, role_id)
    VALUES 
        (1, 1), 
        (1, 2), 
        (1, 3), 
        (3, 2), 
        (3, 3)
    GO
    
    CREATE VIEW dbo.EmployeeRoles
    WITH SCHEMABINDING
    AS
    SELECT
        e.id as employee_id, 
        e.name as employee_name, 
        r.id as role_id, 
        r.name as role_name
    FROM
        dbo.employees e
        	INNER JOIN dbo.lnk_employee_roles er ON e.id = er.employee_id
        	INNER JOIN dbo.roles r ON r.id = er.role_id
    GO
    
    CREATE UNIQUE CLUSTERED INDEX idx_EmployeeRoles
    ON dbo.EmployeeRoles (employee_id, role_id)
    GO
    

    Now check out the execution plan for the following query:

    SELECT employee_name, role_name FROM dbo.EmployeeRoles
    

    You'll see an index scan on employees.UQemployees and lnkemployeeroles.PKlnkemployeeroles, plus a Clustered Index See on roles.PK_roles.

    Compare this with the execution plan for the following query

    SELECT employee_name, role_name FROM dbo.EmployeeRoles WITH (NOEXPAND)
    

    You'll see only a clustered index scan on EmployeeRoles.idx_EmployeeRoles.

    The automatic consideration of Indexed Views when calculating query plans only happens on Enterprise Edition.

    Replied on Apr 18 2011 7:34AM  . 
    Marc Jellinek
    95 · 2% · 586
  • Score
    7

    One last thing:

    I've found it to be very useful to store indexes in their own filegroup, especially Indexed Views.

    What this means is that the base data (data in base tables) lives on one filegroup and the data for the indexes lives on another filegroup. If these filegroups live on separate storage (ideally separate hard drives with seperate controllers), writes to the base table do not contend with writes to the indexes... they can happen with a lower degree of contention.

    CREATE TABLE dbo.employees
        (
        	id int NOT NULL, 
        	name varchar(255) NOT NULL, 
        	CONSTRAINT PK_employees PRIMARY KEY (id), 
        	CONSTRAINT UQ_employees UNIQUE (name)
        ) ON PRIMARY
    
    CREATE TABLE dbo.roles
        (
        	id int NOT NULL, 
        	name varchar(255) NOT NULL, 
        	CONSTRAINT PK_roles PRIMARY KEY (id), 
        	CONSTRAINT UQ_roles UNIQUE (name)
        ) ON PRIMARY
    
    CREATE TABLE dbo.lnk_employee_roles
        (
        	employee_id int NOT NULL, 
        	role_id int NOT NULL, 
        	CONSTRAINT PK_lnk_employee_roles PRIMARY KEY (employee_id, role_id), 
        	CONSTRAINT FK_lnk_employee_roles__employees FOREIGN KEY (employee_id) REFERENCES dbo.employees (id), 
        	CONSTRAINT FK_lnk_employee_roles__roles FOREIGN KEY (role_id) REFERENCES dbo.roles (id)
        ) ON PRIMARY
    
    INSERT INTO dbo.employees (id, name)
    VALUES 
        (1, 'Employee 1'), 
        (2, 'Employee 2'), 
        (3, 'Employee 3')
    
    INSERT INTO dbo.roles (id, name)
    VALUES 
        (1, 'Role 1'), 
        (2, 'Role 2'), 
        (3, 'Role 3')
    
    INSERT INTO lnk_employee_roles (employee_id, role_id)
    VALUES 
        (1, 1), 
        (1, 2), 
        (1, 3), 
        (3, 2), 
        (3, 3)
    GO
    
    CREATE VIEW dbo.EmployeeRoles
    WITH SCHEMABINDING
    AS
    SELECT
        e.id as employee_id, 
        e.name as employee_name, 
        r.id as role_id, 
        r.name as role_name
    FROM
        dbo.employees e
        	INNER JOIN dbo.lnk_employee_roles er ON e.id = er.employee_id
        	INNER JOIN dbo.roles r ON r.id = er.role_id
    GO
    
    CREATE UNIQUE CLUSTERED INDEX idx_EmployeeRoles
    ON dbo.EmployeeRoles (employee_id, role_id)
    ON INDEXES
    GO
    
    Replied on Apr 18 2011 7:40AM  . 
    Marc Jellinek
    95 · 2% · 586
  • Score
    8

    SQLServer Quiz 2011 - Advantage and disadvantage of using Indexed View in SQL Server

    Solution:

    Indexed View Background:

    Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced just like tables in SQL statements. The result set of a standard view is not stored permanently in the database. Every time a query references a standard view, SQL Server substitutes the definition of the view into the query internally until a modified query is formed that only references base tables

    From DBMS perspective, a view is a description of the data, a form of metadata. It is possible to create a unique clustered index on a view, as well as non-clustered indexes, to improve data access performance on the most complex queries. In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view. The main benefit of using index view is that the query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

    In the case of a non-indexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view. After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

    The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or the entire query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

    Advantages of Indexed View:

    Indexed views improve the performance in the following ways:

    • Joins and aggregations that process many rows.
    • Join and aggregation operations that are frequently performed by many queries.
    • Decision support workloads.
    • The real benefits of Indexed Views are when we have aggregates that are too expensive to compute in real time.
    • Aggregations can be pre-computed and stored in the index to minimize expensive computations during query execution.
    • Tables can be pre-joined and the resulting data set stored.
    • Combinations of joins or aggregations can be stored.
    • Indexed views may be substituted by the query optimizer if it is determined that the cost of the query will be less than using the base table.

    For a standard view, the overhead of dynamically building the result set for each query that references a view can be significant for views that involve complex processing of large numbers of rows, such as aggregating lots of data, or joining many rows. If such views are frequently referenced in queries, we can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.

    Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.

    As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification. The capability of the query optimizer to take advantage of indexed views when it processes queries has improved over earlier versions when both the query and the view definition contain the following matching elements:

    Disadvantages of Indexed View:

    To use an indexed view in all other editions, the NOEXPAND table hint must be used. Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.

    Another cost of indexed views is that the data is actually stored. By applying the clustered index we are creating a copy of the data. So if we have several indexed views on a single table, we will have several copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes with it.

    Following are the disadvantages using index views that do not improve the performance:

    • OLTP systems that have many writes to the disk.
    • Databases that have many updates.
    • Queries that do not involve aggregations or joins.
    • High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
    • The overuse of indexed views can be like winding a watch with a wrench.
    • Online index rebuilds are not supported for indexed views.

    I agree with Lutz that Filtered index is better and top of his comments on filtered index versus indexed view; we can create our filtered index as a non-unique index and where as indexed views can only be created as a unique index. One point to keep in mind is we cannot create a filtered index on a view. However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. With filtered index, online index rebuilds is possible. We can rebuild filtered indexes while they are available for queries. Online index rebuilds are not supported for indexed views.

    Reference: http://technet.microsoft.com/en-us/library/cc917715.aspx

    Abi Chapagai

    Replied on Apr 26 2011 11:46AM  . 
    Abi Chapagai
    70 · 3% · 808

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.