none
SqlClient.SqlDataAdapter.Update() Performs Very Slowly RRS feed

  • Question

  • I am porting VB.Net 2 code (VS 2005) to VB.Net 4 (VS 2010).  So far things have been going relatively smooth.  However, in testing my new ported code, I came across some strange behavior.

    Using: SQL Server 2005

    In VS 2005 (.Net 2), I have a DataTable filled with data (4386 Records, 4 fields / record).  I then use a DataAdapter to send updates back to the SQL Server, these records are new so the Update() sends SQL INSERT commends.  When I perform the SqlClient.SqlDataAdapter.Update() command, it executes in roughly 4-5 seconds.

    Now the same code, on the same table in VS 2010 (.Net 4) it runs in 1 Minute 17 Seconds (Roughly 18½ times slower).  This is completely unacceptable.  However, I have no clue as to why it is happening.  I have not extended the Update() method at all.  It does not error out, it's just painfully slow.

    Strictly looking at performance, it appears that .Net4's SqlDataAdapter.Update() method runs significantly slower then .Net2's version of the same command. I found that it affected both the INSERT and conversely the DELETE commands sent to the SQL Server (I have yet to check the performance on SQL UPDATE statements). It appears as if each individual SQL Statement sent takes significantly longer to process then it did previously (AKA .Net2).

    If this is indeed the case...If I release this application with this "performance issue" I am sure to have major problem with my customers when they notice the degredation in speed.

    I can't seem to find any information about any differences between .Net2's SqlDataAdapter and .Net4's SqlDataAdapter. I thought maybe there was a Property/Switch that I had to use to "speed up" the communication/performance.  Figuring that maybe .Net4 SqlDataAdapter was geared more towards SQL2008 and that maybe it performs slower against a SQL 2005 Server unless I flip a switch of some sort.  I didn't find any such documentation anywhere though...

    Thursday, June 30, 2011 3:29 PM

All replies

  • Hello,

    Thank you for posting.

    From your post, it looks that your concern was focused on slow-running queries. If I'm correct, I think this question should be broken down 2 parts to analyze, on the sql server side and your local machine.

    Regarding SQL Server side, many factors can cause this issue, not only CPU resources but can also IO storage or bandwidth and so on. I suggest you can check this document for more information about troubleshooting SQL Server 2005. http://msdn.microsoft.com/en-us/library/cc966540.aspx#EDAA

    From your description, it looks you have made this test on your own machine. In another hand, I suggest you can try to test this question on other machines and does it work?

    If you have any finding, please feel free to let me know.

    Best Regards,


    Larcolais Gong[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, July 4, 2011 5:58 AM
  • Larcolais,

    Thank you for your response. The problem is most definitely with Visual Studio (.Net 4), because as I stated, I've run the same code, with the same SQL Statement on a compiled Visual Studio (.Net 2) executable and the speed is unbelievably different. And I have already checked on multiple machines. I even went so far as to have it tested on a slower machine, one with less memory and CPU power, and it was faster than my Quad Core system with double the memory, in-regards to processing the Update() command.

    I am at a loss as to why this is happening. It just seems like a major problem in .Net 4 if indeed it’s the Framework causing the slow-down. It’s hard to believe that a problem this big was allowed to find its way into production.

    -Ben

    Wednesday, July 6, 2011 12:06 PM
  • Hello again,

    Regarding performance issue, I'm afraid it'a a little tough for us because we cannot reproduce your issue on my side. For your reference, I suggest you can try to create a performance routine via VS IDE. Test your application under two different environments (normal one and abnormal one) with the same version of Visual Studio. Please check this document for more steps about performance tool Visual Studio.

    http://msdn.microsoft.com/en-US/library/z9z62c29(v=VS.80).aspx

    http://msdn.microsoft.com/en-US/library/ms182372(v=VS.80).aspx

    Best Regards,


    Larcolais Gong[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.

    • Marked as answer by Larcolais Gong Monday, July 18, 2011 2:52 AM
    • Unmarked as answer by Tekk_Guy Friday, July 29, 2011 5:58 PM
    Thursday, July 7, 2011 6:03 AM
  • Any update? Would you mind letting us know how it goes?

    Please feel free to let us know if you need further support.

    Best Regards,


    Larcolais Gong[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.

    Friday, July 8, 2011 4:38 PM
  • Sorry about the delayed response.  For some reason I am not getting E-Mail notification of postings on this thread.

    There is still no answer to this problem which we are still having.  We are looking into purchasing a MS Support Ticket so I can call MS directly and see if they can help, since apparently I can't seem to get any information regarding this problem online so far.  Getting the support ticket call will take time in my agency though...lots of paperwork, PO#'s need to be created...(AKA Red Tape).

    When/If I find an answer I will definitly post it here.

    ________________________

    Ben Santiago, MCP Certified & A+ Certified
    Programmer Analyst
    (SQL, FoxPro, VB, VB.Net, Java, HTML, ASP, JSP, VBS, Cognos ReportNet)

    Friday, July 29, 2011 6:02 PM
  • Did you find a solution? I am having the same Problems

    Tuesday, February 21, 2012 2:25 PM
  • My apologies.  Yes I did find an answer, but I posted on so many sites I didn't post my answer everywhere.

    After MONTHS working with Miscorosft directly...the answer was so simple, it's laughable.

    If you are a Visual Basic developer in Visual Studio, and have your menu's set in VB mode, there is a DEBUG option that is not visible, but available. That is "Run without Debugger". Apparently Visual Studio 2010, when running an executable with the debugger attached, can severely hamper said exe's performance. (AKA The debugger slows down the performance of the EXE). So, to run WITHOUT debugger in a VB environment, one must press CTRL+F5 on the keyboard, since the option isn't available on the menu structure. This will allow you to run the EXE from within the IDE at full speed.

    Remember, this runs the application WITHOUT the debugger, so you will not be able to trace any errors in this mode.


    ________________________

    Ben Santiago, MCP Certified & A+ Certified
    Programmer Analyst
    (SQL, FoxPro, VB, VB.Net, Java, HTML, ASP, JSP, VBS, Cognos ReportNet)

    Tuesday, February 21, 2012 2:37 PM