Results 1 to 6 of 6

Thread: How do I speed up this datagridview search?

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2011
    Posts
    11

    How do I speed up this datagridview search?

    I'm trying to speed up a datagridview search that I'm using.

    First I populates two different datagridviews (dgvVerktyg and dgvSökArtikelnummer) with a normal OleDbConnection
    Then I do a search throuh both datagridviews to find a matching pair and add the reult to another datagridview (dgvResultat)

    The problem is that the search takes about 60 sec to complete and it's to long for my clients

    Is there any faster and easier way to do this?

    <code>
    For e As Integer = 0 To dgvSökArtikelnummer.Rows.Count - 1
    If Not IsDBNull(dgvSökArtikelnummer.Item(1, e).Value) = True Then
    'MsgBox(dgvSökArtikelnummer.Item(1, e).Value)

    For a As Integer = 0 To dgvVerktyg.Rows.Count - 1
    If Not IsDBNull(dgvVerktyg.Item(0, a).Value) = True Then
    If Trim(dgvVerktyg.Item(0, a).Value).ToUpper = Trim(dgvSökArtikelnummer.Item(0, e).Value).ToUpper Then
    dgvResultat.Rows.Add(dgvSökArtikelnummer.Item(0, e).Value, _
    dgvVerktyg.Item(0, a).Value _
    )

    End If
    End If

    Next

    End If
    Next
    </code>

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: How do I speed up this datagridview search?

    Are these datagridviews just showing data from a datatable? That's the most common situation, though it isn't the only possible situation.

    There are a couple points:

    1) All that .ToUpper, and Trim, is going to be slow. They aren't slow for just a few calls, but you are doing them over and over and over...squared. Strings are immutable, so each one of those returns a new string object. In otherwords, first you get the value, then you get a new string that is the value in all upper case, then you get a new string that is the value in all upper case trimmed. You do that for each element in the inner row once for each element in the outer row. That's a total waste. Do all the conversions for each grid one time, and then you can get rid of the .ToUpper and Trim calls.

    2) If the DGV is showing a datatable, then you can use the Datatable.Select method to get the values. This may not be actually faster than the dual loop, though, so you'd have to test it. It also requires that the items in the fields be in trimmed and cased correctly.

    3) Build an index. If you know which field will be compared, then you can perform the search ahead of time, build an index of which rows match each item, and not need to deal with it later. This is unlikely to be useful unless you know exactly which field will be compared, but it's something to think about.
    My usual boring signature: Nothing

  3. #3
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: How do I speed up this datagridview search?

    I'm finding it difficult to understand the value of this procedure and there's nothing quicker than not doing it at all. If I read you correctly, the result is ..

    DGV1 DGV2 DGV3
    Fred Wilma Barney | Barney
    Betty Barney
    Barney Pebbles


    What now?
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2011
    Posts
    11

    Re: How do I speed up this datagridview search?

    Quote Originally Posted by Shaggy Hiker View Post
    Are these datagridviews just showing data from a datatable? That's the most common situation, though it isn't the only possible situation.

    There are a couple points:

    1) All that .ToUpper, and Trim, is going to be slow. They aren't slow for just a few calls, but you are doing them over and over and over...squared. Strings are immutable, so each one of those returns a new string object. In otherwords, first you get the value, then you get a new string that is the value in all upper case, then you get a new string that is the value in all upper case trimmed. You do that for each element in the inner row once for each element in the outer row. That's a total waste. Do all the conversions for each grid one time, and then you can get rid of the .ToUpper and Trim calls.

    2) If the DGV is showing a datatable, then you can use the Datatable.Select method to get the values. This may not be actually faster than the dual loop, though, so you'd have to test it. It also requires that the items in the fields be in trimmed and cased correctly.

    3) Build an index. If you know which field will be compared, then you can perform the search ahead of time, build an index of which rows match each item, and not need to deal with it later. This is unlikely to be useful unless you know exactly which field will be compared, but it's something to think about.
    Hello Shaggy Hiker.

    Tanks for the quick respond.

    1) I removed the trim and ToUpper but it still takes about 50 sec to perform the operation (now it's 10 sec faster)

    2) The dgv is populated from a datatable, can you please show me how you mean with the select method (I have never use that kind of method before)

    My code to populate the datatabel is this (I use the same method for both datatables, but with different dataset, datatable and oledbadapter):

    Code:
    Dim ds As New DataSet
    Dim dt As New DataTable 
    Dim da As New OleDbDataAdapter
    
    connDataBas.Open
    
    ds.Tables.Add(dt)
            da = New OleDbDataAdapter("select Ritningsnr, Benämning, Orgformat, UppdatDat from Verktyg", connDataBas)
            Try
                da.Fill(dt)
                dgvVerktyg.DataSource = dt
            Catch ex As System.Exception
                MsgBox("Det blev något fel vid laddning av data" & vbNewLine & ex.Message)
                StängKomunikationMedDataBas()
                Exit Sub
            End Try
    
    connDataBas.Close

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2011
    Posts
    11

    Re: How do I speed up this datagridview search?

    Quote Originally Posted by dunfiddlin View Post
    I'm finding it difficult to understand the value of this procedure and there's nothing quicker than not doing it at all. If I read you correctly, the result is ..

    DGV1 DGV2 DGV3
    Fred Wilma Barney | Barney
    Betty Barney
    Barney Pebbles


    What now?
    Hello Dunfiddlin.

    In the first datagridview (dgvSökArtikelnummer) I have this data:

    abc 123
    def 456
    ghi 789

    And in the other datagridview (dgvVerktyg) I have:
    123 New York
    456 London
    789 Dublin

    And the result I'm looking for is:
    abc 123 New York
    def 456 London
    ghi 789 Dublin

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: How do I speed up this datagridview search?

    How real is that example? If the value you are comparing is actually a number, that would be great. Comparing on strings is always going to be slower than comparing numbers.

    Roughly speaking, you might try something like this for the selection:


    Code:
    Dim dRows as Datarow()
    Dim dgv2Source As Datatable = dgvVerktyg.DataSource
    
    For e As Integer = 0 To dgvSökArtikelnummer.Rows.Count - 1
     dRows = dgv2Source.Datatable.Select("ColumnName = '" & e("ColumnName").ToString & '")
     If dRows.Count > 0
      'Found some
     End If
    Next
    The Select method is kind of like a WHERE statement from a SQL clause, but operates against a datatable. It will return all rows that match the search criteria. One point to note is that the search criteria is wrapped in single quotes, regardless of whether it is a string or a number. This is a cleaner way to write the code, but I have some doubts as to whether or not it will be any faster at all. The Select method is likely doing almost exactly what you were doing with your code, and may do it more slowly.

    There would also be a way to do this using LINQ and Joining the two tables. Normally, if somebody is looking for a faster way to get something done, I wouldn't recommend LINQ, as it is usually slower, in my experience. However, a LINQ join may prove faster. It's not something I write very often, and I'm a bit down on LINQ, so somebody else would have to write that. Even if the LINQ proved faster, it would be only a few seconds, nothing more. The same can be said for Select, as there won't be enough gain to make a difference.

    Ultimately, to get much better speed you will have to change your algorithm. Exactly how to do this I can't say. Perhaps you can run a third query that returns a Datareader (faster than a datatable) which covers the cases where the two join up. This won't work if the data displayed in the datagrid has changed from what is in the database at the time that you want to run this loop. Alternatively, you might run a background thread that found all the matches and built the strings while the user is looking at the data. Naturally, this would only work if you can guess which field needs to match up, and the user will spend a couple seconds looking at the data. Thinking about the problem in a different way is the only path to much greater speed than what you currently have, though.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width