.NET Data Adapter Timeout SP Issue

Question

We have a SQL Server stored procedure that runs fine in SQL Manager directly, does a rather large calculation but only takes 50-10 seconds max to run.

However when we call this from the .NET app via a data adapter it times out. The timeout however happens before the timeout period should, we set it to 60 seconds and it still times out in about 20 seconds or less.

I've Googled the issue and seen others note issues where a SP works fien directly but is slow via a data adpater call.

Any ideas on how to resolve this?

Answer 1

When you say it runs fine in SSMS directly, do you mean that executing the stored procedure itself runs fine, or that the underlying SQL runs fine?

From your description, this sounds like an example of parameter sniffing. Basically, SQL Server has cached an execution plan that is optimal for one set of parameters but exceptionally poor for most others.

You can use the RECOMPILE option on the query in your stored procedure to force recompilation on every execution. If this isn't called frequently or compilation doesn't take long, you can make use of this trick.

The other solution is to copy the stored procedure parameters into local variables and use those in the query. Example:

CREATE PROCEDURE my_proc
  @var1 INT
AS
  DECLARE @_var1 AS INT;
  SET @_var1 = @var1;

  SELECT col1, col2, col3
    FROM t1
   WHERE t1.pk = @_var1;

Answer 2

Make sure you've checked all your timeouts. I don't know if you're using asp.net or winforms but at least the first two of this list will apply:
http://stackoverflow.com/questions/848628/time-out-period-not-elapsed-but-still-timing-out-see-code/848664#848664

Another thing to check is that you are setting up your parameters correctly. Doing it wrong can break indexes. The place I've seen this is when you have an index on a varchar column that is key to your query, and set up your parameter for that column using a plain "AddWithValue()" or other function that doesn't explicitly set the type. .Net will give you an nvarchar parameter by default because strings in .Net are unicode. The result is that it won't be able to use the index.

Answer 3

Change the timeout property of the SqlConnection object