ADO.NET query execution much slower than SQL Management Studio

[dustbort]

I have a stored procedure that when called from SQL 2005 Management Studio
takes less than one second to execute. The same SP, when called from .NET
code takes about 13 seconds. I am using a SqlCommand object with
CommandType set to StoredProcedure and I am passing arguments through the
parameters collection. I have tried using a SqlDataReader and a DataAdapter
to retrieve the data, but both are equally slow. From stepping thru the
debugger, I know that the specific statement that takes a long time to
execute is either reader.ExecuteReader() or dataAdapter.Fill(dataTable),
equivalently depending on the method I tried. I did a trace in the
Profiler, and got nearly identical result for either method of .NET
SqlClient Data Provider. Here is an example:

EventClass: RPC:Completed
CPU: 13390
Reads: 559475
Writes: 0
Duration: 13496
Binary Data: (a long hex value)

When I copied the TextData (SQL Statement being executed) from Profiler into
SQL Management Studio, I get the following trace:

EventClass: SQL:BatchCompleted
CPU: 437
Reads: 9998
Writes: 0
Duration: 440
BinaryData: (empty)

(Immediately prior to this there is a corresponding SQL:BatchStarting trace,
with empty CPU, Reads, Writes, and Duration columns.)

What could explain the orders of magnitude difference in reads and duration?
Is the problem due to RPC? What about the binary data? I have tried using
the overload of ExecuteReader(CommandBehavior.SingleResult) with no
improvement. What can I try to improve it?

Thanks,
Dustin

 

[Peter Bromberg [C# MVP]]

Hate to say this, but it doesn't compute. Look carefully for a bug in your
code, connections being closed properly, etc.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: htp://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[Paul Shapiro]

If you first ran the procedure from your .net code, and then ran it from SQL
Management Studio, it's possible the data and/or stored procedure execution
plan were cached from the first run in .net. Try clearing the caches before
running in SQL Management, or try executing the same procedure twice in a
row from .net.
SQL commands to clear procedure and data caches:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

If you still have the issue, make sure sql profiler is showing enough detail
so you can see how the data is being moved in the two cases.

 

[JosipJaic]

had same probelm

I had same problem.
Store procedure executes in 1 seconds.
and when executed in c# (Executereader) it takes 30 seconds.

Fix was to remove SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
from store procedure body...

If this is not case in your problem. Try removing any transaction in procedure,
and check for performances