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.
"Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in message
news:A6ABED66-2158-401A-98DA-(E-Mail Removed)...
> 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
>
>
> "dustbort" wrote:
>
>> 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?
Similar Threads
