Loading data in to a combo box is slow.

Question

I have a VB6 application with a search screen. On the search, I have 9 combo boxes. Some of the combo boxes only have a couple items, but some have a couple hundred items. It takes a long time (couple of seconds) to populate the data.

Each combo box is configured the same: Sorted = False, Style = 2 - Dropdown List

3 of the combo boxes have less that 20 items. 1 has 130 items. 4 have approximately 250 items 1 has almost 700 items.

I fill all nine combo boxes with similar code.

While Not RS.EOF

    cmbX.List(i) = RS("Description")
    cmbX.ItemData(i) = RS("Id")

    i = i + 1

    RS.MoveNext
Wend

I tried setting Visible = False but it had no effect on performance.

Is there another way to fill the combo box that will perform better than my existing method?

Answer 1

That does seem like a long time. How are you opening your recordsets? Are you using firehose (readonly, forward-only recordset) cursors? If you aren't, you might get some performance improvement from that. Make sure your SQL statements are ONLY returning the data they need for the combo boxes (i.e. DO NOT use a SELECT *).

If your SQL statements contain WHERE clauses or JOINS, make sure you have indexes on the appropriate fields.

If you are using ACCESS as a backend you will get an immediate speed improvement by upsizing to SQL Server Express.

Answer 2

Here is something you can try. According to this post you can shave about 60% off your overhead by using a Windows API function to populate the combo box, instead of the usual AddItem method:

Private Const CB_ERR As Long = -1
Private Const CB_ADDSTRING As Long = &H143
Private Const CB_RESETCONTENT As Long = &H14B
Private Const CB_SETITEMDATA As Long = &H151

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal _
hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Sub AddItem(cmb As ComboBox, Text As Variant, Optional ItemData As Long)

   Dim l As Long
   Dim s As String

   If VarType(Text) = vbString Then
      s = Text
   Else
      s = Trim$(Str$(Text))
   End If

   l = SendMessage(cmb.hwnd, CB_ADDSTRING, 0&, ByVal s)
   If l <> CB_ERR Then
      SendMessage cmb.hwnd, CB_SETITEMDATA, l, ByVal ItemData
   End If

End Sub

Public Sub Clear(cmb As ComboBox)
   SendMessage cmb.hwnd, CB_RESETCONTENT, 0, 0&
End Sub

You might be able to shave a little more off by omitting the function call, and just calling the API function directly.

Answer 3

Some suggestions:

• Use With RS.

• Use the Recordset object's RecordCount property rather than test for EOF on every iteration (if RecordCount = -1 then you should alter the cursor type, cursor location, etc to ensure RecordCount is supported).

• Use a For..Next loop rather than maintain you own iterator variable.

• Use the bang operator (!).

For example:

With RS

  Debug.Assert .RecordCount >= 0

  Dim counter As Long
  For counter = 0 To .RecordCount - 1

    cmbX.List(counter) = !Description
    cmbX.ItemData(counter) = !Id

    .MoveNext
  Next

End With

Perhaps something else to consider is setting the combo's Sorted property to False and if sorting is required then either use the Recordset's Sort property or do the sorting at source (e.g. using an ORDER BY clause in SQL code).

Answer 4

You can try telling the combobox not to repaint itself while you add the new items. You can do this with WM_SETREDRAW. EDIT - apparently this didn't help, probably because the combo box is being hidden while it's filled, which probably gives you all the same benefits.

  Private Declare Function SendMessage Lib "user32" Alias "SendMessageA"( _
    ByVal hwnd As Long, ByVal wMsg As Long, _
    ByVal wParam As Long, lParam As Any) As Long
  Private Const WM_SETREDRAW = &HB

  Call SendMessage(cmbX.hWnd, WM_SETREDRAW, 0, 0&)
  'DO STUFF'
  Call SendMessage(cmbX.hwnd, WM_SETREDRAW, 1, 0&)

Warning: lots of otherwise excellent VB6 websites tell you to use LockWindowUpdate instead. Do not do this, or you will get bugs. Also the disfavour of Raymond Chen!

Answer 5

Why are you prepopulating the list and then repopulating List(i) and ItemData(i)? Instead you should do the following

While Not RS.EOF

    cmbX.AddItem RS("Description")
    cmbX.ItemData(cmbX.NewIndex) = RS("Id")

    RS.MoveNext
Wend

You will see zero performance difference between Robert Harvey's answer and the code above. For added speed, apply MarkJ's answer.

Another problem could be the cursor you are using to bring the data back from the database. Since you are loading every single item from the recordset, there is very little reason to have a server-side cursor. So you might want to specify Client side cursor when retrieving the recordset.