StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery times out in .Net SqlCommand.ExecuteNonQuery, works in SQL Server Management Studio
text
Body
copied!<p><strong>Update: Problem solved, and staying solved.</strong> <em>If you want to see the site in action, visit <a href="http://www.tweet08.com" rel="nofollow noreferrer">Tweet08</a></em></p> <p>I've got several queries that act differently in SSMS versus when run inside my .Net application. The SSMS executes fine in under a second. The .Net call times out after 120 seconds (connection default timeout).</p> <p>I did a SQL Trace (and collected <em>everything</em>) I've seen that the connection options are the same (and match the SQL Server's defaults). The SHOWPLAN All, however, show a huge difference in the row estimates and thus the working version does an aggressive Table Spool, where-as the failing call does not.</p> <p>In the SSMS, the datatypes of the temp variables are based on the generated SQL Parameters in the .Net, so they are the same.</p> <p>The failure executes under Cassini in a VS2008 debug session. The success is under SSMS 2008 . Both are running against the same destination server form the same network on the same machine.</p> <p>Query in SSMS:</p> <pre><code>DECLARE @ContentTableID0 TINYINT DECLARE @EntryTag1 INT DECLARE @ContentTableID2 TINYINT DECLARE @FieldCheckId3 INT DECLARE @FieldCheckValue3 VARCHAR(128) DECLARE @FieldCheckId5 INT DECLARE @FieldCheckValue5 VARCHAR(128) DECLARE @FieldCheckId7 INT DECLARE @FieldCheckValue7 VARCHAR(128) SET @ContentTableID0= 3 SET @EntryTag1= 8 SET @ContentTableID2= 2 SET @FieldCheckId3= 14 SET @FieldCheckValue3= 'igor' SET @FieldCheckId5= 33 SET @FieldCheckValue5= 'a' SET @FieldCheckId7= 34 SET @FieldCheckValue7= 'a' SELECT COUNT_BIG(*) FROM dbo.ContentEntry AS mainCE WHERE GetUTCDate() BETWEEN mainCE.CreatedOn AND mainCE.ExpiredOn AND (mainCE.ContentTableID=@ContentTableID0) AND ( EXISTS (SELECT * FROM dbo.ContentEntryLabel WHERE ContentEntryID = mainCE.ID AND GetUTCDate() BETWEEN CreatedOn AND ExpiredOn AND LabelFacetID = @EntryTag1)) AND (mainCE.OwnerGUID IN (SELECT TOP 1 Name FROM dbo.ContentEntry AS innerCE1 WHERE GetUTCDate() BETWEEN innerCE1.CreatedOn AND innerCE1.ExpiredOn AND (innerCE1.ContentTableID=@ContentTableID2 AND EXISTS (SELECT * FROM dbo.ContentEntryField WHERE ContentEntryID = innerCE1.ID AND (ContentTableFieldID = @FieldCheckId3 AND DictionaryValueID IN (SELECT dv.ID FROM dbo.DictionaryValue AS dv WHERE dv.Word LIKE '%' + @FieldCheckValue3 + '%')) ) ) ) OR EXISTS (SELECT * FROM dbo.ContentEntryField WHERE ContentEntryID = mainCE.ID AND ( (ContentTableFieldID = @FieldCheckId5 AND DictionaryValueID IN (SELECT dv.ID FROM dbo.DictionaryValue AS dv WHERE dv.Word LIKE '%' + @FieldCheckValue5 + '%') ) OR (ContentTableFieldID = @FieldCheckId7 AND DictionaryValueID IN (SELECT dv.ID FROM dbo.DictionaryValue AS dv WHERE dv.Word LIKE '%' + @FieldCheckValue7 + '%') ) ) ) ) </code></pre> <p>Trace's version of .Net call (<em>some formatting added</em>):</p> <pre><code>exec sp_executesql N'SELECT COUNT_BIG(*) ...' ,N'@ContentTableID0 tinyint ,@EntryTag1 int ,@ContentTableID2 tinyint ,@FieldCheckId3 int ,@FieldCheckValue3 varchar(128) ,@FieldCheckId5 int ,@FieldCheckValue5 varchar(128) ,@FieldCheckId7 int ,@FieldCheckValue7 varchar(128)' ,@ContentTableID0=3 ,@EntryTag1=8 ,@ContentTableID2=2 ,@FieldCheckId3=14 ,@FieldCheckValue3='igor' ,@FieldCheckId5=33 ,@FieldCheckValue5='a' ,@FieldCheckId7=34 ,@FieldCheckValue7='a' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload