CODEWITHSUNDEEP
c#sql-serverstored-procedures
Blake Mills
Blake Mills

Reputation: 844

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

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.

Upvotes: 9

Views: 29020

Answers (11)

Tom Psillas
Tom Psillas

Reputation: 21

I had a similar issue with stored procedures running slower than the same query in the query window. I had tried everything from coding for parameter sniffing (local variables), removing clustered indexes and using only non-clustered, etc. It still took 22 seconds minuimum to retrieve a varchar field, using a nvarchar parameter.

I originally thought it was just the difference between nvarchar and varchar and changed the database field to nvarchar. I lost another full day, moving 50 million records to a new table and re-indexing. Still took over 22 seconds.

Finally, I changed any key fields in the tables from nvarchar to varchar, plus all the parameters and wow; back dowen to less than 1 second.

I strongly believe this is a bug in SQL Server, that has never got corrected. How can you run a query directly in the query window, or call sql from vb.net or c# code and get results in less than 1 second; then run the same query, using parameters, in a stored procedure and get such horrendus results?

Short answer: Stay away from nvarchar data types at all costs.

Also, learn to use the merge statement to move data into large tables to avoid timeouts. Set recovery mode to simple, while running a large merge query; then put back to full recovery.

Boy, did I learn alot this week. Over 80 hours of education that I did not need.

Upvotes: 2

Jeff Pallatt
Jeff Pallatt

Reputation: 1

We had this same issue and thought it was parameter sniffing also, but after trying many things including changing parameter sniffing, using with recompile, drop/recreate, updating statistics, freeing proc cache, we found none of these worked. We tracked it down to a single index that needed to be added. We had just switched from the legacy cardinality estimator to the post-2014 estimator. Switching back to the legacy estimator resolved the issue, or using the new estimator and adding the index.

Upvotes: 0

maurox
maurox

Reputation: 1292

I tried with "with recompile", with "arithabort off", to change the code etc etc but at the end only restarting sql server I solved the problem.

Upvotes: 0

Hirr0
Hirr0

Reputation: 1

I had same issue. My stored proc executed from MSSMS or dbForgeStudio, but not from C# code (SqlCommand). I fixed this problem by altering stored proc in SQL server (without any changes).

Upvotes: 0

sanjay bhansali
sanjay bhansali

Reputation: 327

change the CommandTimeout=0 

sqlCmd.CommandTimeout = 0;

Upvotes: -2

Hoarst
Hoarst

Reputation: 191

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

Upvotes: 11

user2141193
user2141193

Reputation: 251

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

Upvotes: 25

mahesh kuppi reddy
mahesh kuppi reddy

Reputation: 11

Make sure the paramters being passed to SP match the ones on the databse [thi is Case sensitive ]

Upvotes: 1

marc_s
marc_s

Reputation: 754618

Try to a) reorganize your code, and b) increase the timeout:

DataTable returnData = null;

try
{
    using(SqlConnection connSQL = new SqlConnection(sqlConnection))
    using(SqlCommand sqlCmd = new SqlCommand("STORED_PROC_X", connSQL))
    {
       sqlCmd.CommandType = CommandType.StoredProcedure;
       sqlCmd.CommandTimeout = 1200;

       // those two parameters should really be SqlDbType.DateTime!!
       sqlCmd.Parameters.Add("@StartDate", SqlDbType.NVarChar, 25).Value = "07/01/2010";
       sqlCmd.Parameters.Add("@EndDate", SqlDbType.NVarChar, 25).Value = "07/31/2010";

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

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

       // this parameter should really be SqlDbType.Bit !!
       sqlCmd.Parameters.Add("@IsClaimDepartment", SqlDbType.NVarChar, 50).Value = "true";

       sqlCmd.Parameters.Add("@IdsList", SqlDbType.NVarChar, 25).Value = "";
       sqlCmd.Parameters.Add("@ReportType", SqlDbType.NVarChar, 25).Value = "Top 20";

       SqlDataAdapter sqlDataAdpater = new SqlDataAdapter(sqlCmd);

       returnData = new DataTable();
       sqlDataAdpater.Fill(returnData);
   }

   return returnData;
}
catch (Exception ex)
{
   LogErrorMessages("ExecuteStoredProcedure", ex.Message);
   throw;
}

The SqlDataAdapter will open and close the connection itself - no need to do that explicitly.

Furthermore, I would

  • define a sensible max length for your NVARCHAR parameters (what does the stored proc except)
  • pass the dates as DATETIME ! (not NVARCHAR)
  • pass the boolean value as BIT ! (not NVARCHAR)
  • when you re-throw an exception, only use throw and not throw ex (if you use throw ex, you're basically breaking the stack trace and cannot figure out where the exception really came from)

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453378

Assuming that you are passing the same parameters from your code as when you are testing in SSMS and your SSMS test is exactly the same in terms of data type usage I would have thought this is likely to be a parameter sniffing issue.

Do you have access to SQL Profiler (doesn't come with Express edition) to get both actual execution plans? If not you can follow the advice in this answer to get the plans.

Upvotes: 4

PsychoCoder
PsychoCoder

Reputation: 10755

Are you using any transactions in your stored procedure? Uncommitted transactions will cause this exact error message.

Upvotes: 1

Related Questions