The timing for adding empty rows is interesting but not very valuable. As soon as you start adding data the nice stuff starts. There is a reason why there are only 3 solutions available: one outdated (Einhugur), one working well but with a few problems (piDog) and and the Mac-only one (MBS).
Looks like I (or someone) should create a listbox subclass that uses an in memory SQLite database to store the list data. Pair that with a a separate scroll bar and only put the visible data in the listbox. I guess 20 hours of programing would get you close (at least an initial beta version) Ok maybe including the paint events would take a bit more time.
[quote=328759:@J H Timmerman]4.5M 79 - 172 (!)
As in Alexander’s case, the 5M test could not be finished,
as Windows displays an error and the program must be shut down.
In this case Windows Task Manager showed > 99% memory used.[/quote]
How about this?
Just learned something new about speeding up the listbox.
On my machine (Win 10, i5 3.10 GHz, 16GB RAM) it took 1,309 seconds to load 5,000,000 items into a list box. Only one column, it couldn’t handle 5 columns, only got to 3.5m.
The exact same code but setting ScrollBarVertical = False . Loaded the listbox in 35 seconds. WOW! Quite a difference.
Sorry to keep going on and on @Dave S , but speed tests do intrigue me.
I welcome you to keep going on and on. I’ve got a client who loads 5000 rows into a two column listbox and it takes about 1 second per 1000 rows unless I hide the listbox. The interface blink is less than preferred so I’m watching your discoveries for any tips that could significantly improve performance without a blink 
@Neil B.
Interesting and promising result, where you used an in memory database.
How many space did this database use up?
I have not tried this yet… but what about ENABLED=FALSE in addtiion to the other suggestions?
[quote=328818:@J H Timmerman]@Neil B.
Interesting and promising result, where you used an in memory database.
How many space did this database use up?[/quote]
To load from an SQLite database into an in memory db using attach takes 3.56 seconds when only adding the displayed rows to the listbox. This puts my total memory usage for the app at 389MB, when loading 5 columns X 5 million rows.
On the other hand loading a single column X 5 million rows into the listbox using addrow took 35 seconds and used 1.2 gb of ram.
Did you check out the results using a canvas while hiding the listbox. (8 posts up)
Off topic: Funny I just noticed I can like my own posts. Strange. Conceited. 
I just tried this and it didn’t make any difference. I’ll summarize my (very surprising) results below.
- 1,309 seconds with scrollbar and listbox visible
- 119 seconds with listbox.visible = false and listbox ScrollBarVertical = true
- 35 seconds with listbox.ScrollBarVertical = false and listbox.visible = true
- 26 seconds with listbox.ScrollBarVertical = false and listbox.visible = false
In summary hiding the virtical scrollbar is much more important than hiding the listbox.
More testing: (5 million rows with varying number of columns)
1 column: 26 second - 1.2 GB
2 columns: 30 second
3 columns: 36 second - 2.8 GB
4 columns: 37 second
5 columns: 41 second - 6.5 GB Ram used
Tests with 4 & 5 columns needed 64 Bit to prevent a memory shortage.
Windows only here…
Single column, 5 million rows @ 14.39 seconds.
Feel free to use that AllowRedraw call to stop the flicker which means you don’t have to hide the control. Neil, give that a try on your control above instead of using the canvas, see how you get on.
[code]Private Sub Test_SendMessage(TestControl As ListBox, Result As Label)
AllowRedraw(TestControl, False)
TestControl.ScrollBarVertical = False
dim start as Double=Microseconds
for a as Integer = 0 to 5000000
TestControl.AddRow "test " + str(a)
next
dim ittook as Double=(Microseconds-start)/1000000 _
TestControl.ScrollBarVertical = true
AllowRedraw(TestControl, True)
TestControl.Invalidate ’ force the refresh of the control as no updates just took place
Result.Text = str(ittook) + " seconds"
End Sub
[/code]
Private Sub AllowRedraw(Ctrl As Control, State As Boolean)
Const WM_SETREDRAW = &h000B
Declare Function SendMessage Lib "User32" Alias "SendMessageW" ( hWnd As Integer, Msg As UInt32, wParam As UInteger, lParam As Integer ) As Integer
Call SendMessage(Ctrl.Handle, WM_SETREDRAW, Ctype(State, UInteger), 0)
End Sub
@ You awesome.
That eliminate the flicker much better than the canvas, with less work.
On my computer your method took 15.22 seconds. Using your code but hiding the listbox instead took 12.03 seconds.
Considering that I would prefer having the listbox ‘froze’ rather than disappear and leave a ‘hole’.
[quote=328727:@Alexander van der Linden]A quick test reveals following:
[/quote]
As you do the 5 million rows in a loop, chances are the crash comes from the fact that the system does not have time to properly dispose of intermediate strings. I would guess that the same code looped through a timer would not crash.
Loops for such a thing are evil.
Quick and dirty! Don’t do it unless you don’t mind seeing ‘My App (Not Responding)’.
I suspect the crash was from a memory issue. In my tests it crashed close to 4gb which is the limit for 32 bit apps. In a 64 bit version I used almost 7GB in one of my tests (loop - yeah go out of the room while it’s running) with out a crash.
BTW I just downloaded the piDog demo. That does makes the standard listbox look very clumsy.
[quote=328881:@Neil Burkholder]@anon20074439 You awesome.
That eliminate the flicker much better than the canvas, with less work.
On my computer your method took 15.22 seconds. Using your code but hiding the listbox instead took 12.03 seconds.
Considering that I would prefer having the listbox ‘froze’ rather than disappear and leave a ‘hole’.[/quote]
Neat, try the following then, the listbox should remain as its ability to make itself vanish is removed by the call to AllowRedraw
[code]AllowRedraw(TestControl, False)
TestControl.ScrollBarVertical = False
TestControl.Visible = False
dim start as Double=Microseconds
for a as Integer = 0 to 5000000
TestControl.AddRow "test " + str(a)
next
dim ittook as Double=(Microseconds-start)/1000000 _
TestControl.Visible = True
TestControl.ScrollBarVertical = true
AllowRedraw(TestControl, True)
TestControl.Invalidate ’ force the refresh of the control as no updates just took place
Result.Text = str(ittook) + " seconds"[/code]
Mine got down to 10.8 seconds.
Precisely, using a loop does not yield to the scheduler to clean temporary strings, and this accumulates, eating up memory that would have been freed with a timer.
I doubt each row has 1400 bytes into it.
Great. Best of both worlds.
I thought for a minute you had a fast computer. It turns out I was using i.totext. cstr(i) is 5 seconds faster!
Just wanted to chime in here on DataView (a commercial product of mine) since it was mentioned previously.
I’ve added an example to the demo that shows a simplified on-demand approach. You can see it in the Listbox comparison window in the demo project.
By setting listCount and then populating cells in the cellBackgroundPaint event, I was able to add 5 million rows in 1.12 seconds on my Macbook Pro
Cell values are only set as they become visible.
Drag Reordering rows gets a little laggy with 5 million rows and column sort won’t work unless all rows are populated, but that is to be expected…
@Beatrix Willius - I looked at the “Select All” issue you mentioned earlier. Turned out to be the way the Change event was fired with each row selection, and that the Event was being used to enable a pushButton. The big issue was using selectedRows.ubound in the Change event as selectedRows returns an array of the selected indexes. 50k rows was generating 50k array creations, each with one more row than the previous. v1.8.2.2 fixes the issue along with a few others your comment brought to my attention.
I know. I was playing around with it. It’s like magic on steroids. Great work.
My project of the last days: improve the slow opening of a database in my app.
The data loading is done on-demand with the piDog DataView so this was fast. The database cursor also was created in a couple of milliseconds. For housekeeping (selecting and dragging rows mostly) I need some keys out of the cursor. So I do a loop over records and fish out the keys.
Now the fun part: getting the cursor was fast but looping over the records in the cursor was taking seconds. The root cause was an SQL function in Valentina. When I replaced this with an additional field with a method everything was fast again.