Peformance problems with updates affecting indexed views

Question

Post reply

Peformance problems with updates affecting indexed views

Tim Januario

SSChasing Mays

Points: 609
More actions
December 5, 2005 at 10:25 am

#90043

Hello,
I recently installed an indexed view onto our production sql2k machine in order to speed up some of our harder-working queries. What I have noticed though, is that the updates to the base-tables are now taking a significant amount of time (about 1.5 seconds). When I look at the query plan, I notice that the update to the views index does a seek at one point but a full-table scan of the index at another point (~500,000 records) even though the clustered index on the view has the same columns as the base table's pkey. I loaded a copy of the db on our dev sql2k5 server and noticed very different, and much improved results, which I guess I would expect after 5 years development.
My question is: Is this expected behavior? Everything I have found in my vast googling regarding this has said, basically, indexed views good, possible performance hit in base table updates. However, a full-index scan for every update does not qualify as a "possible" performance hit. This behavior is absolutely unacceptable if speed is even a slight concern because obviously an indexed view wouldn't scale. Am I missing something? We have sp3 on the server, but I have verified that sp4 does not improve the problem.
Here is the view:
CREATE VIEW dbo.V0106_USER_COMPANY_INFO
WITH SCHEMABINDING
AS
SELECT
T0106.T0501_COMMUNITY_ID,
T0106.T0101_USER_ID,
T0106.T0516_COMM_JOB_FUNCTION_ID,
COUNT_BIG(*) TOTAL
FROM
dbo.T0106_USER_COMMUNITY T0106 INNER JOIN
dbo.T0103_USER_PRODUCT_INTEREST T0103 ON
(
T0103.T0501_COMMUNITY_ID = T0106.T0501_COMMUNITY_ID AND
T0103.T0101_USER_ID = T0106.T0101_USER_ID
)
GROUP BY
T0106.T0501_COMMUNITY_ID,
T0106.T0101_USER_ID,
T0106.T0516_COMM_JOB_FUNCTION_ID
And the index:
CREATE UNIQUE CLUSTERED INDEX [PK_V0106_USER_COMPANY_INFO] ON [dbo].[V0106_USER_COMPANY_INFO]([T0501_COMMUNITY_ID], [T0101_USER_ID]) ON [PRIMARY]
GO
And the plan (if it helps. I prefer the graphic, but...). The part that troubles me is the hash match right outer join with V0106 which show the full scan in the graphic version:
|--Sequence
|--Index Update(OBJECT[BDMETRICSDOTNET].[dbo].[T0106_USER_COMMUNITY].[IX_T0106_COMM_JOB_FUNCTION_ID]), SET[T0101_USER_ID1019]=[T0106_USER_COMMUNITY].[T0101_USER_ID], [T0516_COMM_JOB_FUNCTION_ID1018]=[T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTIO
| |--Table Spool
| |--Split
| |--Clustered Index Update(OBJECT[BDMETRICSDOTNET].[dbo].[T0106_USER_COMMUNITY].[PK_T0106_USER_COMMUNITY]), SET[T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTION_ID]=[T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTION_ID]), WHERE[T0106_
|--Clustered Index Update(OBJECT[BDMETRICSDOTNET].[dbo].[V0106_USER_COMPANY_INFO].[PK_V0106_USER_COMPANY_INFO]), SET[V0106_USER_COMPANY_INFO].[TOTAL]=[V0106_USER_COMPANY_INFO].[TOTAL], [V0106_USER_COMPANY_INFO].[T0516_COMM_JOB_FUNCTION_ID]=[V010
| |--Collapse(GROUP BY[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID]))
| |--Sort(ORDER BY[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID] ASC, [T0106_USER_COMMUNITY].[T0101_USER_ID] ASC, [Act1020] ASC))
| |--Compute Scalar(DEFINE[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID]=[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID]=[T0106_USER_COMMUNITY].[T0101_USER_ID], [T0106_USER_COMMUNITY].[T0516_COMM_JOB_F
| |--Table Spool
| |--Compute Scalar(DEFINE[Bmk1011]=[Bmk1011]))
| |--Hash Match(Right Outer Join, HASH[V0106_USER_COMPANY_INFO].[T0501_COMMUNITY_ID], [V0106_USER_COMPANY_INFO].[T0101_USER_ID])=([T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID]), RES
| |--Clustered Index Scan(OBJECT[BDMETRICSDOTNET].[dbo].[V0106_USER_COMPANY_INFO].[PK_V0106_USER_COMPANY_INFO]))
| |--Compute Scalar(DEFINE[Expr1014]=If ([V0106_USER_COMPANY_INFO].[TOTAL] IS NULL) then [Expr1014] else ([V0106_USER_COMPANY_INFO].[TOTAL]+[Expr1014]), [Act1020]=If ([V0106_USER_COMPANY_INFO].[TOTAL] IS NULL) then
| |--Nested Loops(Left Outer Join, OUTER REFERENCES[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID], [T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTION_ID]) WITH PREFETCH)
| |--Stream Aggregate(GROUP BY[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID], [T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTION_ID]) DEFINE[Expr1014]=SUM(If ([Act1015]
| | |--Nested Loops(Inner Join, OUTER REFERENCES[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID]))
| | |--Sort(ORDER BY[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID] ASC, [T0106_USER_COMMUNITY].[T0101_USER_ID] ASC, [T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTION_ID] ASC))
| | | |--Compute Scalar(DEFINE[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID]=[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T0106_USER_COMMUNITY].[T0101_USER_ID]=[T0106_USER_COMMUNITY].[T0101_USER_
| | | |--Table Spool
| | |--Clustered Index Seek(OBJECT[BDMETRICSDOTNET].[dbo].[T0103_USER_PRODUCT_INTEREST].[PK_T0103_USER_PRODUCT_INTEREST] AS [T0103]), SEEK[T0103].[T0501_COMMUNITY_ID]=[T0106_USER_COMMUNITY].[T05
| |--Clustered Index Seek(OBJECT[BDMETRICSDOTNET].[dbo].[V0106_USER_COMPANY_INFO].[PK_V0106_USER_COMPANY_INFO]), SEEK[V0106_USER_COMPANY_INFO].[T0501_COMMUNITY_ID]=[T0106_USER_COMMUNITY].[T0501_COMMUNIT
|--Assert(WHEREIf (NOT([Pass1023]) AND ([Expr1022] IS NULL)) then 0 else NULL))
|--Nested Loops(Left Semi Join, WHERE[Act1015]<>4 OR (([T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID] IS NULL) OR ([T0106_USER_COMMUNITY].[T0516_COMM_JOB_FUNCTION_ID] IS NULL)))OUTER REFERENCES[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID], [T010
|--Table Spool
|--Row Count Spool
|--Clustered Index Seek(OBJECT[BDMETRICSDOTNET].[dbo].[T0516_COMMUNITY_JOB_FUNCTION].[PK_T0516_COMMUNITY_JOB_FUNCTION]), SEEK[T0516_COMMUNITY_JOB_FUNCTION].[T0501_COMMUNITY_ID]=[T0106_USER_COMMUNITY].[T0501_COMMUNITY_ID] AND [T051
(sorry, couldn't get the rest. but I think that should cover it)
Thanks,
Tim Januario

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32342 More actions · Answer 1

In sql 2000 BOL:

Indexed views are meant for static tables that have very infrequent changes.

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views that are mapped over relatively static data, process many rows, and are referenced by many queries.

Are the statistics updated?

Ian Yates SSCoach Points: 19738 More actions · Answer 2

You are using a group by to do a record count - perhaps it is joining back to itself to do the grouping? Could you add an index on these columns that might assist it?