ADO.NET query execution much slower than SQL Management Studio

Discussion in 'ASP .Net' started by dustbort, Feb 22, 2008.

  1. dustbort

    dustbort Guest

    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
     
    dustbort, Feb 22, 2008
    #1
    1. Advertising

  2. 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?
    >
    > Thanks,
    > Dustin
    >
    >
    >
     
    Peter Bromberg [C# MVP], Feb 22, 2008
    #2
    1. Advertising

  3. dustbort

    Paul Shapiro Guest

    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]" <> wrote in message
    news:...
    > 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?
     
    Paul Shapiro, Feb 23, 2008
    #3
  4. dustbort

    JosipJaic

    Joined:
    Jun 18, 2010
    Messages:
    1
    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
     
    JosipJaic, Jun 18, 2010
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark
    Replies:
    20
    Views:
    1,654
    Dave O'Hearn
    Dec 27, 2004
  2. Replies:
    15
    Views:
    521
    Roy Harvey
    Oct 26, 2006
  3. Replies:
    27
    Views:
    540
    Gabriel Genellina
    Jun 14, 2007
  4. Tim Meagher
    Replies:
    3
    Views:
    104
    Ray Costanzo [MVP]
    Oct 4, 2005
  5. Tom
    Replies:
    2
    Views:
    117
    The Natural Philosopher
    Jan 16, 2009
Loading...

Share This Page