Indexed view in Sql server performance

Speeding up queries by indexed views. Query tuning and optimization best practices.  

As we know SQL view are named SELECT statement i.e.  It a saved a SELECT statement and data are not stored physically in the disk like table. So when execute a view internally name of view is replaced or expend by the SELECT statement which is inside the way.  So in general view doesn’t affect the query performance significantly. For example:

Let’s assume there is database view name vewGetUserComment whose definition is follow:

CREATE VIEW vewGetUserContact

AS

SELECT ntUserID

     ,COUNT(*) ntTotalContact

FROM tblUserContact

GROUP BY ntUserID

If we execute below script:

SELECT *

FROM vewGetUserContact

This query is internally converted into:

Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))

Stream Aggregate(GROUP BY:([Project].[dbo].[tblUserContact].[ntUserID]) DEFINE:([Expr1006]=Count(*)))

Clustered Index Scan(OBJECT:([Project].[dbo].[tblUserContact].[PK_tblUserContact]), ORDERED FORWARD)

So here actual view vewGetUserContact is not in the picture.

SQL server has introduced a new type of view called indexed view which a materialized that is data of the view is stored physically in the disk just like a table. For examples:

Creating an indexed view:

CREATE VIEW vewGetUserContact_New

     WITH SCHEMABINDING

AS

SELECT ntUserID

     ,COUNT_BIG(*) ntTotalContact

FROM dbo.tblUserContact

GROUP BY ntUserID

CREATE UNIQUE CLUSTERED INDEX NCI_ntUserID

ON vewGetUserContact_New (ntUserID)

If we execute below script:

SELECT * FROM vewGetUserContact

This query is internally converted into:

Clustered Index Scan(OBJECT:([Project].[dbo].[vewGetUserContact_New].[NCI_ntUserID]))

In this case query view is not replaced by the SELECT statement and SQL server directly fetching the records from the indexed view. Let’s compare the execution cost of normal view and indexed view:

--Using normal view

SELECT *

FROM vewGetUserContact

WHERE ntUserID > 20

Total  logical read: 3117

--Using Indexed view

SELECT *

FROM vewGetUserContact_New WITH (NOEXPAND)

WHERE ntUserID > 20

Total  logical read: 157

Execution plan:

Indexed view Sql server

So, it is obvious indexed view perform much better than a normal view. Let’s us understand indexed view in bit closer way. Indexed view saved the output of SELECT statement. So when indexed view is executed it directly retrieved the saved data instead of executing the SELECT query inside the way.  So, what a powerful feature!! If  there is very complex and time consuming query then by using indexed view we can saved the output of the complex query physically in the disk and next time onwards we will directly retrieved  the data from saved data and adding some filter conditions etc. in very fast way.  The real world is not as simple as look like. Actual problem is synchronizing the data of indexed view with actual table is very costly.   That is when data in the base tables are deleted, updated or deleted and indexed view need to be updated accordingly. This is costly operation. Hence, indexed view should be created when:

 a. A query is executing very frequently, which needs optimization and there is not any other way to optimize it.

  

  b. A query references only almost statistics tables that is those table where very less numbers of INSERT, DELETE, UPDATE or MERGE operation is performed.

Note: There are already many limitations in creating an indexed view.