none
SQLDataReader.ExecuteReader() extremely slow in CLR debugging RRS feed

  • Question

  • Good Day,

    I have a simple query which take < 1 sec to run in SQL management studio (~5000 records 3 columns), yet when the same query is run by a SQLDataReader's ExecuteReader() method while debugging my CLR stored Procedure it takes 80 sec to 600 seconds.

    Slowing down debugging drastically as you may imagine.

    I added a stopwatch around the Execute.Reader() command and ran it in release (through SQL managemetn studio (outputing the stopwatch timer's elapsed time to sql management studio ... 1 sec)

    after some research I tried

    SET ARITHABORT ON in the query.. with no gain.

    Any assistance would be greatly appreciated.

     

    Friday, November 4, 2011 11:00 PM

Answers

  • It turns out that it's to do with "Application Debugging" and "Allow SQL/CLR Debugging" options of the Data Connection in my IDE.

    By Turning off "Application Debugging" and toggling Allow SQL/CLR Debugging off then back on it seems to fix the issue. Once and a while after a Visual Stuidio Crash it seems to need me to toggle the SQL/CLR Debugging off then back on again like it's resetting something. I can't explain why this helps... it does however solve my problem in a workaround sort of fashion.

    • Marked as answer by Scott Gall Monday, November 7, 2011 1:04 PM
    Monday, November 7, 2011 1:04 PM

All replies

  • Hi Scott,

    Welcome!

    Are your code looks like:

    SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;
    
    cmd.CommandText = "StoredProcedureName";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = sqlConnection1;
    
    sqlConnection1.Open();
    
    reader = cmd.ExecuteReader();
    // Data is accessible through the DataReader object here.
    
    sqlConnection1.Close();
    

    You can use SQL Profiler tool in SQL Server to watch where cause the performance hit.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, November 7, 2011 5:42 AM
    Moderator
  • It turns out that it's to do with "Application Debugging" and "Allow SQL/CLR Debugging" options of the Data Connection in my IDE.

    By Turning off "Application Debugging" and toggling Allow SQL/CLR Debugging off then back on it seems to fix the issue. Once and a while after a Visual Stuidio Crash it seems to need me to toggle the SQL/CLR Debugging off then back on again like it's resetting something. I can't explain why this helps... it does however solve my problem in a workaround sort of fashion.

    • Marked as answer by Scott Gall Monday, November 7, 2011 1:04 PM
    Monday, November 7, 2011 1:04 PM
  • how do you turn off application debugging?
    Saturday, October 5, 2013 2:07 AM
  • Try this: http://msdn.microsoft.com/en-us/library/ms165039(v=vs.90).aspx If you are using 2010/2012 with data tools it has been moved to a new menu: SQL Server Object Explorer
    Saturday, October 5, 2013 10:58 AM