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>
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.
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?
Re: How do I speed up this datagridview search?
Quote:
Originally Posted by
Shaggy Hiker
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
Re: How do I speed up this datagridview search?
Quote:
Originally Posted by
dunfiddlin
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
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.
