All geek questions in one place

XML request is extremely slow through ADO.NET, instantly through SSMS

I am in a multi-year situation: a request that runs instantly through SSMS with multiple reads, but is slow enough to timeout with thousands of reads when running through ADO.NET. Unlike other questions I can find in StackOverflow, clearing the request cache (or forcing the use of a single SSMS application) does not seem to do the trick.

Typically, when others report this situation to StackOverflow, they have corrupted request caches. In all of these cases, the fix was to either run ADO.NET queries using SET ARITHABORT ON (to match the session settings used by SSMS), or to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to force the query cache to rebuild. These methods have no meaning in my application, making me believe that something more fundamental is happening there.

The question that arises is this (the actual verbatim query captured by SQL Profiler, cleared for formatting only):

 declare @p5 xml set @p5=convert(xml,N'<r> <nv="66ebc21b3bcb31e9a5ecbfb4b29fd2a47c37994c"/> <nv="665919306fb23d9e685638a2d199e1e623745305"/> <nv="a080c3b4e0c86e37b4d494d5efc09cebe20c6929"/> <nv="245cb49bdeca9e37ef9bbd55877e21ade14e6282"/> <nv="297650a6be65be332c1bb2aab426331a156ee342"/> <nv="6a2668c8ab64fecf3b6925c7be613c61cef4dd7c"/> <nv="09923f25f8b1de19f693bca1111bfa50d617856e"/> <nv="0a7836d8e4e34f4ea92b2105eea5a99029949428"/></r>') exec sp_executesql N' SELECT ixChangesetTag, ixRepo, ixChangeset, sTag, fBookmark FROM ChangesetTag INNER JOIN @p2.nodes(''/r/n'') X(n) ON Xnvalue(''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

(The XML parameter is intended to allow the use of a parameterized query, in which I usually have problems, since the number of objects I want to transfer varies. Table-oriented procedures will be the way of 2008, but some of our clients work in 2005 year.)

Running through SSMS, the actual query plan used looks appropriate (index search) and takes about 200 views in 4 ms. Launching through a web application takes about 4,500 views per second.

What am I missing here? Can something restore a bad query plan when launched through a web application, despite DBCC and ARITHABORT ?

+4
source share
2 answers

The problem was that SQL Server usually opted for an absolutely terrible execution strategy, basically repeating the XML loop, rather than making a reasonable connection. The fix was to put the XML in a temporary table and join it instead, which reliably gave good execution plans.

+1
source

A simple fix will include an index with multiple columns (ixCustomer, ixRepo, ixChangeset). Not knowing what the actual columns are, whether they are unique, etc., It’s hard to find the best answer.

+1
source

Source: https://habr.com/ru/post/1403682/

More articles:


All Articles