FOR XML 和性能
有没有人对如何在以下情况下提高性能有任何提示/技巧：
- 我有一个大约的记录集。 500,000 行。
- 此查询会提取大量数据。为了最大限度地减少查询时间，我将其分解为几个较小的查询；所有内容都存储在带有索引等的临时表中。在最后;我将所有较小的查询组装到一个临时表中，作为最终查询的“主”记录。
- 上述步骤的当前执行时间约为 2 分钟（考虑到提取的数据量，这还不算太糟糕）。
- 最终结果需要以 XML 格式交付。在编写最终查询时，我使用
FOR XML EXPLICIT将其转换为正确的格式。 - 我遇到的问题是上面的 XML 查询花费了 30 多分钟，在本例中太长了。
因此，我在这里确实有几个选项：
- 我可以将此数据“预加载”到实际表中，然后查询该数据，而不是使用临时表。我不确定这会为我赢得很多时间，因为问题不在于加载数据，而在于生成 XML 需要很长时间。
- 上述 XML 最终将被加载到我们的 C# 代码库中，并上传到其最终目的地。简单地将记录集加载到 C# 中并在那里进行 XML 转换（而不是在数据库端进行）是否更有意义？
- 现在我正在使用
FOR XML EXPLICIT;使用任何其他 XML 模式（RAW、AUTO、PATH）是否可以获得任何性能优势？
再次感谢。
〜吉姆
Does anybody have any tips/tricks as to how to improve performance in the following scenario:
- 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 EXPLICITto 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.
So, I do have a couple options here:
- 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)?
Thanks again.
~Jim
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是您的选择。 XML 转换在 C# 中比在 SQL 中高效得多。
This is your option right here. XML transformation is going to be far more efficient in C# than in SQL.