Sql Timeout in Application but not in Sql Server Mgmt Studio

Question

I have this relatively complex stored procedure that performs a database search. It's a good 400 lines long, but when I execute it in Sql Server Management Studio using a search parameter that will return no hits, it takes about 1 to 3 seconds to complete. It takes nearly the same time to execute a search that returns a few hundred rows.

However, when I run it from a web application that calls this stored procedure, using the exact same search parameters, it basically never returns a result. I've increased connection and command timeouts to 5 minutes, and still never comes back. In fact, it takes the 5 minutes I allowed it before it throws a timeout exception.

This timeout occurs both on a web server and my workstation, going against a production database.

Any ideas on what I might be up against? The code is C#, and I've run Microsoft.Practices.EnterpriseLibrary code as well as straight SqlClient code, inside a using clause and without one. There are 19 parms, and so here's a shortened version of my latest attempt:

IList<QueryResultsItem> resultsItem = new List<QueryResultsItem>();

string connString = 
  "server=[servername];database=[dbname];uid=[userid];pwd=[psw];Connection Timeout=300";

SqlConnection conn = new SqlConnection(connString);
conn.Open();

SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 300;
cmd.Connection = conn;
cmd.CommandText = "ap_tems_get_batch_history_list";
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parm = new SqlParameter();
parm.ParameterName = "@parm";
parm.Value = [value];
parm.Size = 3;
parm.SqlDbType = SqlDbType.Char;
cmd.Parameters.Add(parm);
...

SqlDataReader reader = cmd.ExecuteReader();

Answer 1

I met quite a few these kinds of time-out issues in web applications. Here are my findings.

1. SSMS connects to and executes against database directly via sql provider.
2. Web applications go through the whole Microsoft stack from IIS, business layers,..., then the sql provider to database. That is one reason.
3. This is the main reason. your C# code builds the params, and talks to database through a database schema/metadata/information inspection process, which takes considerable longer time. If you set debugger break point around the SqlDataReader, you will find out tons of metadata getting back from database.

My experience is that if it takes more than 1 second to finish the sql statements in SSMS, you likely get time-out issue in default setting of web applications. It happens more often in selects than inserts or updates.

My solutions:

1. Increase the time-out value in web.config;
2. Performance-tune your stored procedure using execution plan;
3. Avoid using SqlDataReader. Use SqlCommand for insert and update;
4. Return like top 100 of records instead of all the records if SqlDataReader must be used.