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.

I don't know if it would help, but I'd build the command completely before opening the connection. — AllenG, Aug 03 '10 at 16:00
@AllenG: plus I'd put `SqlConnection` and `SqlCommand` into `using(...) { ...... }` blocks, too — marc_s, Aug 03 '10 at 16:02
One thign I notice: when you execute from SSMS, you are not supplying Unicode string - from your code, you are. What is the stored proc expecting?? VARCHAR or NVARCHAR?? Also : why aren't you specifying maxa lengths for your NVARCHAR parameters in your code?? — marc_s, Aug 03 '10 at 16:03

score 21 · Answer 1 · edited Sep 12 '13 at 11:36

21

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

edited Sep 12 '13 at 11:36

LCJ

22,196
67
260
418

answered Mar 06 '13 at 18:09

user2141193

211
2
3

ALtering the procedure saved me too..... Interested to see why? Any expert opinion? – LCJ Sep 12 '13 at 11:37
Seems like the compiled sproc was corrupted in some way. – Starfleet Security Sep 08 '16 at 19:11
1

This saved me too! SP took 1 second in SSMS but timed out in code, very strange. ALTER PROCEDURE with no changes fixed this. – NetWave Oct 22 '21 at 09:20
Magic!!! That really worked.. @>@ – Vnuuk Oct 29 '21 at 15:44

score 9 · Answer 2 · answered Mar 13 '14 at 07:19

9

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

answered Mar 13 '14 at 07:19

Hoarst

171
2
9

`This can improve the procedure’s processing performance.` straight from the source. Good on ya', mate. – interesting-name-here May 02 '17 at 15:42
Also, that is essentially what the top voted answer does, right? – interesting-name-here May 02 '17 at 15:42
3

@GibralterTop Yes, except that "with recompile" prevents parameter sniffing so that you don't have to drop and recreate every time you encounter issues – Ashley Lee Aug 07 '17 at 17:11
1

Thanks. In my case, SQL took 6 seconds for 2000 rows. But, EF was timed out. Now, "with recompile" it works faster without any issue. I almost spent 5 hours with no clue. You saved me !! – Abdur Rahim Sep 01 '21 at 17:13

score 4 · Answer 3 · edited May 23 '17 at 12:17

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.

marc_s · Answer 4 · 2010-08-03T16:16:14.740

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)

Yes these type conversions from NVARCHAR could cause problems using indexes as well. — Martin Smith, Aug 03 '10 at 16:18

score 2 · Answer 5 · edited Sep 09 '22 at 16:29

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.

score 1 · Answer 6 · answered Aug 03 '10 at 16:04

1

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

answered Aug 03 '10 at 16:04

PsychoCoder

10,570
12
44
60

score 1 · Answer 7 · answered Feb 22 '12 at 13:37

1

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

answered Feb 22 '12 at 13:37

mahesh kuppi reddy

11
1

score 0 · Answer 8 · answered Jul 12 '16 at 09:22

0

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).

answered Jul 12 '16 at 09:22

Hirr0

1

1

After few days I again got this exception so alter is not a solution. – Icet May 12 '17 at 10:42

score 0 · Answer 9 · answered Sep 05 '18 at 15:04

0

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.

answered Sep 05 '18 at 15:04

maurox

1,254
1
19
33

score 0 · Answer 10 · answered Feb 14 '20 at 22:53

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.

score -2 · Answer 11 · answered Mar 13 '14 at 07:21

-2

change the CommandTimeout=0

sqlCmd.CommandTimeout = 0;

answered Mar 13 '14 at 07:21

sanjay bhansali

327
1
10

1

How on earth do you conclude that this is a better solution than what was already suggested here? – marknuzz Oct 24 '15 at 03:32
sir in our code, we can set command timeout to 0 so that code give not give error until stored procedure complete – sanjay bhansali Dec 21 '16 at 09:52
Thank you sir. This actually worked for me. Had to try it out bc I'm desperate to find a quick solution. But I need to figure out why the SP is soo slow to execute when running outside of the query tool. Tried everything above and nothing seems to work.. – Mhmdan01 May 07 '21 at 02:44
@Mhmdan01 please upgrade my solution. – sanjay bhansali Dec 14 '21 at 06:30

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

11 Answers11