Faster way to populate a combobox

[realm174]

Hello,

I have a form on a VB app that has 2 comboboxes. When I load that form, I need to fill the comboboxes with data from a data table. What I have right now works very well, except for the fact that it is excruciatingly slow since the table has about 24000 records in it. Here's what I have:

Sub comboPopulate()        
        Dim dt As New DataTable("From")

        dt = TryCast(frmFSEHelper.dgvAirportList.DataSource, DataTable)
        If Not dt.Columns.Contains("Description") Then            
            dt.Columns.Add("Description", GetType(String))
        End If
        For Each row As DataRow In dt.Rows
            row.Item("Description") = row.Item("ICAO") & " - " & row.Item("City") & ", " & row.Item("Country")
        Next

        'This is to duplicate the Origin combobox to the Destination combobox
        If dt.Rows.Count > 0 Then
            cmbOrigin.DataSource = dt
            cmbOrigin.DisplayMember = "Description" 'What is displayed
            cmbOrigin.ValueMember = "ICAO" 'The ID of the row
            cmbDestination.DataSource = dt.Copy
            cmbDestination.DisplayMember = "Description" 'What is displayed
            cmbDestination.ValueMember = "ICAO" 'The ID of the row
        End If
    End Sub

Any idea/suggestions on how I can make this more efficient?

Many thanks!


Cheers,

Realm174

 

[realm174]

Can't figure out how to modify my previous post... but I've made some progress... First, the section

        If Not dt.Columns.Contains("Description") Then            
            dt.Columns.Add("Description", GetType(String))
        End If
        For Each row As DataRow In dt.Rows
            row.Item("Description") = row.Item("ICAO") & " - " & row.Item("City") & ", " & row.Item("Country")
        Next

has been replaced with

        If Not dt.Columns.Contains("Combined") Then
            dt.Columns.Add("Combined", GetType(String))
            For Each row As DataRow In dt.Rows
                row.Item("Combined") = row.Item("ICAO") & " - " & row.Item("City") & ", " & row.Item("Country")
            Next
        End If

after I've made changes to the sql table from which I get the data. I added a column called "Combined" that already have the information I need to pull. I am leaving the "if" portion there in case the table hasn't been updated. (This app runs on multiple databases, so I only modified my test db).

So doing that improved the speed quite a bit,as I don't need to iterate through each row to combine the different columns. Then, tracing through the code, I found out that there's 2 places where it does take a fair amount of time to update, and that is on the line

cmbOrigin.DisplayMember = "Description"

I am guessing that is because the entire datable needs to be scanned to populate the combobox. Not sure this can get improved. But then, I need to update the DisplayMember again on destination, which adds another delay.

So I am left with one question at this point. Is there a faster way to update the DisplayMember? (Especially since I need to do that twice)



Cheers,

Realm174

 

[jebenson]

The best way to speed up populating the combobox is not to put so many items in there. 24,000 is a LOT for one combobox. If possible, do some sort of filtering before populating the combobox, to reduce the number of records.

Here's a tip: you can create the "Combined" field in SQL, and not need to make any change to your databases:

Select (ICAO + ' - ' + City + ', ' + Country) As Combined From <table name here>

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[griffitd]

hi

looks like you are also checking for duplicates in the combo. im guessing because the source data has duplicates

could you only select distinct items for the combo box??

 

[realm174]

jebenson: Unfortunately, I can't filter it down, but thanks for the tip on the "Select". It's something I already knew but I never thought of it. SQL isn't my thing, so I forget those simple things. Really appreciate it tho!

griffitdI am not checking for duplicates, there's no duplicates in the data source. I check to see if the column already exists, if not, I add the column, then fill in the data. But that's just on the data table itself. I'm actually eliminating that code from my last post, as all the tables now have the needed column. So the time consuming part is really the assignment of the values to the comboboxes. As jebenson stated, other than shrinking the amount of items, there's not much I can do to speed that up. But thanks!


Cheers,

Realm174

 

[jebenson]

Perhaps you could break City and Country out into their own combos. First select a Country, which populates the Cities combo. Then select a City, which populates the original combo. This should reduce the load times significantly.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!