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
精彩评论