Is this the fastest / Best way to display Data?
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.
Code:
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. :wave:
Re: Is this the fastest / Best way to display Data?
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:
vb Code:
Dim itm As ListItem
'....other code
Set itm = ListViewMP3.ListItems.Add(Text:=strmp3name)
itm.SubItems(1) = rstNewSearch!IDFolder
'... and so on
Re: Is this the fastest / Best way to display Data?
Quote:
Originally Posted by 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:
vb Code:
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?
Re: Is this the fastest / Best way to display Data?
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.
Code:
nCounter = nCounter + 1
If nCounter Mod 200 = 0 Then
DoEvents
End If
Re: Is this the fastest / Best way to display Data?
Quote:
Originally Posted by 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.
Code:
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?
Re: Is this the fastest / Best way to display Data?
Try using the LockWindowUpdate API right before your ListView code.
Re: Is this the fastest / Best way to display Data?
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.
Re: Is this the fastest / Best way to display Data?
Quote:
Originally Posted by 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.
How would this be done?
Re: Is this the fastest / Best way to display Data?
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.
Re: Is this the fastest / Best way to display Data?
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.