Pages

Dec 28, 2014

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:

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.