ADO.NET query execution much slower than SQL Management Studio

D

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
 
P

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.
 
Joined
Jun 18, 2010
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top