Performance of autocomplete

[pettrer]

Hi,

I have a textbox that is filled using autocomplete. The autocomplete is a globally accessible sub that I use for textboxes and (I guess) comboboxes.

My problem is that the autocomplete sub takes several seconds to fill the textbox. Is there some way my code could be improved? (The code below takes three seconds to fill for 288 autocomplete entries, which I don't think should be a lot for this kind of functionality.)

My best bet is that the autosorcecollection has to redraw itself every time an entry is added, and that causes the delay, but I'm not sure.

Apart from taking time I think this global function is really handy, so feel free to use!

Thanks a lot for suggestions,
/Pettrer

dt = PoulateDT("SELECT cCode, cText, ID FROM MyTable WHERE inUse=1 ORDER BY cCode")
FillAutocomplete(txtSearchBox, dt, "cCode")

Public Sub FillAutocomplete(ByVal MyControl As Object, ByVal dt As DataTable, ByVal ColName As String)

Dim str As String = Now().ToString
MyControl.AutoCompleteMode = AutoCompleteMode.SuggestAppend
MyControl.AutoCompleteSource = AutoCompleteSource.CustomSource
Dim autoSourceCollection As New AutoCompleteStringCollection()
MyControl.AutoCompleteCustomSource = autoSourceCollection
Dim drow As DataRow
For Each drow In dt.Rows
autoSourceCollection.Add(drow.Item(ColName))
Next
str &= " " & Now.ToString
End Sub

 

[Tom]

If you already have filled a datatable with your data, why are you adding the items one at a time to the combobox instead of just binding the table?

 

[pettrer]

If you already have filled a datatable with your data, why are you adding the items one at a time to the combobox instead of just binding the table?

Hi Tom,

Thanks for replying. I tried to do it some other way but it doesn't work. I saw this in another forum:

You set the AutoCompleteSource to Custom and then you have to add and remove items directly to the AutoCompleteCustomSource, which is itself a collection. You're going to have to manually get the data out of the column of your DataTable and manually add each value to the collection. You'll then need to handle the appropriate events of the DataTable to detect when a row is added, editied or deleted and then manually update the auto-complete collection.

I've tried

MyControl.autocompletesource = dt
autoSourceCollection.AddRange(dt.Rows)
MyControl.AutoCompleteCustomSource = dt
MyControl.AutoCompleteCustomSource = dt(0)(ColName)

but all of them crash. The last two pass the build command but yield the error "Method invocation failed because 'Public Property AutoCompleteCustomSource() As System.Windows.Forms.AutoCompleteStringCollection' cannot be called with these arguments: Argument matching parameter 'value' cannot convert from 'String' to 'AutoCompleteStringCollection'."

Please help me write this line if you know how to.

Warm regards

Pettrer

 

[Tom]

This is assumming that your PopulateData function works and fills your datatabe...

dt = PoulateDT("SELECT cCode, cText, ID FROM MyTable WHERE inUse=1 ORDER BY cCode")
Combobox1.DataSource = dt               'The name of your dataset/datatable
ComboBox1.DisplayMember = "cText"    'The name of the field in your table you want to display
Combobox1.ValueMember = "ID"           'Hidden value associated with each item in combo

 

[pettrer]

Tom,

Thanks, but what I have is a textbox, not a combobox (sorry I didn't spot that in your previous reply). Thus, the control itself is not bound to an entire datatable column, but needs to get its values from the autocomplete function (the Populate thing I wrote).

Is it impossible then to speed things up more for me?

Warm regards,
Pettrer

 

[Tom]

ok I seem to be way off base here....

Ok can you narrow down exactly what is taking the 3 seconds to load (is it the query or the loop etc?)? And when is this event fired that its loading this list?

 

[pettrer]

ok I seem to be way off base here....

Ok can you narrow down exactly what is taking the 3 seconds to load (is it the query or the loop etc?)? And when is this event fired that its loading this list?

Ok, here's another try...

The call to the code below is from the forms Load event.

Dim str As String = Now.Second & ":" & Now.Millisecond 'Test

MyControl.AutoCompleteMode = AutoCompleteMode.SuggestAppend
MyControl.AutoCompleteSource = AutoCompleteSource.CustomSource
Dim autoSourceCollection As New AutoCompleteStringCollection()
MyControl.AutoCompleteCustomSource = autoSourceCollection

str &= "               " & Now.Second & ":" & Now.Millisecond 'Test

Dim drow As DataRow
For Each drow In dt.Rows
autoSourceCollection.Add(drow.Item(ColName))
Next

str &= "               " & Now.Second & ":" & Now.Millisecond 'Test

End Sub

After the lines above, str is "2:182 2:182 4:478".

So as you see, the for loop is the culprit here.

Thanks again,

Pettrer

 

[Tom]

If you assign your sourcecollection to the control after it is filled does it make a difference?

Dim str As String = Now.Second & ":" & Now.Millisecond 'Test

Dim autoSourceCollection As New AutoCompleteStringCollection()
Dim drow As DataRow

str &= "               " & Now.Second & ":" & Now.Millisecond 'Test

For Each drow In dt.Rows
    autoSourceCollection.Add(drow.Item(ColName))
Next

str &= "               " & Now.Second & ":" & Now.Millisecond 'Test

MyControl.AutoCompleteMode = AutoCompleteMode.SuggestAppend
MyControl.AutoCompleteSource = AutoCompleteSource.CustomSource
MyControl.AutoCompleteCustomSource = autoSourceCollection

 

[pettrer]

If you assign your sourcecollection to the control after it is filled does it make a difference?

YES!!!!

That did the trick!

Now, instead of 2.5 seconds, it took 0.05 seconds for the same operation!

Thanks a lot and have a really nice weekend,

Pettrer