OPENXML()和性能 [英] OPENXML() and performance

问题描述

嗨.

我有一个存储过程，它工作正常.
输入参数之一是XML(@p_xml)，其处理方式如下:

Hi.

I have a stored procedure, and it''s working fine.
One of the input parameters is XML (@p_xml) and is processed like this:

DECLARE @docHandle int<br />EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml<br /><br />SELECT * INTO #raw_table <br />FROM <br />OPENXML(@docHandle, ''//NODE'', 2)  <br />

问题是当XML很大时(〜20 MB，约35000个节点).然后OPENXML语句需要7-8分钟才能完成.

是否有任何方法可以加快OPENXML的速度，或者是否有其他解决方法?

我已经尝试了select into和insert into and临时表和表变量.所有结果都相同.

有人可以帮助我吗?

Kjetil

The problem is when the XML is really big (~20 MB with ~35000 nodes). Then the OPENXML statement uses 7-8 minutes to finish.

Is there any way to speed up OPENXML, or is there any other workaround ?

I have tried both select into and insert into and both temp table and table variable. All with the same result.

Could someone help me ?

Kjetil

推荐答案

我对此表示怀疑.它不是很快的事情.顺便说一句，通过显示的代码，您确实意识到您已将tempdb数据库中的大多数系统表锁定了7至8分钟的运行时间.在运行期间选择进入锁定系统表，这可能会严重影响其他用户.

对不起，我无济于事.

I doubt it. Its not the fast thing around. BTW, with the code shown, you do realise you are locking most of the system tables in your tempdb database for the 7-8 minutes it takes to run. A select into locks system tables for the duration of its run, this could seriously impact other users.

Sorry I can''t help further.

这篇关于OPENXML()和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！