Stored procedure times out when ran from code, but not from query tool

Question

I am trying to figure out why a stored procedure call takes seconds in a SQL server express query window, but when I run call the stored procedure in code the query TIMES OUT. We are using sql server 2008. I know it is hard to say exactly what is going on without seeing the stored procedure. I'm just hoping this is a known issue. Any guidance is much appreciated.

SQL query that calls "STORED_PROCEDURE_X" and runs in 2 seconds in SQL server express query window:

EXEC STORED_PROCEDURE_X '07/01/2010', '07/31/2010', 0, '', 'true','', 'Top 20'

Code that calls "STORED_PROCEDURE_X" and TIMES OUT:

SqlConnection connSQL = null;
SqlCommand sqlCmd = null;
SqlDataAdapter sqlDataAdpater = null;
DataTable returnData = null;

try
{
    returnData = new DataTable();
    connSQL = new SqlConnection(sqlConnection);
    sqlCmd = new SqlCommand("STORED_PROC_X", connSQL);
    if (connSQL.State == ConnectionState.Closed)
    {
        connSQL.Open();
    }
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandTimeout = 600;
    sqlCmd.Parameters.Add("@StartDate", SqlDbType.NVarChar).Value = "07/01/2010";
    sqlCmd.Parameters.Add("@EndDate", SqlDbType.NVarChar).Value = "07/31/2010";

    sqlCmd.Parameters.Add("@AuditType", SqlDbType.Int).Value = "0";

    sqlCmd.Parameters.Add("@SortBy", SqlDbType.NVarChar).Value = "";

    sqlCmd.Parameters.Add("@IsClaimDepartment", SqlDbType.NVarChar).Value = "true";
    sqlCmd.Parameters.Add("@IdsList", SqlDbType.NVarChar).Value = "";
    sqlCmd.Parameters.Add("@ReportType", SqlDbType.NVarChar).Value = "Top 20";
    sqlDataAdpater = new SqlDataAdapter(sqlCmd);
    sqlDataAdpater.Fill(returnData);
    if (connSQL.State == ConnectionState.Open)
    {
        connSQL.Close();
    }
    return returnData;
}
catch (Exception ex)
{
    LogErrorMessages("ExecuteStoredProcedure", ex.Message);
    throw ex;
}

Exception Received:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Answer 1

So, running a stored proc that returned 30 records took me 00:00 second in the management console but when loading it in .net took me about 40 seconds, more than the default 30 sec TimeOut.

I just modify the stored proc and rerun the ALTER PROCEDURE... code without changing anything and the problem was solved instantly. I don't have more details about the source of this error, but at least it's a very quick fix

Answer 2

What helped me always a lot was to Add the "with recompile" Option to the Procedure.

http://technet.microsoft.com/en-us/library/ms190439.aspx