SQL Server Performance Forum – Threads Archive

Strange behaviour of Indexed View

Hi All, We are using indexed views in our application for performance benefit and better utilisation of resources on MS SQL Server 2000 Enterprise Edition… We are facing a strange problem… Before explaining the problem let me explain how we are making use of indexed views in our application Illustration: Indexed View
CREATE VIEW dbo.vwIndexed
WITH SCHEMABINDING
AS
SELECT
Table_A.Pk_identitycolumn,
Table_A.ColumnA,
Table_A.ColumnB,
Table_B.ColumnA,
Table_B.ColumnB,
Table_B.ColumnC,
Table_B.ColumnD,
Table_A.ColumnC,
Table_A.ColumnD,
Table_A.ColumnE,
Table_A.ColumnF,
Table_A.ColumnG,
Table_C.ColumnA,
Table_C.ColumnB,
Table_A.ColumnH,
Table_A.ColumnI,
Table_A.ColumnJ,
Table_A.ColumnK,
Table_A.ColumnL,
Table_A.ColumnM,
Table_D.ColumnA,
Table_D.ColumnB,
Table_D.ColumnC,
Table_D.ColumnD,
Table_D.ColumnE,
Table_D.ColumnF,
Table_D.ColumnG,
Table_D.ColumnH,
Table_A.ColumnN,
FROM dbo.Table_A
INNER JOINdbo.Table_B ONTable_A.ForeginKey = Table_B.PrimaryKey
INNER JOINdbo.Table_D ONTable_A.ForeginKey = Table_D.PrimaryKey
INNER JOINdbo.Table_C ONTable_A.ForeginKey = Table_C.PrimaryKey
WHERE ((Table_A.ColumnM = ‘A’)
OR (Table_A.ColumnM = ‘B’))
OR((Table_A.ColumnM = ‘C’)
OR(Table_A.ColumnM = ‘D’))
AND Table_A.ColumnN = 1 We have a clustered indexe on the identity column and a non-clustered composite index consisting of Table_B.ColumnA, Table_A.ColumnA, Table_D.ColumnA, Table_D.ColumnB, Table_D.ColumnC, Table_D.ColumnD, Table_A.ColumnC, TableB_ColumnC We have created a view on top of this so that we can have a aggregate column, which is as follows CREATE VIEW dbo.view
AS
SELECT dbo.vwIndexed.*
TableE.ColumnAAS Total,
TableE.ColumnBAS Total1
INNER JOIN TableE ON dbo.vwIndexed.ColumnA = Table_E.PrimaryKey Then we run one of the following queries from a stored procedure which are dynamically generated based on the inputs parameter to the stored procedure Select Distinct Table_D.ColumnA from dbo.view where view.[Table_A.ColumnA] = ‘1234’ AND ((view.[Table_A.ColumnM] = ‘A’) OR (view.[Table_A.ColumnM] = ‘B’)) AND view.[Table_A.ColumnJ] > 0 Select Distinct Department_Id from dbo.view where view.[Table_D.ColumnA] = ’02’ AND view.[Table_A.ColumnA] = ‘1234’ AND ((view.[Table_A.ColumnM] = ‘A’) OR (view.[Table_A.ColumnM] = ‘B’)) AND view.[Table_A.ColumnJ] > 0 Select Distinct Stroke_id from dbo.view where view.[Table_D.ColumnB] = ‘A02’ AND view.[Table_A.ColumnA] = ‘1234’ AND ((view.[Table_A.ColumnM] = ‘A’) OR (view.[Table_A.ColumnM] = ‘B’)) AND view.[Table_A.ColumnJ] > 0 All the objects mentioned above are created using
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO We have around 1.25 million rows in the underlying table as well as the Indexed view that we created… The first two selects in the above query uses indexed view to fetch the data while the third query goes to the base table to retireve the data… But if the underlying table has 1.25 million rows and the indexed view we created holds 25% of the data held by underlying table, then all the three queries uses indexed view… Though we have proper indexes created it still uses Clustered index scan in all the cases… We found this through the execution plan… We are not able to understand this behaviour Are we doing something wrong? Or is there a known issue with indexed view… Response immediately is greatly appreciated as the performance of our applications is greatly dependent upon this indexed view…
Cheers
Suresh Shenoy

Can you post the indexes used on the view and the base tables please? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

For Indexed View we have a clustered index on Table_A.Pk_identitycolumn and a non-clustered composite index consisting of Table_B.ColumnA, Table_A.ColumnA, Table_D.ColumnA, Table_D.ColumnB, Table_D.ColumnC, Table_D.ColumnD, Table_A.ColumnC, TableB_ColumnC For base table we have clustered index as well as non-clustered index (wherever applicable) Cheers
Suresh Shenoy

How about SET options to obtain Consistent Results and using Deterministic Functions.
Are you using any Views in the queries, if so how about SCHEMABINDING option setup?
The SQL Server query optimizer automatically determines when an indexed view can be used for a given query execution. The view does not need to be referenced directly in the query for the optimizer to use it in the query execution plan. Therefore, existing applications may take advantage of the indexed views without any changes to the application itself. Only the indexed views have to be created. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>