Databases
StackOverflow2013
Postsdbo
POWhy is some sql query much slower when used with SqlCommand?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy is some sql query much slower when used with SqlCommand?
    text
    copied!<p>I have a stored procedure that executes much faster from Sql Server Management Studio (2 seconds) than when run with <code>System.Data.SqlClient.SqlCommand</code> (times out after 2 minutes).</p> <p>What could be the reason for this?</p> <hr> <p>Details: In Sql Server Management Studio this runs in 2 seconds (on production database):</p> <pre>EXEC sp_Stat @DepartmentID = NULL </pre> <p>In .NET/C# the following times out after 2 minutes (on production database):</p> <pre><code>string selectCommand = @" EXEC sp_Stat @DepartmentID = NULL"; string connectionString = "server=***;database=***;user id=***;pwd=***"; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(selectCommand, connection)) { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { } } } } </code></pre> <p>I also tried with <code>selectCommand = "sp_Stat"</code>, <code>CommandType = StoredProcedure</code>, and an <code>SqlParameter</code>, but it's the same result.</p> <p>And without <code>EXEC</code> it's the same result as well.</p> <p>On an almost data-empty development database both cases finishes in less than 1 second. So it's related to that there's a lot of data in the database, but it seems to only happen from .NET...</p> <hr> <p>What Marc Gravell wrote about different <code>SET</code> values makes the difference in the presented case.</p> <p>SQL Server Profiler showed that Sql Server Management Studio runs the following <code>SET</code>'s that .NET Sql Client Data Provider does not:</p> <pre><code> SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON SET NOEXEC, PARSEONLY, FMTONLY OFF </code></pre> <p>When I included these, the same query took the same amount of time in SSMS and .NET. And the responsible <code>SET</code> is ...</p> <pre><code>SET ARITHABORT ON </code></pre> <p>What have I learnt? Maybe to use a profiler instead of guessing...</p> <p>(The solution at first seemed to be related to parameter sniffing. But I had mixed some things up...)</p>
 

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