Is this the fastest / Best way to display Data?

[thekurbster]

hello all,
I wanted to know if this is the best way to display about 9,000 + songs on a listview from a database. here's my code... any advice, suggestions would be great.

Private Sub cmdSearch_Click()
On Error Resume Next
Dim rstNewSearch As ADODB.Recordset
Set rstNewSearch = New ADODB.Recordset
Text3.Enabled = True
Text3.BackColor = &HFFFFFF
Dim strmp3name As String






        ListViewMP3.ListItems.Clear
        IsSearchResult = True




rstNewSearch.Open "SELECT DISTINCTROW * FROM Songs ORDER BY Song ASC", cnnKerb, adOpenForwardOnly, adLockReadOnly



tmpChangecolors = fileGetFromIni("Settings", "ChangeColors", App.Path + "\settings.ini")
 
 If tmpChangecolors = "" Then
    tmpChangecolors = "0"
 Else

End If

If tmpChangecolors = 1 Then
        Do While Not rstNewSearch.EOF

              strmp3name = rstNewSearch!song
              ListViewMP3.ListItems.Add , , strmp3name
                          
              ListViewMP3.ListItems(ListViewMP3.ListItems.Count).SubItems(1) = rstNewSearch!IDFolder
              ListViewMP3.ListItems(ListViewMP3.ListItems.Count).SubItems(2) = rstNewSearch!LastPlayed
              ListViewMP3.ListItems(ListViewMP3.ListItems.Count).SubItems(3) = rstNewSearch!LastPlayedDate

            ChangeColors
                
              rstNewSearch.MoveNext
             
            Loop
            ListViewMP3.Refresh
    Else
    
        Do While Not rstNewSearch.EOF

              strmp3name = rstNewSearch!song
              ListViewMP3.ListItems.Add , , strmp3name
                          
              ListViewMP3.ListItems(ListViewMP3.ListItems.Count).SubItems(1) = rstNewSearch!IDFolder
              ListViewMP3.ListItems(ListViewMP3.ListItems.Count).SubItems(2) = rstNewSearch!LastPlayed
              ListViewMP3.ListItems(ListViewMP3.ListItems.Count).SubItems(3) = rstNewSearch!LastPlayedDate


              rstNewSearch.MoveNext
            Loop
            ListViewMP3.Refresh
End If
              
             
    rstNewSearch.Close
    Set rstNewSearch = Nothing
End Sub

Thank you guys in advance.

[Joacim Andersson]

Instead of doing this:
ListViewMP3.ListItems(ListViewMP3.ListItems.Count)

You could declare an object of the ListItem type which is returned by the Add method:

Dim itm As ListItem
'....other code
    Set itm = ListViewMP3.ListItems.Add(Text:=strmp3name)
    itm.SubItems(1) = rstNewSearch!IDFolder
    '... and so on

[thekurbster]

Instead of doing this:
ListViewMP3.ListItems(ListViewMP3.ListItems.Count)

You could declare an object of the ListItem type which is returned by the Add method:

Dim itm As ListItem
'....other code
    Set itm = ListViewMP3.ListItems.Add(Text:=strmp3name)
    itm.SubItems(1) = rstNewSearch!IDFolder
    '... and so on

Is this a faster way or just a cleaner way to write the code? I tried it and it works the same? I just wanted to know cause when I press the command button it takes like 5-14 secs for the data to show depending on what system I use. And while it's doing this code the listview is blank. Is there a way to make it a least show the rows that are visible first in the listview then if i scroll pass that it will then show the next 200 and so on?

[Joacim Andersson]

It would be sleightly faster since it doesn't have to lookup the item. However adding 9000 items to a listview would be a slow process. You could add a DoEvents call after each 200 item or so. That will make it take even longer to process the loop but at least the interface will be updated and it wont looked all freezed up. Simply declare a counter (as long) and add code simular to the following just before the Loop call.

nCounter = nCounter + 1
If nCounter Mod 200 = 0 Then
    DoEvents
End If

[thekurbster]

It would be sleightly faster since it doesn't have to lookup the item. However adding 9000 items to a listview would be a slow process. You could add a DoEvents call after each 200 item or so. That will make it take even longer to process the loop but at least the interface will be updated and it wont looked all freezed up. Simply declare a counter (as long) and add code simular to the following just before the Loop call.

nCounter = nCounter + 1
If nCounter Mod 200 = 0 Then
    DoEvents
End If

Works better thanks, just one problem... it's flickering while doing it, anyway to prevent that?

[Hack]

Try using the LockWindowUpdate API right before your ListView code.

[LaVolpe]

Maybe thinking a bit differently would help. Do you need to display 9000 db records? What about displaying 500 at a time. The ado recordset has paging properties where you can read x number, then go back and get the next x number and so on. Requires far more code than simply dumping them all in a list, but also would appear lightning fast.

[thekurbster]

Maybe thinking a bit differently would help. Do you need to display 9000 db records? What about displaying 500 at a time. The ado recordset has paging properties where you can read x number, then go back and get the next x number and so on. Requires far more code than simply dumping them all in a list, but also would appear lightning fast.

How would this be done?

[LaVolpe]

Look at this link and see if you are interested in pursuing the idea. The MSDN link uses a MSFlexGrid for its example, but the logic would be very similar to listviews or any other "data" control. Re-thinking is the key, your listview scrollbar won't be for 9000+ records, only n number per page. Therefore, you would need to implement a Next & Previous button or a slider control/scrollbar (as done in the link) to move from page to page.

[szlamany]

DoEvents - in my opinion - is not a solution for a slow loading control.

DoEvents will offer up to the operating system the processing of events - clicks on other buttons and what not. This can cause unexpected bugs and problems elsewhere...

Managing the refreshing and redrawing - locking the window - as Hack suggested - those are cleaner ideas.

Who wants to scroll through 9000 items? How about two controls - one to select the letter of the alphabet so that the user "drills down" to what they are looking for.

Then you only load that letter into your listview.

Or maybe try a tree view or some other control that naturally adds a layer for drilling down into.