基于XML的查询在ADO.NET下非常缓慢，但在SSMS下瞬间完成。

Question

我处于一个长期的情况：通过SSMS运行的查询可以立即执行，只有少量读取，但是当通过ADO.NET运行时，由于读取量增加而变得缓慢，甚至会超时。与StackOverflow上的其他问题不同的是，清除查询缓存（或强制使用SSMS使用的缓存）似乎并没有起到作用。

通常，当其他人在StackOverflow上报告此情况时，他们的查询缓存已损坏。在所有这些情况中，解决方法要么是使用SET ARITHABORT ON（以匹配SSMS使用的会话设置），要么是运行DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE来强制重建查询缓存。但是这些技术在我的应用程序中没有任何区别，这使我相信还有更根本性的问题存在。

涉及的查询如下（由SQL Profiler捕获的实际逐字查询，仅为格式化而进行了清理）：

declare @p5 xml
set @p5=convert(xml,N'<r>
<n v="66ebc21b3bcb31e9a5ecbfb4b29fd2a47c37994c"/>
<n v="665919306fb23d9e685638a2d199e1e623745305"/>
<n v="a080c3b4e0c86e37b4d494d5efc09cebe20c6929"/>
<n v="245cb49bdeca9e37ef9bbd55877e21ade14e6282"/>
<n v="297650a6be65be332c1bb2aab426331a156ee342"/>
<n v="6a2668c8ab64fecf3b6925c7be613c61cef4dd7c"/>
<n v="09923f25f8b1de19f693bca1111bfa50d617856e"/>
<n v="0a7836d8e4e34f4ea92b2105eea5a99029949428"/></r>')
exec sp_executesql N'
            SELECT ixChangesetTag, ixRepo, ixChangeset, sTag, fBookmark
            FROM ChangesetTag
              INNER JOIN @p2.nodes(''/r/n'') X(n) ON X.n.value(''xs:hexBinary(@v)'', ''binary(20)'') = ixChangeset
            WHERE ixRepo = @p0 AND ixCustomer = @p1',N'@p0 bigint,@p1 int,@p2 xml',@p0=2,@p1=23363,@p2=@p5

（XML参数是为了允许使用参数化查询，平常我需要传递的对象数量不同。表值函数是2008年后这样做的方法，但我们的一些客户在2005上运行。）通过SSMS运行，实际使用的查询计划看起来合适（索引查找），大约需要200个读取操作，耗时4毫秒。通过Web应用程序运行，需要大约1秒钟进行4500次读取操作。我错过了什么？尽管进行了DBCC调用和ARITHABORT设置，但是否有些东西在通过Web应用程序运行时重新启用了错误的查询计划?

- Benjamin Pollack

---

“这是一个典型的使用XML来执行参数化的WHERE IN子句技巧” - 你再跟我解释一遍？ - Mitch Wheat

顺便说一句：“在所有这些情况下，修复方法要么是使用SET ARITHABORT ON运行ADO.NET查询（以匹配SSMS使用的会话设置），要么是运行DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE来强制查询缓存重建” - 这些都不是真正的解决方法。这只是治疗症状，而不是真正的原因。 - Mitch Wheat

@MitchWheat 我稍微整理了一下那个解释。这个查询用一个 WHERE ixChangeset IN (...) 子句替换了一个元素数量任意且可能非常大的查询，使其能够进行参数化并访问查询缓存。 - Benjamin Pollack

@BogdanSahlean 我已经阅读了那篇文章，并尽我所能地按照建议去做（包括使用确切的SQL，包括变量定义；在可能的情况下检查查询计划等）。你认为有什么具体的适当方法吗？最有帮助的事情是看到错误的计划，但由于它不是存储过程，我很难弄清楚如何做。 - Benjamin Pollack

@BenjaminPollack：请运行此查询（https://gist.github.com/2217486）并发布结果。此查询显示了SSMS会话和ASP.NET会话的设置。请用真实的ASP.NET会话ID替换<write ASP.NET session id>。 - Bogdan Sahlean

Answer 1

问题在于SQL Server通常会选择一种非常糟糕的执行策略，基本上是重复循环遍历XML，而不是进行合理的连接。解决方法是将XML放入临时表中并加以连接，这样可以可靠地产生良好的执行计划。请注意保留HTML标签。

- Benjamin Pollack

Answer 2

简单的解决方法是在（ixCustomer，ixRepo，ixChangeset）上放置多列索引。不知道这些列实际上是什么，它们是否唯一等等，很难提出更好的答案。

- Louis Ricci

---

考虑到当在 SSMS 中运行查询时可以立即运行，我不太相信这是索引问题。 - Benjamin Pollack

@Benjamin Pollack - 当你从SMS运行时，它会选择一个比从代码运行时更好的索引，这可能是由于一些模糊的字符集设置或.NET与默认SMS会话不同的原因。如果你使用多列索引，它将始终倾向于使用它。 - Louis Ricci