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.

enter image description here

Are you saying that the SQL command string is 1600 lines, or that it returns 1600 rows? — RBarryYoung, Feb 26 '13 at 20:37
Is it plain SQL Text sent from C# code, or Stored Procedure saved in SQL Server and just invoked using ADO.NET? — MarcinJuraszek, Feb 26 '13 at 20:37
You could try running the SQL Server Profiler to see how quickly the query makes it to the server, and then just how long it actually takes to execute. — mellamokb, Feb 26 '13 at 20:37
@RBarryYoung: It is 1600 lines but most of this is because of enumerating the column names. The actual logic itself is simple. — Legend, Feb 26 '13 at 20:38
Why would you send 1600 lines of SQL code instead of having a stored procedure and invoking it? — istepaniuk, Feb 26 '13 at 20:40
@istepaniuk: You're right. This is in a test environment and I'll convert this into a SP later. — Legend, Feb 26 '13 at 20:42
So maybe you should skip performance testing for later as well? ... What is the point of testing a thing, that's going to be changed in a while? — MarcinJuraszek, Feb 26 '13 at 20:43
@Legend That's a pretty freakin big command string to be pushing to the server every time you loop. And if most of those lines are column names, I can tell you that 1000+ columns is one heckuva lot of columns to be returning to the client. That's way outside of the normal bounds. — RBarryYoung, Feb 26 '13 at 20:43
Ok; I guess I'll spend some time creating an SP on the server. I am thinking this is not the actual problem but I'll make the change anyways. Thank you for your suggestions. — Legend, Feb 26 '13 at 20:45
I made all the changes suggested and ran SQL profiler. Updated my question with details. Observe how the .NET Client takes > 18 seconds whereas SSMS takes only 3 seconds. — Legend, Feb 26 '13 at 22:35
possible duplicate http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-st — devio, Feb 26 '13 at 22:56

score 8 · Accepted Answer · edited May 23 '17 at 11:52

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.

score 1 · Answer 2 · answered Feb 26 '13 at 23:21

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

score 0 · Answer 3 · answered Feb 26 '13 at 20:44

Why don't U use XML instead of a Result Set ? As far as I know using XML is much faster than Reading a result set. so in this case you should use something like this :

SELECT * 
FROM [Table Name]
FOR XML PATH('[Your Path]'), ELEMENTS XSINIL, ROOT('Your Root')

and after that I think you can serialize it in your Project.

Elian Ebbing · Answer 4 · 2013-02-26T23:46:32.773

I had the same problem once, and it turned out that the difference was caused by the ARITHABORT setting that is different if you connect via SQL management studio. The .net connection set the ARITHABORT setting to OFF, while SQL management studio sets this setting to ON.

You can try if you have the same problem by executing SET ARITHABORT OFF in the SQL Management Studio, and then execute your query.

Here is a thread that explains why this setting can cause such dramatic performance differences.

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

4 Answers4