[RESOLVED] DataTable Adapter update very slow-VBForums
Results 1 to 9 of 9

Thread: [RESOLVED] DataTable Adapter update very slow

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    615

    Resolved [RESOLVED] DataTable Adapter update very slow

    I have an ADO DataSet with a table of a few hundred rows. I created a DataGridView with some custom code to prevent the user from selecting an invalid combination of values. It worked great and then I decided I would move the selections column to the SQL Db for use elsewhere ion the program. VS is amazing how the wizards set everything up for me in normal circumstances. I don't know how to do all this myself and my ignorance is causing me fits. I struggled though it all today making some other examples and analyzing what all the wizards do and created a setup that works now that meshes with my old code. But now when I use the TableAdapter Update method against the table it takes about 30 seconds to push the data back to the SQL server. The SQL server is over the internet but I have a fast and reliable connection. In most case only about a dozen rows change. But as I'm looking at the progress from SSMS it seems the method is updating all the rows in the table regardless of whether they have changed or not. The MSDN says it will only execute changes on rows which have been modified. I followed the instructions on this page. Is this behavior and performance normal? Am I doing something wrong?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,547

    Re: DataTable Adapter update very slow

    The first thing to do would be to confirm the RowState of each DataRow. Make sure that the rows you expect to have changed have a value of Modified and the others are Unchanged.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    615

    Re: DataTable Adapter update very slow

    Sensible. I don't know how to make that determination however. I'll research that now. But if you can tell me how to check the row states I'd appreciate it. I looked at the DataTabble in the debugger but it only shows me the normal columns.

  4. #4

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    615

    Re: DataTable Adapter update very slow

    Learned it. Without doing anything in the DGV all 207 rows have the RowState of Modified. Any ideas how this could happen? I'll start sleuthing to see if I can figure out where this is happening.

  5. #5

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    615

    Re: DataTable Adapter update very slow

    This is bizarre. I added debug lines to each line of this method and every time the count of modified rows is zero until "cbVendor.SelectedIndex = 0". What the heck? How does changing a ComboBox modify all the rows of an disassociated DataTable?
    vn.net Code:
    1. Private Sub frmJdxSelection_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    2.     JurisdictionsTableAdapter.Fill(DsMain.Jurisdictions)
    3.     Dim strVendors As New List(Of String)
    4.     For Each rowTemp As dsMain.JurisdictionsRow In DsMain.Jurisdictions
    5.         If Not strVendors.Contains(rowTemp.Vendor) Then
    6.             strVendors.Add(rowTemp.Vendor)
    7.         End If
    8.     Next
    9.     strVendors.Sort()
    10.     strVendors.Insert(0, "All")
    11.     cbVendor.Items.AddRange(strVendors.ToArray)
    12.     cbVendor.SelectedIndex = 0
    13.     blnLoaded = True
    14. End Sub

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,547

    Re: DataTable Adapter update very slow

    Do you have a handler for the SelectedIndexChanged event of that ComboBox?

    By the way, you can simplify that code like this:
    vb.net Code:
    1. Dim distinctVendors = DsMain.Jurisdictions.Select(Function(row) row.Vendor).Distinct()
    2.  
    3. cbVendor.DataSource = {"All"}.Concat(distinctVendors.OrderBy(Function(s) s)).ToArray()
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    615

    Re: DataTable Adapter update very slow

    Oh cool! Nice code. I had something like that but I didn't have the "All". Good show.

    I do have a handler for the SelectedIndexChanged.

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,547

    Re: DataTable Adapter update very slow

    Quote Originally Posted by cory_jackson View Post
    I do have a handler for the SelectedIndexChanged.
    So what's in it? I suspect that that is where your RowStates are being changed.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    615

    Re: DataTable Adapter update very slow

    You're a genius. I had a loop to clear any selections but it wasn't conditional so it was setting each to false. Thank you very much.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.