XML需要长时间在MSSQL中解析

I have a large xml stream I pass to a stored procedure, but I'm getting a long response time of almost 1.30 minutes on a specific part of the xml which gets repeated alot.

我有一个大的XML流我传递给一个存储过程,但我得到的几乎1.30分钟很长的响应时间上被重复了很多的XML的特定部分。

I've checked the Execution Plan, and 2 sections of the xml structure takes up almost all of the processing time. I've also tried reading it into 2 temp tables first, before doing the insert, not that it helped. The XML is 368Kb in size, so I have a strong suspicion my xml is either wonky or i'm missing something to help ease the parsing.

我检查了执行计划,xml结构的2个部分占用了几乎所有的处理时间。在插入之前,我还尝试将它读入2个临时表,而不是它有帮助。 XML的大小是368Kb,所以我怀疑我的xml要么不稳定,要么我错过了一些有助于简化解析的东西。

Is there a better way to formulate the xml, to make it easier for Xpath() to parse? I really have no idea what else to do.

有没有更好的方法来制定xml,使Xpath()更容易解析?我真的不知道还能做什么。

The structure of the xml is:

xml的结构是:

<root>
<someobject>
 <param1></param1>
 <param1></param1>
</someobject>
<somethingelse>
 <param1></param1>
 <param1></param1>
</somethingelse>
<row1>
  <param1></param1>
  <param1></param1>
  <param1></param1>
  <param1></param1>
  <param1></param1>
  <param1></param1>
  <param1></param1>
  <param1></param1>
</row1> 

The query I currently use:

我目前使用的查询:

Insert Into [Table] 
Select   
  XmlParam.Doc.value('fieldA[1]','UNIQUEIDENTIFIER')
,XmlParam.Doc.value('fieldB[1]','UNIQUEIDENTIFIER')
,XmlParam.Doc.value('fieldC[1]','UNIQUEIDENTIFIER')
,XmlParam.Doc.value('fieldD[1]','UNIQUEIDENTIFIER')
,XmlParam.Doc.value('fieldE[1]','UNIQUEIDENTIFIER')
,XmlParam.Doc.value('fieldF[1]','UNIQUEIDENTIFIER')
,XmlParam.Doc.value('fieldG[1]','bit')
,XmlParam.Doc.value('fieldH[1]','bit')
      From @Xml.nodes('//Flow/StepData') XmlParam(Doc)  

Thanks for the help!

谢谢您的帮助!

1 个解决方案

#1

Try this, it might be faster for you.

试试这个,它可能会更快。

Select   
   XmlParam.Doc.value('(fieldA/text())[1]','UNIQUEIDENTIFIER')
  ,XmlParam.Doc.value('(fieldB/text())[1]','UNIQUEIDENTIFIER')
  ,XmlParam.Doc.value('(fieldC/text())[1]','UNIQUEIDENTIFIER')
  ,XmlParam.Doc.value('(fieldD/text())[1]','UNIQUEIDENTIFIER')
  ,XmlParam.Doc.value('(fieldE/text())[1]','UNIQUEIDENTIFIER')
  ,XmlParam.Doc.value('(fieldF/text())[1]','UNIQUEIDENTIFIER')
  ,XmlParam.Doc.value('(fieldG/text())[1]','bit')
  ,XmlParam.Doc.value('(fieldH/text())[1]','bit')
From @Xml.nodes('//Flow/StepData') XmlParam(Doc)