FOR XML和性能 [英] FOR XML and Performance

问题描述

有人在以下情况下如何提高效果，有任何提示/技巧：

Does anybody have any tips/tricks as to how to improve performance in the following scenario:

• 我有一个约为中。 500,000行。


• 为此查询提取了大量数据。为了最小化查询时间，我把它分成几个较小的查询;所有存储在具有索引等的临时表中。最后;我将所有较小的查询集合到一个临时表中作为最终查询的主记录。


• 上述步骤的当前执行时间大约为2分钟（不太糟糕，考虑要提取的数据量）


• 最终结果需要以XML格式提交。在写我的最终查询时，我使用 FOR XML EXPLICIT 将这个格式设置为正确的格式。


• 正在运行的是上面的XML查询需要30多分钟，在这种情况下这太长了。

• I have a record set of approx. 500,000 rows.
• There's a lot of data being pulled for this query. In order to minimize the query time, I've broken this up into several smaller queries; all stored in temp tables with indexes and such. At the end; I assemble all the smaller queries into one temp table to serve as the "master" record for the final query.
• Current execution time for the above step is approximately 2 minutes (not too bad, considering the amount of data being pulled)
• The final result needs to be delivered in XML format. When writing my final query, I'm using FOR XML EXPLICIT to put this into the proper format.
• The problem I'm running into is that the above XML query is taking 30+ minutes, which is way too long in this case.

这里有几个选项：

• 我可以将这些数据预加载到实际表中，然后查询而不是使用临时表。我不确定这会给我多少时间，因为问题不是加载数据，而是生成XML需要很长时间。


• 上面的XML将最终加载到我们的C＃代码库中，以便上传到其最终目的地。简单地将记录集加载到C＃中，并在那里进行XML转换，而不是在数据库侧进行它更有意义吗？


• 现在我使用 FOR XML EXPLICIT ;使用任何其他XML模式（ RAW ， AUTO ， PATH ）

• I'd be able to "pre-load" this data into an actual table, and then query off that instead of using temp tables. I'm not sure this will buy me much time, as the issue isn't with loading the data, but rather the long time that it takes to generate the XML.
• The above XML will eventually be loaded into our C# codebase to be uploaded to its final destination. Does it make more sense to simply load the record set into C#, and do the XML transformation there, instead of doing it on the database side?
• Right now I'm using FOR XML EXPLICIT; are there any performance benefits gained by using any of the other XML modes (RAW, AUTO, PATH)?

再次感谢。

〜Jim

推荐答案

这是您的选择。 XML转换在C＃中将比在SQL中更加高效。

This is your option right here. XML transformation is going to be far more efficient in C# than in SQL.

上述XML最终将被加载到我们的C＃代码库
上传到其最终目的地。简单地
将记录集加载到C＃中，并在那里执行XML转换，
而不是在数据库端执行它更有意义吗？

The above XML will eventually be loaded into our C# codebase to be uploaded to its final destination. Does it make more sense to simply load the record set into C#, and do the XML transformation there, instead of doing it on the database side?

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