OPENXML 远程扫描性能
我正在寻找有关 OPENXML 的一些建议。 具体来说，就是表现。 我发现一小段 XML 的性能非常慢。
与此相关的某些原因导致远程扫描。 关于如何调整它有什么想法吗？
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @ResourceXML
DECLARE @tmpRes TABLE (ResourceID int, Quantity int, FormID int)
INSERT INTO @tmpRes (ResourceID, Quantity, FormID)
SELECT TMP.ID, TMP.Q, RESC.FormID
FROM OPENXML(@idoc, '/Resources/R') WITH (ID int, Q int) TMP
INNER JOIN dbo.tblResources RESC ON TMP.ID = RESC.ResourceID
WHERE RESC.OrgID = @OrgID
I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML.
Something about this is causing a Remote Scan. Any ideas on how to go about tuning it?
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @ResourceXML
DECLARE @tmpRes TABLE (ResourceID int, Quantity int, FormID int)
INSERT INTO @tmpRes (ResourceID, Quantity, FormID)
SELECT TMP.ID, TMP.Q, RESC.FormID
FROM OPENXML(@idoc, '/Resources/R') WITH (ID int, Q int) TMP
INNER JOIN dbo.tblResources RESC ON TMP.ID = RESC.ResourceID
WHERE RESC.OrgID = @OrgID
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
远程扫描是优化器用来访问 XML 数据的方法，因为 OPENXML 实际上位于数据库引擎外部 - 因此该位是正常的。
然而，优化器使用的统计信息似乎确实存在问题，本文
Remote Scan is what the optimizer uses to access the XML data as OPENXML is actually external to the database engine - so that bit is normal.
However there does seem to be an issue with the statistics that the optimizer uses, this article here discusses that, offers some pointers, but I don't know whether this helps your particular situation.