Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry on the clustered index when associated with a large amount of records (~2.5 million in this case), the indexed view which in turn creates a massive entry in the transaction log. I'm wondering what I could try to avoid this massive entry into the transaction log, without disabling the index on the view? I know the field that is being updated is not a part of any index, it is just part of this view. My google-fu is failing me at present, so wanted to get this up while I continue searching to see if anything pops up here.

CREATE TABLE Big ( Id INT IDENTITY, SmallId VARCHAR(3) NOT NULL ); -- 11 million rows, 2.5 million where SmallId = 'xxx'
CREATE TABLE Small ( Id VARCHAR(3) NOT NULL, [Description] VARCHAR(128) NOT NULL ); -- 12k rows
CREATE VIEW [View] WITH SCHEMABINDING AS
    SELECT Id, b.SmallId, s.Description AS SmallDescription
    FROM Big b
    INNER JOIN Small s
        ON b.SmallId = s.Id;
CREATE CLUSTERED INDEX [PK_View] ON [View]([Id]);
UPDATE Small SET [Description] = 'x' WHERE Id = 'xxx'; --- massive amount of transaction log entries for PK_View
share|improve this question
1  
The indexed view persists a copy of the data. If you're going to update 2.5 million rows of data, you have to pay that cost at some point - either during the update, or by dropping the index and re-creating it. –  Aaron Bertrand Apr 15 '13 at 14:45
add comment

1 Answer

up vote 2 down vote accepted

One idea might be to create a second view that doesn't persist 2.5 million copies of the same value.

CREATE VIEW dbo.innerview
WITH SCHEMABINDING
AS
  SELECT b.Id, SmallId = s.Id
   FROM dbo.Big AS b
   INNER JOIN dbo.Small AS s
   ON b.SmallId = s.Id;
GO
CREATE UNIQUE CLUSTERED INDEX PK_View On innerview([Id]);
GO

Then you can create a view around this, without an index:

ALTER VIEW dbo.View
WITH SCHEMABINDING
AS
  SELECT v.Id, v.SmallId, s.description AS SmallDescription
    FROM dbo.innerview AS v
    INNER JOIN dbo.Small AS s
    ON v.SmallID = s.Id;
GO

Now you can change the small description and it won't affect all those rows, and the queries against dbo.View should exhibit about the same performance as your current queries against the indexed version. Your indexed view shouldn't contain many, many copies of a value that is likely to change.

Of course this view doesn't need to be indexed at all IMHO. A lot of people think indexing a view magically speeds up the query; in a lot of cases it actually just slows down your overall workload. What is the indexed view gaining you that the clustered index on the source table (Big) doesn't already do? Transaction log madness aside, have you compared queries against the indexed view with a simple query like:

SELECT 
  Id, -- please be specific, both tables have a column named Id
  b.SmallId, s.Description AS SmallDescription
FROM dbo.Big AS b
INNER JOIN dbo.Small AS s
    ON b.SmallId = s.Id;

?

share|improve this answer
    
This should actually work, since the small table does not participate in any of sorting/paging. Thanks! –  Dave Apr 15 '13 at 15:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.