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.
No comments:
Post a Comment