Stop SQL Server doing an unneccessary clustered index insert on a view

Question

Hi

I have a large table (between 74 and 88million rows) which is the middle table (Table B) of a many to many relationship. I have a view that builds a unified picture of the data contained in the these tables. The view has a clustered index defined against it.

Table A on the left hand side of my large table is the core table in my database. Table C is the table that contains the normalised data items. When I insert a new record into table C the task takes a long time to complete (5mins on a very very good server). This is because SQL server rebuilds part of the clustered index on the view (I can see a clustered index insert in the actual execution plan). This is despite the new row in Table C not being referenced in Table B. Inserting rows into Table A and Table B takes milliseconds as I would expect. The database is not partitioned.

This is an annoymised version of the tables creation script. I've left in the columns that aren't used so you can see the full structure of the tables.

CREATE TABLE [dbo].[TableA] (
    [TableAId] [INT] IDENTITY(1,1)  NOT NULL PRIMARY KEY  CLUSTERED ,
    [TableAIdGUID] [uniqueidentifier] NOT NULL,
    [ImportantId] [INT] NOT NULL ,
    [DateCreated] [datetime] NOT NULL ,
    [OtherId2] [int] NOT NULL ,
    [TableATypeId] [int] NOT NULL ,
    [Active] [bit] NOT NULL ,
    [AuditUser] [NVARCHAR] (20) NULL , 
    [AuditTime] [datetime] NULL 
)
GO

CREATE TABLE [dbo].[TableB] (
    [TableBId] [INT]  IDENTITY(1,1)  NOT NULL PRIMARY KEY  CLUSTERED ,
    [TableBGUID]  [uniqueidentifier] NOT NULL,
    [TableAId] [INT] NOT NULL ,
    [TableCId] [INT] NOT NULL ,
    [Tag] [NVARCHAR] (50) NULL , 
    [Order] [tinyint] NOT NULL ,
    [DateCreated] [datetime] NOT NULL ,
    [Date1] [datetime] NULL ,
    [Date2] [datetime] NULL ,
    [LastUpdated] [datetime] NOT NULL ,
    [Active] [bit] NOT NULL ,
    [AuditUser] [NVARCHAR] (20) NULL , 
    [AuditTime] [datetime] NULL
)
GO

CREATE TABLE [dbo].[TableC] (
    [TableCId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY  CLUSTERED ,
    [TableCIdGUID]  [uniqueidentifier] NOT NULL,
    [TableCTypeId] [int] NOT NULL ,
    [TableCValue] [NVARCHAR] (255) NOT NULL , 
    [Frequency] [float] NOT NULL  DEFAULT 1,
    [TableCValue2] [NVARCHAR] (255) NULL ,
    [AuditUser] [NVARCHAR] (20) NULL ,
    [AuditTime] [datetime] NULL 
)
GO

CREATE VIEW [dbo].[vwTables]
WITH SCHEMABINDING ,ENCRYPTION
AS

SELECT  dbo.[TableB].TableBId, 
     dbo.[TableB].TableAId, 
     dbo.TableA.ImportantId,
     dbo.TableC.TableCValue,
     dbo.TableC.TableCValue2, 
     dbo.TableC.TableCTypeId,
     dbo.TableA.TableATypeId
FROM    dbo.[TableB] INNER JOIN
        dbo.TableC ON dbo.[TableB].TableCId = dbo.TableC.TableCId INNER JOIN
        dbo.TableA ON dbo.[TableB].TableAId = dbo.TableA.TableAId
WHERE     (dbo.[TableB].Active =  CAST(1 AS BIT)) AND (dbo.TableC.TableCValue<>'') and (dbo.TableC.TableCValue is not null)
GO

CREATE UNIQUE CLUSTERED INDEX [IX_vwTables] ON [dbo].[vwTables] 
(
    [TableCValue] ASC,
    [TableCTypeId] ASC,
    [TableBId] ASC,
    [ImportantId] ASC
)
GO

ALTER TABLE [dbo].[TableB] ADD 
    CONSTRAINT [FK_TableB_TableC] FOREIGN KEY 
    (
     [TableCId]
    ) REFERENCES [dbo].[TableC] (
     [TableCId]
    ),
    CONSTRAINT [FK_TableB_TableA] FOREIGN KEY 
    (
     [TableAId]
    ) REFERENCES [dbo].[TableA] (
     [TableAId]
    )
GO


CREATE NONCLUSTERED INDEX [IX_TableA_Nonclustered] ON [dbo].[TableA] 
(
    [ImportantId] ASC,
    [TableAId] ASC,
    [TableATypeId] ASC,
    [Active] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_TableA_OtherId2] ON [dbo].[TableA] 
(
    [AuditTime] ASC,
    [OtherId2] ASC
)
GO


CREATE NONCLUSTERED INDEX [IX_TableB_NonClustered] ON [dbo].[TableB] 
(
    [TableAId] ASC,
    [TableBId] ASC,
    [Active] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_EntityAttributes_NonClustered_2] ON [dbo].[TableB] 
(
    [TableBId] ASC,
    [TableAId] ASC,
    [TableCId] ASC,
    [Tag] ASC,
    [Active] ASC
) 
GO

A sample query ran against this is as follows.

SELECT Query.ImportantId
FROM(

     SELECT  b.ImportantId
     FROM [vwTables] a WITH (NOLOCK)
     INNER JOIN [vwTables] b WITH (NOLOCK, NOEXPAND) ON a.TableCValue = b.TableCValue
     WHERE a.ImportantId = @ImportantId AND
     a.TableATypeId=3 AND b.TableATypeId=3   

) As Query
GROUP BY Query.ImportantId

Does anyone know how I can get the inserts into table C to happen (almost) instantaneously like the inserts into TableA and TableB?

Thanks in advanced.

Answer 1

Just use DB Tuning Advisor

Please Show your table structure

Answer 2

If I understand you correctly, you have a clustered index on a view. When SQL Server sees that the tables beneath the view change, it has to reindex the view. This is only appropriate when the tables change rarely (think Business Intelligence databases.)

Consider removing the index from the view, and support the view with indexes on the underlying tables.

For example, see this quote from MSDN:

Q. I update my data warehouse once a week. Indexed views speed up my queries a lot during the week, but slow down the weekly update? What should I do?

A. Consider dropping the indexed views before the weekly update, and creating them again afterwards.

EDIT: Your example query appears to search for rows in A that share a TableCValue. I'd think it would benefit from these indexes:

• Table A: (TableATypeId,TableAId)
• Table B: (TableAId,TableCId)
• Table B: (TableCId,TableAId)
• Table C: (TableCValue,TableCId)

With these indexes you'd hope the query is fast enough that it doesn't require an indexed view.

P.S. Be sure to compare my suggestion with the index suggestions from SQL Server Profiler in case I missed something.