开发者

If a table has two xml columns, will inserting records be a lot slower?

开发者 https://www.devze.com 2022-12-24 13:16 出处:网络
Is it a bad thing to have two xml columns in one table? + How much slower are these xml columns in terms of updating/inserting/reading data?

Is it a bad thing to have two xml columns in one table? + How much slower are these xml columns in terms of updating/inserting/reading data?

In profiler this kind of insert normally takes 0 ms, but sometimes it goes up to 160ms:

declare @p8 xml
set @p8=convert(xml,N'<interactions><interaction correct="false" score="0" 
id="0"    gapid="0" x="61" y="225"><feedback/><element id="0" position="0"  
elementtype="1"><asset/></element></interaction><interaction correct="false" 
score="0" id="1" gapid="1" x="64" y="250"><feedback/><element id="0" position="0" 
elementtype="1"><asset/></element></interaction><interaction correct="false"
score="0" id="2" gapid="2" x="131" y="250"><feedback/><element id="0" position="0" 
elementtype="1"><asset/></element></interaction></interactions>')

declare @p14 xml
set @p14=convert(xml,N'<contentinteractions/>')
exec sp_executesql N'INSERT INTO    
[dbo].[PackageSessionNodes]([dbo].[PackageSessionNodes].[PackageSessionId],
[dbo].[PackageSessionNodes].[TreeNodeId],[dbo].[PackageSessionNodes].[Duration],
[dbo].[PackageSessionNodes].[Score],[dbo].[PackageSessionNodes].[ScoreMax],
[dbo].[PackageSessionNodes].[Interactions],[dbo].[PackageSessionNodes].[BrainTeaser],
[dbo].开发者_C百科[PackageSessionNodes].[DateCreated],
[dbo].[PackageSessionNodes].[CompletionStatus],
[dbo].[PackageSessionNodes].[ReducedScore],
[dbo].[PackageSessionNodes].[ReducedScoreMax],
[dbo].[PackageSessionNodes].[ContentInteractions])
VALUES     (@ins_dboPackageSessionNodesPackageSessionId,
@ins_dboPackageSessionNodesTreeNodeId,
@ins_dboPackageSessionNodesDuration,
@ins_dboPackageSessionNodesScore,
@ins_dboPackageSessionNodesScoreMax,
@ins_dboPackageSessionNodesInteractions,
@ins_dboPackageSessionNodesBrainTeaser,
@ins_dboPackageSessionNodesDateCreated,
@ins_dboPackageSessionNodesCompletionStatus,
@ins_dboPackageSessionNodesReducedScore,
@ins_dboPackageSessionNodesReducedScoreMax,
@ins_dboPackageSessionNodesContentInteractions)
;
SELECT SCOPE_IDENTITY() as new_id

This is the table:

CREATE TABLE [dbo].[PackageSessionNodes](
[PackageSessionNodeId] [int] IDENTITY(1,1) NOT NULL,
[PackageSessionId] [int] NOT NULL,
[TreeNodeId] [int] NOT NULL,
[Duration] [int] NULL,
[Score] [float] NOT NULL,
[ScoreMax] [float] NOT NULL,
[Interactions] [xml] NOT NULL,
[BrainTeaser] [bit] NOT NULL,
[DateCreated] [datetime] NULL,
[CompletionStatus] [int] NOT NULL,
[ReducedScore] [float] NOT NULL,
[ReducedScoreMax] [float] NOT NULL,
[ContentInteractions] [xml] NOT NULL,
 CONSTRAINT [PK_PackageSessionNodes] PRIMARY KEY CLUSTERED 
(
[PackageSessionNodeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PackageSessionNodes]  WITH CHECK ADD  CONSTRAINT   
[FK_PackageSessionNodes_PackageSessions] FOREIGN KEY([PackageSessionId])
REFERENCES [dbo].[PackageSessions] ([PackageSessionId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PackageSessionNodes] CHECK CONSTRAINT 
[FK_PackageSessionNodes_PackageSessions]
GO

ALTER TABLE [dbo].[PackageSessionNodes]  WITH CHECK ADD  CONSTRAINT  
[FK_PackageSessionNodes_TreeNodes] FOREIGN KEY([TreeNodeId])
REFERENCES [dbo].[TreeNodes] ([TreeNodeId])
GO

ALTER TABLE [dbo].[PackageSessionNodes] CHECK CONSTRAINT 
[FK_PackageSessionNodes_TreeNodes]
GO

ALTER TABLE [dbo].[PackageSessionNodes] ADD  CONSTRAINT 
[DF_PackageSessionNodes_Score] DEFAULT ((-1)) FOR [Score]
GO

ALTER TABLE [dbo].[PackageSessionNodes] ADD  CONSTRAINT 
[DF_PackageSessionNodes_ScoreMax]  DEFAULT ((-1)) FOR [ScoreMax]
GO

ALTER TABLE [dbo].[PackageSessionNodes] ADD  CONSTRAINT 
[DF_PackageSessionNodes_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[PackageSessionNodes] ADD  CONSTRAINT 
[DF_PackageSessionNodes_ReducedScore]  DEFAULT ((-1)) FOR [ReducedScore]
GO

ALTER TABLE [dbo].[PackageSessionNodes] ADD  CONSTRAINT 
[DF_PackageSessionNodes_ReducedScoreMax]  DEFAULT ((-1)) FOR [ReducedScoreMax]
GO


There will be at least some speed impact, since when you put text into an xml column the system validates it to ensure it's well-formed, and can collapse redundant tags and whitespace. But these operations are quite fast -- there should not be significant extra overhead just to do these conversions.

It's more likely that locking, index issues, or resource problems on the server are responsible for the occasional slower insert.


In your comment, you said "To the same table without the xml columns"

If you see the occasional spike in CPU in profiler, then it could be a page split. Inserting a chunk of XML data into a data page allocated to a table may fill up the page, so a split is required. A similar table without XML obviously has less overhead and less space is used.

There is also the extra overhead of storing LOB type data, rather than plain old varchar types

0

精彩评论

暂无评论...
验证码 换一张
取 消