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?
In Sql Server Management Studio this runs in 2 seconds (on production database):
@DepartmentID = NULL
string selectCommand = @"
@DepartmentID = NULL";
string connectionString = "server=***;database=***;user id=***;pwd=***";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(selectCommand, connection))
using (SqlDataReader reader = command.ExecuteReader())
selectCommand = "sp_Stat"
CommandType = StoredProcedure
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
SET ARITHABORT ON
Another thing that can be important is the
SET options that are enabled. Some of these options change the query plan sufficiently to change the profile. Some can have a huge impact if you are looking at (for example) a calculated + persisted (and possibly indexed) column: if the
SET options aren't compatible, it can be forced to re-calculate the values, rather than using the indexed value - which can change an index seek into a table scan + calculation.
Try using the profiler to see what
SET options are "in play", and see if using those options changes things.
Another impact is the connection string; for example, if you enable MARS that can change the behaviour in subtle ways.
Finally, transactions (implicit (
TransactionScope) or explicit) can have a huge impact, depending on the isolation level.