Creating Clustered Index on View of table containing XML data types Takes Forever and causes Timeouts

Last Post 21 Apr 2007 02:42 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sachavieuxroy
New Member
New Member

--
20 Apr 2007 07:45 PM
Creating Clustered Index on View with table containing XML data types Takes Forever and causes Timeouts

I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?



The table definition is displayed below.



CREATE TABLE [dbo].[AuditLogDetails](

[ID] [int] IDENTITY(1,1) NOT NULL,

[RecordID] [int] NOT NULL,

[TableName] [varchar](64) NOT NULL,

[Modifications] [xml] NOT NULL,

CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]



The view definition is displayed below.



ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING

AS

SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications

FROM dbo.AuditLogParent P

INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1



The definition for UDF f_GetModification



ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )

returns xml

with schemabinding

as

begin

declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')

end





The definition for UDF f_GetIfModificationExist



ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )

returns Bit

with schemabinding

as

begin

declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')

end



The Statement to create the index is below.



CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]

(

[ID] ASC,

[RecordID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
SQLUSA
New Member
New Member

--
21 Apr 2007 02:42 AM
How many rows are there in the table? View?

What is your platform?
sachavieuxroy
New Member
New Member

--
22 Apr 2007 07:22 AM
There are 87781 rows in table AuditLogDetails.

There are 1867 rows in table AudiLogParent.

The platform is Windows XP Professional Service Pack 2.
obahat
New Member
New Member

--
25 Apr 2007 08:12 AM
Not sure where to begin. There are numerous "best practices" and performance issues with the design of the DDL here. I'm not judging or criticizing, but you should really try and understand how the XML columns and your DDL work under the hood and realize the performance implications.

I'm not surprised that SQL server choked on the creation of the clustered index, however more importantly- do you understand that your new clustered index will hardly have any effect on the performance of the view (according to the SQL statement that you provided for the creation of the clustered index)? The view consists of a JOIN between two tables and the ID (identity col) is already a clustered index (CI) on one of the underlying tables. In your statement you include the RecordID to be part of the CI which may not improve performance due to the CI on the ID already...

For more information I would strongly recommend to look at the underlying architecture of SQL Server (how CIs are built, how XML columns work under the hood, what is the impact of having functions in JOIN and WHERE clauses, etc.).

Hope this helps.
You are not authorized to post a reply.

Acceptable Use Policy