Query executes in 4 seconds directly in SQL Server but takes > 30 seconds in ASP.NET?

Question

I have a query (about 1600 lines stored as a Stored Procedure) that takes about 3 seconds to execute (after optimizing it by adding the correct indexes), when executed inside SQL Server Management Studio.

I wrote a wrapper for this in C# and provided myself with the ability to use a URI to execute this query. However, this takes more than 30 seconds to execute and because of this, when I run this query as part of a loop, the browser stalls due to too many pending requests. I wrote the wrapper like this:

try
{
   string ConString = Constants.connString;

   using (con = new SqlConnection(ConString))
   {
      cmd = new SqlCommand(sql, con);
      con.Open();
      dr = cmd.ExecuteReader();

      while (dr.Read())
      {
         ...
      }
   }
}

My connection string is this:

Data Source={0};Initial Catalog={1};Integrated Security=True;MultipleActiveResultSets=true

I know the query itself is good because I've run it multiple times inside SSMS and it worked fine (under 5 seconds on an average). And, I'd be happy to provide with more debug information, except that I don't know what to provide.

To troubleshoot such problems, where would I start?

EDIT:

I ran SQL Profiler and collected some stats. This is what I am observing. Very strange that it is the exact query being executed. Let me know if there is anything else I can do at this point.

Answer 1

Ok; Finally, found the answer here and here. Answer replicated here for convenience. Thanks goes to the original poster, Jacques Bosch, who in turn took it from here. Cannot believe this problem was solved in 2004!

The problem seems to be caused by SQL Server's Parameter Sniffing. To prevent it, just assign your incoming parameter values to other variables declared right at the top of your SP.

See this nice Article about it

Example:

CREATE PROCEDURE dbo.MyProcedure
(
    @Param1 INT
)
AS

declare @MyParam1 INT
set @MyParam1 = @Param1

SELECT * FROM dbo.MyTable WHERE ColumnName = @MyParam1 

GO

I copied this information from eggheadcafe.com.

Answer 2

Queries often run faster within SQL Server management studio due to caching of query plans. Try running sp_recompile on your stored procedure before benchmarking. This will clear the query plan.

More details can be found here: http://www.sommarskog.se/query-plan-mysteries.html