ListView Loading Slow

[admdev]

Hi all,

Coming from a VB.NET\C# environment back to VB6, which I have not coded for years, is a bit challenging. So, I needed to add a new form to one of our VB6 applications, which is supposed to display a list of inventory items. The Items table contains up to 6500 items. All I'am displaying is the item number and the description. In implementing a filtering option, using a textbox, I need to repopulate the items from the new recordset. So, I am clearing the items, loop through the new recordset, and add the items. Unfortunately, it takes up to five seconds to repopulate. Doing some research, I found out that by hiding the ListView and show it after the items are added, should improve its performance; however, I do not see a big difference.

Has anybody experienced this?

I need to be able to filter the list within one or two seconds.

Any ideas?

[SamOscarBrown]

No, but try this....First put all your records into an array, clear and hide the listview, add the items from the array to the listview, and show the listview. Let me know.

[Spoo]

AD

Perhaps one way to identify the bottleneck would be to "comment out"
any ListBox.AddItem stuff while you are in the loop .. ie, you will be
seeing how long it takes to just loop thru the recordset. You could set
2 breakpoints .. one just before the loop, one just after .. and watch
the time it takes.

I would hope that you can loop thru the recordset in a flash, but doing
the above might be useful anyway.

BTW, when you say "hiding the ListView", do you mean setting Visible = False?

I can affirm that doing so with a FlexGrid improves display time immensely. I have
not tried it with ListView, though. Do you have the (ahem) flexibility to use a FlexGrid
instead of a ListView?

Spoo

[Doogle]

Five seconds sounds a long time. I suspect your filtering mechanism may be causing a problem. Perhaps you could post the code and we can take a look.

[admdev]

Thank you all for the reply.

Below is the code I am using.

Private Sub PopulateItems()

Me.lstvItems.ListItems.Clear
Me.lstvItems.Sorted = False
Dim sql As String
Dim rs As New ADODB.Recordset
Dim I As Integer

Me.lstvItems.Visible = False

sql = "Select ItemNumber, Description from ItemMaster"
rs.Open sql, dbase

I = 1
While Not rs.EOF

With Me.lstvItems.ListItems.Add(I, , rs("ItemNumber"))
.SubItems(1) = rs("Description")

End With

rs.MoveNext
I = I + 1

Wend

Me.lstvItems.Visible = True

End Sub

[DataMiser]

How many records are we talking about here?

[admdev]

We have up to 6500 or 7000 records.

[admdev]

Sorry guys!

The actual code when filtering is this one. However, the code I provided previously, which it's being called when the form loads, also takes two to three seconds to load.

Private Sub txtFilter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim rs As New ADODB.Recordset
Dim sql As String
Dim I As Integer

sql = "Select ItemNumber, Description from ItemMaster Where ItemNumber Like " _
& "'%" & Me.txtFilter.Text & "%'" & " Or Description Like " _
& "'%" & Me.txtFilter.Text & "%'"

rs.Open sql, dbase

Me.lstvItems.ListItems.Clear
Me.lstvItems.Visible = False

I = 1
While Not rs.EOF

With Me.lstvItems.ListItems.Add(I, , rs("ItemNumber"))
.SubItems(1) = rs("Description")

End With

rs.MoveNext
I = I + 1

Wend

Me.lstvItems.Visible = True
End Sub

[DataMiser]

5 seconds seems like a lot for this. What kind of cursor settings are you using if any? A server side forward only cursor will be the fastest possible.

Is the DB on a network or local the machine? If on a network what speed is the network and is there a lot of traffic?
What db engine? Are others using it at the same time from other pcs?

Try this and see if it helps

rs.Open sql, dbase,adOpenForwardOnly,adLockOptimistic

[admdev]

DataMiser,

The Database server is at a remote location; however, I added a breakpoint after the "rs.Open sql, dbase" and it was executing within a second. So, I thought that at that point the data had already been retrieved from the DB. It looks like the data is being retrieved as I loop through the recordset. I backed up the database and restored it on my local db and it is much faster.

Thanks.

[Krool]

According to MSDN you can also speed up the creation of large lists by sending a WM_SETREDRAW to the listview before and after. (0 before and 1 after at wParam) The "Refresh" method is necessary after that.

[Doogle]

I just realised that you're executing the 'filtering' on every 'KeyUp' event of the TextBox. That's going to slow things down a bit unless you're filtering on only 1 character.