I have a stored procedure that executes much faster from Sql Server Management Studio (2 seconds) than when run with System.Data.SqlClient.SqlCommand (times out after 2 minutes).
What could be the reason for this?
Details: In Sql Server Management Studio this runs in 2 seconds (on production database):
EXEC sp_Stat
@DepartmentID = NULL
In .NET/C# the following times out after 2 minutes (on production database):
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())
{
}
}
}
}
I also tried with selectCommand = "sp_Stat", CommandType = StoredProcedure, and an SqlParameter, but it's the same result.
And without EXEC it's the same result as well.
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...
What Marc Gravell wrote about different SET values makes the difference in the presented case.
SQL Server Profiler showed that Sql Server Management Studio runs the following SET's that .NET Sql Client Data Provider does not:
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
When I included these, the same query took the same amount of time in SSMS and .NET.
And the responsible SET is ...
SET ARITHABORT ON
What have I learnt? Maybe to use a profiler instead of guessing...
(The solution at first seemed to be related to parameter sniffing. But I had mixed some things up...)
ARITHABORT OFFyou may be JOINing two tables by a string and a numeric field. In my case, adding a simpleISNUMERIC(string_field)=1check before the join solved the problem :) – Promethean