Adding Directly To TextBox
Adding To A New AutoCompleteCustomSource
65.08% Improvement
Using LINQ
Only 0.27% Worse With LINQ
Introduction
I wanted to create a simple AutoComplete source which pulled data from a SQL table and displayed the choices in a text box so when the user types, it brings up the possible choices. I got it to work but it was very slow so I loaded up JetBrains dot Trace and found a much faster way of getting this done.
The data I was trying to get was a list of IDs and store names from my Store table. The clients had entered the 4-digit store IDs with leading zeros (0004) but wanted to search by entering the store ID without the leading zeros (4).
Background
JetBrains dotTrace is a great tool for profiling resource hogging functions or variables. It can monitor CPU or memory usage and can give you resource statistics down to a single function in your code or variable in memory. I love it!
Using the Code
I will show you three methods I tried and show you the performance impact of doing this in different ways.
The first thing you will have to do is create your TextBox
control and set the AutoCompleteMode
property. You can select from:
Suggest
- Shows available choices in a drop-down interfaceAppend
- Doesn't show drop-down, instead automatically appends the rest of the first available choice to the text in the box SuggestAppend
- Obviously, it does both...I like this one
Next, set the AutoCompleteSource
property. It supports these sources out of the box:
FileSystem
HistoryList
RecentlyUsedList
AllUrl
AllSystemSources
FileSystemDirectories
CustomSource
ListItems
If you want to know more about these, here is a good article on them. For the purposes of this article, we are going to use CustomSource
.
Now, in your code behind for the form's Load
event, let's create some code to get the list of choices and bind it to the TextBox AutoCompleteCustomSource
s. The first method I tried looks like this:
For Each store As StoreDataSet.StoreRow In StoreDataSet.Store
If store.StoreID.StartsWith("0") Then
StoreIDTextBox.AutoCompleteCustomSource.Add(store.StoreID.TrimStart("0"))
End If
StoreIDTextBox.AutoCompleteCustomSource.Add(store.StoreID)
StoreNameTextBox.AutoCompleteCustomSource.Add(store.StoreName)
Next store
So I've got a DataSet
and I loop through each row in the Store
table. For each row, I add the store ID and name to their respective TextBox AutoCompleteCustomSource
s. Also, if the StoreID
starts with a "0
", I trim the zeros from it and add that as well (so users gets the auto completion whether they type the leading zeros or not). Pretty simple, except it was SLOW!
So I loaded up the JetBrainz dotTrace to see what was causing the problem. I found that the AutoCompleteStringCollection.Add
method had fired 1080 times taking 20 seconds to complete and 65.34%of the form load time! For some reason, the .NET folks made this method quite expensive.
Next I found that it was MUCH faster to create new AutoCompleteStringCollection
s, add the store IDs and names to those, and then assign them as the AutoCompleteCustomSource
s for the textbox
es instead of adding them directly:
Dim storeIDList As New AutoCompleteStringCollection()
Dim storeNameList As New AutoCompleteStringCollection()
For Each store As StoreDataSet.StoreRow In StoreDataSet.Store
If store.StoreID.StartsWith("0") Then
storeIDList.Add(store.StoreID.TrimStart("0"))
End If
storeIDList.Add(store.StoreID)
storeNameList.Add(store.StoreName)
Next store
StoreIDTextBox.AutoCompleteCustomSource = storeIDList
StoreNameTextBox.AutoCompleteCustomSource = storeNameList
I noticed right away that the form loaded very quickly now. I did another trace and found that this code is a 65.08% improvement!
I did a bit more tinkering with using LINQ instead of writing the loop code myself and was able to do this with 1 line of code (per textbox
):
StoreIDTextBox.AutoCompleteCustomSource.AddRange_
((From row In StoreDataSet.Store Select row.StoreID).ToArray)
StoreIDTextBox.AutoCompleteCustomSource.AddRange_
((From row In StoreDataSet.Store Where row.StoreID.StartsWith("0") _
Select row.StoreID.TrimStart("0"c)).ToArray)
StoreNameTextBox.AutoCompleteCustomSource.AddRange_
((From row In StoreDataSet.Store Select row.StoreName).ToArray)
The LINQ query gets all of the StoreID
s, converts them to an array, and uses the AddRange
method to add them all to the AutoCompleteCustomSource
in one call. The second line just adds a Where
clause to get only the StoreID
s with zeros, trim the zeros, and add those as well. The third line does the same thing for store names.
I suspected that the LINQ query would be less efficient since it is basically doing the same thing I did but wrapped inside of a LINQ query. I ran another trace and found it was only .27% worse performing than the second example. I can live with this since I think it's more readable (and elegant).
Points of Interest
I think JetBrainz has a really GREAT product in dotTrace and it really helped me out in this case. LINQ is pretty dang sweet too.
History
- 12-21-09 - First revision
I'm a .NET developer, fluent in C# and VB.NET with a focus on SharePoint and experience in WinForms, WPF, Silverlight, ASP.NET, SQL Server. My roots come from a support/system administrator role so I know my way around a server room as well.
I have a passion for technology and I love what I do.