ComboBox Population Speed Issue

[chapran]

I'm trying to populate the ComboBox control using this code:

cndTemp = New SqlCommand
cndTemp.Connection = objConn
cndTemp.CommandType = CommandType.Text
cndTemp.CommandText = "SET NOCOUNT ON; Select PartialID, PartialNumber FROM dbo.[Partial] ORDER BY PartialNumber"

daTemp.SelectCommand = cndTemp
dtFindPartial = New DataTable
daTemp.Fill(dtFindPartial)
Me.ComboBox1.DataSource = dtFindPartial
Me.ComboBox1.DisplayMember = "PartialNumber"
Me.ComboBox1.ValueMember = "PartialID"

Green lines take just portion of second, but orange one take 4 sec, and the red one 5 sec.
If I change the order of assigning DisplayMember and ValueMember then it becomes even worse - ValueMember line takes 5 sec, and DisplayMember line takes 6 sec, all togrther about 15 sec.

The same approach in VB6 doesn't take any time.
The number of records is about 60 thousand. But I cannot understand what I'm doing wrong.
I cannot add just small number of records in the ComboBox list, because it is going to be used in search, so all records must be presented in the list.
I met this problem with Infragistics Ultra Combo first and decided that it is the problem with Infragistics. I replaced the Ultra combo with the standard Microsoft control and still have the same problem.

Thank you

[Leary222]

Dev express user controls boast fast data speeds if you were interested as for why it takes so long other than the number of records I cannot see a reason

[jayinthe813]

Try setting the data source last. Instead of this:

Me.ComboBox1.DataSource = dtFindPartial
Me.ComboBox1.DisplayMember = "PartialNumber"
Me.ComboBox1.ValueMember = "PartialID"

Do This:

Me.ComboBox1.DisplayMember = "PartialNumber"
Me.ComboBox1.ValueMember = "PartialID"
Me.ComboBox1.DataSource = dtFindPartial

If that doesn't help, what event are you using to trigger this code to run?

[wes4dbt]

I don't see why you would want to put 60,000 items in a comboxbox and then use that for doing your searches. Use your database or datatable for searches. They have methods for that purpose.

[Shaggy Hiker]

I totally missed the bit about 60,000 records. Now that I have seen it, I thoroughly agree with wes4dbt. Controls like comboboxes, listboxes, listviews, and DGVs take a fair amount of time to set up because they have a visual interface and all that goes with it. You pay for that with setup time, and the more rows you add, the more you pay. There are things you can do to improve efficiency, but in this case, why bother? Since nobody would ever be willing to scroll through 60,000 items, there is no good reason to waste the overhead time of dealing with the control. Leave the data in a datatable, as you already have it, then use controls only to show that rational subset of the total set which the user would want to see at any given time. They certainly don't want to see even one thousand items, let alone sixty thousand.

[chapran]

Let's say the user need to search for the value '1001-0256-1-A-01' or '1001-0256-1-A-02' or '1001-0256-1-B-02' (any of them). In my system this is an entity representing one part of the Order, called Partial. When the combo is populated with all existing Partials and the user starts to type 1, the combobox drops down and brings the Partial which starts with '1'. It may be, let's say '1001-0012-1-B-01' and several others containing '1' at the beginning. The user has 2 options - first, continue to type to bring the searched value or the second option to scroll down several items to select the needed. He also can see all similar values, which is important.
This scenario works in VB6 just perfect.
I would ask you to give me the sample code to avoid population of the combo as you suggest with all 60000 items.

Thank you

[chapran]

Try setting the data source last. Instead of this:

Me.ComboBox1.DataSource = dtFindPartial
Me.ComboBox1.DisplayMember = "PartialNumber"
Me.ComboBox1.ValueMember = "PartialID"

Do This:

Me.ComboBox1.DisplayMember = "PartialNumber"
Me.ComboBox1.ValueMember = "PartialID"
Me.ComboBox1.DataSource = dtFindPartial

If that doesn't help, what event are you using to trigger this code to run?

This change adjustment of the code doesn't help.
Anyway thank you very much.

[jayinthe813]

This change adjustment of the code doesn't help.
Anyway thank you very much.

Well it should have changed the load time somewhat since its only sorted once, but maybe not what you are expecting. Try what Wes/SH suggested: Fill the data table, then pull only what you need and add it to the combobox at any given time.

[chapran]

Thank you, but unfortunately I do not know how.

[jayinthe813]

This change adjustment of the code doesn't help.
Anyway thank you very much.

Well it should have changed the load time somewhat since its only sorted once, but maybe not what you are expecting. Try what Wes/SH suggested: Fill the data table, then pull only what you need and add it to the combobox at any given time. Also, are you handling the "SelectedIndexChanged" Event of that combobox?

[chapran]

I'm writing the code step by step. I did not come yet to the code in Combo events. Later I will need to write search procedure for SelectedIndexChanged event.

[Shaggy Hiker]

The datatable has a DefaultView, which is a DataView. You can make your own DataViews, too, in addition to the default, but there doesn't seem to be a reason to bother. Dataviews have a .RowFilter property which takes what looks largely like SQL, and will filter the rows in the DataView. A DataView can be bound to a control the same way that a datatable can be. Therefore, rather than binding the datatable, you could bind datatable.DefaultView, and it would work the same. It would also have the same problem, and might even be a bit worse.

I was thinking about how I did a similar thing, but I didn't have 60,000 items, so I guess it isn't quite comparable. I had a good number. The table may have held a few hundred thousand rows, but the query I was running returned only a distinct subset of those, which was probably only in the thousand range. For me, autocomplete worked just fine, which may be due to the smaller number. What I was doing with the DataView was actually filling a DGV based on the selection made in the combobox. So if the user selected Test Creek, then the DataView was filtered on that name and the set of results was what was displayed in the DGV. That's not really the same issue.

The issue you have is that until the user starts typing, you haven't narrowed things down at all. What you could do is filter the DataView on something like 1 = 0, which will return 0 rows. As the user starts typing, you will be able to build a reasonable filter that would greatly restrict the total amount of data. The drawback to that approach would be that the user would initially see nothing. Therefore, if they knew they wanted one of the first few items, or if they were a total masochist, and wanted to pick from the list without any typing, there would be nothing to pick from.

By the way, what happens if you leave the ValueMember line out entirely? You don't really need it anyways, as you can get the same information almost as easily without it.

[Leary222]

not sure how slow it would be, but would either using a text box with the datasource bineded as the autocomplete source be faster?

alternatively updating the data source each time the user changes the text in the combo box by selecting LIKE combobox.value? this would limit the amount of rows retrieved and binded?

[chapran]

If I exclude ValueMember assignment it even doesn't decrease the total number of second spent on the entire process.
As I already said the version written in VB6 works perfect within many years. There are about 300 users of that application. Regular user hates to relearn to do some things which she used to do within years. My boss doesn't want any change in the interface. So, I have not enough freedom, but the most important fact in that I do not have enough knowledge.

Thank you

[chapran]

I always hear that VB.Net is faster than VB6, but unfortunately in many cases where VB6 code doesn't bring any issue, VB.Net does bring. Why the same stored procedure with regular VB6 combo brings data within portion of second while VB.Net takes 10-15 seconds. And then I hear that I use VB.Net wrong way. I agree that I do something wrong almost always. But why similar approaches work with no problem in VB6.
Yes, I understand that they are different languages, but...

[chapran]

not sure how slow it would be, but would either using a text box with the datasource bineded as the autocomplete source be faster?

alternatively updating the data source each time the user changes the text in the combo box by selecting LIKE combobox.value? this would limit the amount of rows retrieved and binded?

I'm also restricted with changing existing stored procedures or adding new ones. It doesn't mean that I cannot do that at all, but my boss afraid that some change in the existing SP may break something in VB6 version, writing duplicate SPs dedicated for VB.Net version is a bad idea from his point of view because in case of the need to change anything in buisness rules may require rewriting 2 SPs instead of one.

[Leary222]

have you had a look at the devexpress controls? if they live up to expectations your 60k records should be loaded in miliseconds


https://www.devexpress.com/Products/...Forms/Editors/

[Shaggy Hiker]

They ARE different languages. They do have a superficial similarity in that they use the same syntax, but otherwise they are VERY different languages. When it comes to database work, the difference is greater still. On the other hand, it always was greater. There were at least three different DB technologies available in VB6 (DAO, RDO, and ADO), all of which had different performance. DAO is the oldest, but it was also the fastest by a fair amount. Now, life is different.

I dislike comboboxes, but you might see whether or not there is a begin layout and end layout. Adding items to a listbox can take a long time because every item causes a paint. You can turn that off while populating the listbox. Perhaps something similar exists for the combobox.

One thing I'm curious about is how you determined where the slowdown is coming from?

[chapran]

In debug mode I found out that the PopulateFindPartialCombo procedure takes a lot of time.
At the beginning of the PopulateFindPartialCombo procedure I wrote
strDatBegin = TimeOfDay.ToString
At the end:
strDatEnd = TimeOfDay.ToString
MessageBox.Show(strDatBegin & ", " & strDatEnd)
When I ran I've got the result 10-14 sec.
I started to move
strDatBegin = TimeOfDay.ToString
line by line and found out what the line takes

Everybody critiseized my approach to do Search, but although I asked to help with sample code, nobody gave anything.
I know that I have to learn, learn, learn. But for the current moment it is not solution.


Thank you

[Shaggy Hiker]

Well, it sounds like you might have some fairly severe restrictions on what alternative approaches you are even allowed to use. I'm not quite clear what you are and are not allowed. Would a textbox rather than a combobox be acceptable?

I feel that the issue is that the control is doing something as a result of the databinding. I don't know what it does, but it may have some kind of dependency on the number of items. That might have happened because whoever wrote the control felt that nobody would use a pick control with more than some small number of items. In any case, it hardly matters why they did what they did. I have never tried binding that many items to a combobox, though perhaps I should, just to see what happens. I don't have time at the moment, though, or I might be able to make a better suggestion. The bottom line is probably that the combobox will slow down the more items you add for whatever reason.

So, if we go with that assumption, then the obvious next step is to find a different approach. A combobox isn't all that interesting as a control. A textbox does virtually the same thing, except for the drop down part. It doesn't sound like you are really using the drop down part, anyways, because nobody would scroll through 60k items. So, would a textbox be acceptable? If so, then use a textbox and handle the textchanged event. In that event, you'd do something like this:

If textbox.Length>0 Then
 yourDatatable.DefaultView.Filter = "SomeField Like '" textbox.Text & "%'"
 'Something else here
End If

I can't say anything about the "something else here" part at this time. Basically, it comes down to what you are doing with the selection. It could certainly be used to fill a combobox, but there are so many options.

Anyways, I have to run. Good luck.

[MonkOFox]

I'm not sure if you can track the scroll offset in a combobox or not. But you could just load 50/100/200 items at a time in the combobox. If the user gets to where he/she can see the last item in the current set, add another 50/100/200 (or the remaining items).

If nothing else, you could use a textbox, button and scrollable panel to simulate the look and feel of a combobox. I know you can manipulate panels fairly easily.

So you could initially add 50/100/200 labels to the panel in a tablelayout with one column. You can keep track of the x,y value of the last item added. If your scroll off set of the panel is >= to that stored y value, append 50/100/200 more labels to the panel (and remove the previous 200 if you want (but that will cause more rearranging).

If the person starts typing into the textbox, do your like search for the PartialNumber, clear all current labels and then bind the first 50/100/200 items from that sub query. The scrolling will have the same result as above.

The 50/100/200 would be a user preference via linklabels.

If you need the PartialNumber and it's unique id represented, you can always store the ID in the Label's Tag field.

Just an idea.

[jayinthe813]

I asked you if you are using combobox selected indexed changed event because setting a datasource will trigger this event. This would mean whatever code is in that event could be running more than once, depending how you are triggering the data load to occur. I believe setting the DisplayMember can also trigger the event (I do not recall that, would have to research). Make sure you have no code in this event that could be blocking the UI. I agree with SH, the control could be doing something that is probably taking a while. Try this to test out performance:

Comment out this piece:

Me.ComboBox1.DataSource = dtFindPartial
Me.ComboBox1.DisplayMember = "PartialNumber"
Me.ComboBox1.ValueMember = "PartialID"

Replace it with this:

For each row in dtFindPartial
ComboBox1.Items.Add(row.item("PartialNumber")
Next row

See if this changes anything. Did that affect the speed at all? IIRC, databinding is supposed to be faster, but the fact that you are changing displaymember/valuemember may be affecting the control as well.

[chapran]

I tested this approach - no difference.
I did not write any code in event procedures of Combo.

[chapran]

Well, it sounds like you might have some fairly severe restrictions on what alternative approaches you are even allowed to use. I'm not quite clear what you are and are not allowed. Would a textbox rather than a combobox be acceptable?

I feel that the issue is that the control is doing something as a result of the databinding. I don't know what it does, but it may have some kind of dependency on the number of items. That might have happened because whoever wrote the control felt that nobody would use a pick control with more than some small number of items. In any case, it hardly matters why they did what they did. I have never tried binding that many items to a combobox, though perhaps I should, just to see what happens. I don't have time at the moment, though, or I might be able to make a better suggestion. The bottom line is probably that the combobox will slow down the more items you add for whatever reason.

So, if we go with that assumption, then the obvious next step is to find a different approach. A combobox isn't all that interesting as a control. A textbox does virtually the same thing, except for the drop down part. It doesn't sound like you are really using the drop down part, anyways, because nobody would scroll through 60k items. So, would a textbox be acceptable? If so, then use a textbox and handle the textchanged event. In that event, you'd do something like this:

If textbox.Length>0 Then
 yourDatatable.DefaultView.Filter = "SomeField Like '" textbox.Text & "%'"
 'Something else here
End If

I can't say anything about the "something else here" part at this time. Basically, it comes down to what you are doing with the selection. It could certainly be used to fill a combobox, but there are so many options.

Anyways, I have to run. Good luck.

Based on your suggestion I tried this:

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
If sender.Name <> "TextBox1" Then Exit Sub
If TextBox1.TextLength > 0 Then
dtFindPartial.DefaultView.RowFilter = "PartialNumber Like '" & TextBox1.Text & "%'"
Else
ListBox1.DataSource = Nothing
ListBox1.Visible = False
Exit Sub
End If
If dtFindPartial.DefaultView.Count > 0 Then
ListBox1.DataSource = dtFindPartial.DefaultView
ListBox1.DisplayMember = "PartialNumber"
ListBox1.Visible = True
Else
ListBox1.DataSource = Nothing
ListBox1.Visible = False
TextBox2.Text = ""
End If
End Sub

Private Sub ListBox1_Click(sender As Object, e As EventArgs) Handles ListBox1.Click
'MessageBox.Show(sender.Name)
'MessageBox.Show(e.ToString & ", Click")
TextBox1.Text = ListBox1.SelectedItem(1).ToString
'TextBox2.Text = ListBox1.SelectedItem(0).ToString
'TextBox2.Text = dtFindPartial.DefaultView.Item(0).Item(1).ToString
'TextBox2.Text = ListBox1.SelectedItem(1).ToString
ListBox1.Visible = False
MessageBox.Show("Searching for PartialID = " & ListBox1.SelectedItem(0).ToString) ' will be replaced with Search procedure

End Sub

It is much much faster. I just need to make my boss allow me to go that way.

Thank you

[Shaggy Hiker]

Cool, but unfortunately, something else is going on. I ran this code on a Win7 system with a measly 2GB RAM, though a quad core CPU (which won't matter, because only one core will be used), which is not a powerful computer for this time:

Dim mDT As New Datatable

mDT.Columns.Add("ID", GetType(Integer))
mDT.Columns.Add("Display", GetType(String))

'Start the profile timer.
ProfileTimer.TheTime.Track("Start")

'Fill the datatable with 60,000 rows.
For x = 1 To 60000
   Dim dr As DataRow = mDT.NewRow
   dr(0) = x
   dr(1) = "Blue" & x.ToString
   mDT.Rows.Add(dr)
Next

'Note when the building of the rows is complete.
ProfileTimer.TheTime.Track("Built")

Me.cbLong.DataSource = mDT
Me.cbLong.DisplayMember = "Display"
Me.cbLong.ValueMember = "ID"

'Stop counting.
ProfileTimer.TheTime.StopAll()

So, what the code does is creates a datatable and puts 60,000 records in it. I then take that datatable and set it as the datasource for a combobox (called cbLong), and set the display and value members. I used the mini-profiler timer (you can find it in the CodeBank under my name and ProfileTimer), which uses the highly accurate Stopwatch object to do the timing (you might be interested in the Stopwatch object, as it is far more accurate and precise than the timing method you used). I measured the time taken to create the datatable and the time taken to set the datasource and set up the display and value members.

The results were this:

Time to put 60,000 rows in the datatable: 318ms
Time to set the datasource and members: 185ms

In other words, binding the datatable took just under 0.2 seconds on a middling computer using code VERY similar to yours, as far as I can see. So, why the difference? I though it might have something to do with ordering the data, so I altered the code such that the ID field is a random number from 0-60,000, and the display field is a random string. This had no noticeable impact on the performance at all. In fact, the time taken to do the binding actually went down on the third test to 154ms.

The simple answer is: I have no idea why you are getting such horrible performance, but it isn't the combobox as I suggested earlier. When doing the same thing in a test, the performance I got was as fast as you could ask for. Binding in less than two tenths of a second means it would appear instantaneous. I don't know of any side effects that could be causing your trouble. If you are handling any events for the combobox, you could put breakpoints in there and see whether or not they are reached, but you already said that you aren't handling any events, yet.

Something is clearly wrong, I just can't say what it is. The first thing I would try would be to use the Stopwatch to measure the time it takes to fill the datatable. If there is a likely place for a slowdown, it is there, not in the binding. You thought you ruled that out, but I'd check again.

[chapran]

I have no idea why it happens as well. I have several computers and tested that slow part on 3 of them. The same slow result.

Thank you.

[Shaggy Hiker]

You might try commenting out the code where you actually query the DB, and try the code that I showed to build a datatable on the fly. If the performance stays bad, then the issue is not the datatable. If the slowdown goes away, then the datatable is likely to be the problem, though I don't see why that would be.

[chapran]

Cool, but unfortunately, something else is going on. I ran this code on a Win7 system with a measly 2GB RAM, though a quad core CPU (which won't matter, because only one core will be used), which is not a powerful computer for this time:

Dim mDT As New Datatable

mDT.Columns.Add("ID", GetType(Integer))
mDT.Columns.Add("Display", GetType(String))

'Start the profile timer.
ProfileTimer.TheTime.Track("Start")

'Fill the datatable with 60,000 rows.
For x = 1 To 60000
   Dim dr As DataRow = mDT.NewRow
   dr(0) = x
   dr(1) = "Blue" & x.ToString
   mDT.Rows.Add(dr)
Next

'Note when the building of the rows is complete.
ProfileTimer.TheTime.Track("Built")

Me.cbLong.DataSource = mDT
Me.cbLong.DisplayMember = "Display"
Me.cbLong.ValueMember = "ID"

'Stop counting.
ProfileTimer.TheTime.StopAll()

So, what the code does is creates a datatable and puts 60,000 records in it. I then take that datatable and set it as the datasource for a combobox (called cbLong), and set the display and value members. I used the mini-profiler timer (you can find it in the CodeBank under my name and ProfileTimer), which uses the highly accurate Stopwatch object to do the timing (you might be interested in the Stopwatch object, as it is far more accurate and precise than the timing method you used). I measured the time taken to create the datatable and the time taken to set the datasource and set up the display and value members.

The results were this:

Time to put 60,000 rows in the datatable: 318ms
Time to set the datasource and members: 185ms

In other words, binding the datatable took just under 0.2 seconds on a middling computer using code VERY similar to yours, as far as I can see. So, why the difference? I though it might have something to do with ordering the data, so I altered the code such that the ID field is a random number from 0-60,000, and the display field is a random string. This had no noticeable impact on the performance at all. In fact, the time taken to do the binding actually went down on the third test to 154ms.

The simple answer is: I have no idea why you are getting such horrible performance, but it isn't the combobox as I suggested earlier. When doing the same thing in a test, the performance I got was as fast as you could ask for. Binding in less than two tenths of a second means it would appear instantaneous. I don't know of any side effects that could be causing your trouble. If you are handling any events for the combobox, you could put breakpoints in there and see whether or not they are reached, but you already said that you aren't handling any events, yet.

Something is clearly wrong, I just can't say what it is. The first thing I would try would be to use the Stopwatch to measure the time it takes to fill the datatable. If there is a likely place for a slowdown, it is there, not in the binding. You thought you ruled that out, but I'd check again.

I was not able to find what Imports should be added to use ProfileTimer.TheTime.Track("Start")
I commented everything related to ProfileTimer and replaced with your altered code my code. Population table doesn't take any significant time, but
lines Me.ComboBox1.DataSource = mDT
Me.ComboBox1.DisplayMember = "Display"
Take about 4 second each.
So, there is no difference between data fro database and data created on the fly.

Thank you very much.

[Leary222]

I was not able to find what Imports should be added to use ProfileTimer.TheTime.Track("Start")

Profile timer is a third party library

I used the mini-profiler timer (you can find it in the CodeBank under my name and ProfileTimer), which uses the highly accurate Stopwatch object to do the timing (you might be interested in the Stopwatch object, as it is far more accurate and precise than the timing method you used).

[Shaggy Hiker]

Profile Timer isn't needed, either. I just have it handy. You could do that timing with the Stopwatch object just as easily.

You have some kind of strange side effect going on. I have no guess as to what it might be if you aren't handling any events based on the combobox.

[chapran]

I added your ProfileTimer into the project. I've got about 6 and 5 seconds. So, the same result.
I'm planing to run your code in a separate project to see if somehow my project affects the performance. I'm using ComponentOne Resizer Light and Infragistics controls. Maybe they affect somehow.

Thank you.

[chapran]

I was not able to find what Imports should be added to use ProfileTimer.TheTime.Track("Start")
I commented everything related to ProfileTimer and replaced with your altered code my code. Population table doesn't take any significant time, but
lines Me.ComboBox1.DataSource = mDT
Me.ComboBox1.DisplayMember = "Display"
Take about 4 second each.
So, there is no difference between data fro database and data created on the fly.

Thank you very much.

I created an absolutly new independent WinForm Project. Added your code. Still same problem - takes about 12 sec.
My machine - Win 7 with SP1 64 bit, Processor Intel Xeon(R) W3503 @2.4GHz 2.40GHz, RAM 12 GB, Video ATI Radeo HD 4600

[TnTinMN]

I think that Jay mentioned this in a roundabout way, but make sure that the ComboBox.Sorted property is set to false.

I did my best to simulate your string values and loading them into the DT in sorted order. On my machine the following code loaded in about 1600 ms with Sorted=False and about 10000 ms with it set to True.

Public Class Form1

   Private rnd As New Random
   Private Const num As Int32 = 60000
   Friend dict As New SortedDictionary(Of String, Int32)()
   Private dt As New DataTable
   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
      For i As Int32 = 1 To num
         Dim key As String = GetPartial(dict)
         dict.Add(key, i)
      Next

      dt.Columns.Add("Partial", GetType(String))
      dt.Columns.Add("PartialID", GetType(Int32))

      dt.MinimumCapacity = num
      dt.BeginLoadData()
      For Each kvp As KeyValuePair(Of String, Int32) In dict
         dt.Rows.Add(kvp.Key, kvp.Value)
      Next
      dt.EndLoadData()

      Dim sw As New Stopwatch
      sw.Start()
      With ComboBox1
         .Sorted = False
         .AutoCompleteMode = AutoCompleteMode.Suggest
         .AutoCompleteSource = AutoCompleteSource.ListItems
         .DisplayMember = "Partial"
         .ValueMember = "PartialID"
         .DataSource = dt.DefaultView
      End With

      sw.Stop()
      MsgBox(String.Format("CB Load:{0} ms", sw.ElapsedMilliseconds.ToString))
   End Sub

   Private Function GetPartial(l As SortedDictionary(Of String, Int32)) As String
       ' 1001-0256-1-A-01
      Const fmt As String = "{0:0000}-{1:0000}-{2:0}-{3}-{4:00}"
      Dim ret As String = String.Format(fmt, rnd.Next(10000), rnd.Next(10000), rnd.Next(10), ChrW(rnd.Next(65, 92)), rnd.Next(100))
      If l.ContainsKey(ret) Then
         ret = GetPartial(l)
      End If
      Return ret
   End Function

End Class

Edit: Interesting tidbit - you can not set it to true after attaching a DataSource, but it sure does affect the fill time if set before attaching the source.

[chapran]

I added your code into new project, commented line .Sorted = False. Ran project, it took 2500 ms. Uncommented the line - same result, changed False to True. The result 16000 ms!!!
I checked the default value of Sorted property. It is false. In my buisiness project Sorted property was not changed, it is false.
By some reason your code is faster. I did not look for the difference between yours and Shaggy Hiker's yet.
I decided to compile the set of reasons for my boss to change the design. I'm not sure I will be successful in that conversation. He is very tough guy, he hates changes. Anyway, if I cannot speed up the process with current user interface I need to change it.

Thank you.

[TnTinMN]

I checked the default value of Sorted property. It is false. In my buisiness project Sorted property was not changed, it is false.!

Well it was worth a try. Just out of curiosity, have you tried rebooting your computer? I know its a cliche, but I just ran across an thread posting where someone else was having speed issues much worse than you and after a reboot it all went away.

[chapran]

I'm fighting with this issue for many days, and every evening I shut my PC down and start it in the morning.

Thank you

[wes4dbt]

I find it interesting how times very for different activities on different machine. I have a Win7 OS x64, 3rd Gen I7 CPU with 16Gb or ram.

with SH example
Time to put 60,000 rows in the datatable: 120ms
Time to set the datasource and members: 3.2 seconds 'This is much longer than to took sh

with Tin example
Time with sorted = false was 1200ms 'this is a little faster
Time with sorted = true was 7750ms ' this is 2.25 seconds faster.

Don't understand why I'm so much slower than sh when it sets the datasource and datamember, but faster in all other areas. And, the terrible performance of chapran doesn't make any sense at all.

[TnTinMN]

I find it interesting how times very for different activities on different machine. I have a Win7 OS x64, 3rd Gen I7 CPU with 16Gb or ram.
...
Don't understand why I'm so much slower than sh when it sets the datasource and datamember, but faster in all other areas. And, the terrible performance of chapran doesn't make any sense at all.

Part of the reason is that I posted times just as an example and my machine was partially loaded (4 VS2008 instances, 3 VS2013, 3 Excel, Reflector, FireFox, etc). After posting I installed some updates and rebooted. After making a tweek to the code I got 750 ms with Sort=False and 6300 ms w/ sort. Then I had a doh moment when I realized speed increase was not due to my tweek but my machine's load. This is why these types of speed tests have to be taken with a grain of salt.

Also, I should have given the Random a constant seed value, that way we all would have been working with the same strings.

[wes4dbt]

Tn,

I did some more testing and it still surprises me that it takes @1200ms to set the datasource and datamember using your datatable and @ 4 seconds using sh datatable. There really doesn't seem to be enough difference between them to cause such a large difference, but it does.

[TnTinMN]

I did some more testing and it still surprises me that it takes @1200ms to set the datasource and datamember using your datatable and @ 4 seconds using sh datatable. There really doesn't seem to be enough difference between them to cause such a large difference, but it does.

Wes,

The difference lies in the order of setting the DataSource and DisplayMember properties.

Public Property DisplayMember() As String
   Get
      Return DisplayMember.BindingMember
   End Get
   Set(ByVal value As String)
      Dim oldDisplayMember As BindingMemberInfo = DisplayMember
      Try
         SetDataConnection(DataSource, New BindingMemberInfo(value), False)
      Catch
         DisplayMember = oldDisplayMember
      End Try
   End Set
End Property

Public Property DataSource() As Object
   Get
      Return DataSource
   End Get
   Set(ByVal value As Object)
      If value IsNot Nothing AndAlso Not (TypeOf value Is IList OrElse TypeOf value Is IListSource) Then
         Throw New ArgumentException(SR.GetString(SR.BadDataSourceForComplexBinding))
      End If
      If DataSource Is value Then
         Return
      End If
      ' When we change the dataSource to null, we should reset
      ' the displayMember to "". See ASURT 85662.
      Try
         SetDataConnection(value, DisplayMember, False)
      Catch
         ' There are several possibilities why setting the data source throws an exception:
         ' 1. the app throws an exception in the events that fire when we change the data source: DataSourceChanged, 
         ' 2. we get an exception when we set the data source and populate the list controls (say,something went wrong while formatting the data)
         ' 3. the DisplayMember does not fit w/ the new data source (this could happen if the user resets the data source but did not reset the DisplayMember)
         ' in all cases ListControl should reset the DisplayMember to String.Empty
         ' the ListControl should also eat the exception - this is the RTM behavior and doing anything else is a breaking change
         DisplayMember = ""
      End Try
      If value Is Nothing Then
         DisplayMember = ""
      End If
   End Set
End Property

As you can see from this code converted from the MS source, if you set DisplayMember after setting DataSource as in Shaggy's example, the "SetDataConnection" method gets called twice. This obviously is going to cost time and it appears as though the penalty is quite high. Try setting DisplayMember before DataSource in Shaggy's code and you will see nice drop in the execution time.