Except there is no indication of price or how to register - the Registration tab only shows RegExRX and TFTP.
To be honest the site looks pretty dead and abandoned (last post in 2019).
I purchased DataView some years ago and loved it on Mac, but could never get horizontal scrolling to work acceptably under Windows - it flickered insanely. I haven’t kept up on releases for a couple of years, maybe it’s better now.
I’m curious… what’s an “unacceptably long delay” mean exactly?
The reason I ask is that even if you have 3000 records, you should be able to populate that list almost instantaneously… unless that sqlite database is on a network share somewhere (which is a really bad idea, by the way). If the database file is local, you might get some speed by adding an index or two.
Check out NSTableView from the MBS plugin (Monkeybread Xojo plugin - NSTableViewMBS). Loading 100k rows from a database is no problem at all. macOS only, though.
Using Kem’s Data-on-Demand to a remote Postgres server, I get basically instant views of any size table. And dragging the scroll bar makes the rows fly past VERY fast. You’d think it was local data. In fact, a search filter text box I have on way subsets on each keystroke and still is instant even to a remote Postgres table (with proper indexes).
The first time I did it, I only had a button to perform the search, but the results came back so fast I added the keystroke events and the response time is incredible, even against a large remote table (not even on local network).
That class may not have been updated in years, but it works well as is. And it is just Xojo source code, so you can tweak if you want to anyway.
Have you tried loading the database file as an in-memory database to begin with? As Greg said, I would not think that 3.000 records should produce a delay.
If the database file is in a memory stick and you are using a m1 (Apple Silicon) computer…
Use a hard disk (SSD, but even HDD is fast, far faster than a MemoryStick).
Yes, it has a similar concept iOSMobileTableDataSource which is handledj by iOS, not Xojo or user code. It is why many iOS have very good performance even on very large “lists”.
The classes I mentioned are pre-written ways to do that in Xojo Desktop.
It’s been a while since I was working on this and my current work has taken me away from Xojo but I seem to remember there being a couple seconds delay when populating the listbox with larger lists from an in-memory SQLite db stored in a local file.
I can’t seem to find the video in which @Paul_Lefebvre walks us through the lazy loading example that ships with Xojo but in it, he demonstrated how 100k simple rows could cause a delay. In his blog post on the same topic he says, the ListBox control “…is not meant to also be a container for hundreds of thousands of rows of data.” and that, “It can take [a] noticeable amount of time to to populate…”
Still, I feel that your attention to this thread warrants further proof so I’m going to go find that project and see if I can replicate the issue with the latest version of Xojo.
I created a quick little test app to see exactly how long the delay might be given a simple table with six columns and 50k rows full of test data. I timed it at just under 3 seconds to display 50k rows.
This was done with the most basic and direct methods. No data classes or framework overhead of any kind. Here is the script that loads the listbox…
Public Sub RefreshList()
Listbox1.RemoveAllRows
StartTime = System.Microseconds
Try
Var rowsFound As RowSet
rowsFound = App.DB.SelectSQL("SELECT * FROM Customer;")
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow( _
row.Column("id").IntegerValue.ToString, _
row.Column("first_name").StringValue, _
row.Column("last_name").StringValue, _
row.Column("company").StringValue, _
row.Column("homepage").StringValue, _
row.Column("email").StringValue _
)
Next
rowsFound.Close
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
EndTime = System.Microseconds
Var d As Double = (EndTime - StartTime) / 1000000
TimeToLoad.Text = "Loaded " + ListBox1.RowCount.ToString + " customers in " + d.ToString + " seconds"
End Sub
Now, I know 50k rows might seem like a lot but data sets of 250k or more are not unheard of… and only six columns isn’t very realistic either.
Still, I remember this taking more like 3 seconds for 3k rows so now I’m thinking that the time delay wasn’t caused by SQLite or the ListBox… but from using a data class.
Loading data this way from a database is a bad idea. Data aware listboxes only load the currently visible rows from the database.
1 Like
Perhaps I should rephrase my question…
How do you deal with lists that take an unacceptably long time to load?
While my example used a local SQLite database with a test table with only 6 columns, what if we require 20 or 30 columns? What if we’re using URLConnection to fetch data from a REST server? The long delay caused by the remote connection, the parsing of JSON into arrays of objects and then displaying them in a list box while also saving a copy of the object as the row tag… could make attempting to display large lists very painful.
This is why I was looking for other options.
I’m sorry. I’m not sure that I understand this.
What do you mean by ‘data aware list boxes’? Are you talking about sub-classing the ListBox control and adding methods that fetch and display data?
Only loading the currently visible rows from the database sounds like lazy loading to me.
Yes, this is lazy loading. Loading lots of data from a remote location isn’t a good idea.
Yes. Thank you.
Now, how do we implement lazy loading AND make the scroll wheel on the mouse work?
a) PiDog DataView
b) NSTableViewMBS
Come on Beatrix, surely we can do better …
You don’t add text to the Listbox - that’s what slows it down massively.
You add empty rows to your ListBox and then either your objects or the ID of your database records to the RowTag, and in the Listbox.CellTextPaint event you draw the text in the respective cells.
That way ONLY the visible cells are drawn, and you can happily draw multiline text or add graphics as well.
I wrote an article series in xDev (issues 18.2, 18.4, 18.5, 18.6) and an online tutorial (just search for “Best programming practises: the ListBox”) where I also show how to use an interface to simplify the whole thing.
Setting the ListBox to invisible and locking it before filling it can also speed it up.
Two pictures from the xDev articles:
2 Likes
… or from my “Tips & Tricks” column in xDev 12.4:
Tip 4: Speeding up the ListBox
There are several common ways to speed up the ListBox:
- as mentioned in a previous issue you can store data (or a reference to your data like RecordID) in the RowTag and only draw the visible rows and columns as required in the CellTextPaint event (though you have to deal with things like sorting the columns and multiple row selections yourself then).
- you can use “lazy loading” and just load the rows that you need to display
- you can speed up loops filling the Listbox by setting Listbox.Visible to false at the beginning, and restoring the Visible state at the end. However it seems that on some Windows systems the ListBox noticeably disappears when swapping the Visible state. You can prevent this by using the FreezeUpdateWFS and UnfreezeUpdateWFS functions in the Windows Functionality Suite (basically the equivalent of MacOSlib for Windows, available at GitHub - arbp/WFS: Windows Functionality Suite). You should NOT use the Windows declare LockWindowUpdate as sometimes recommended as it should only be used for drag and drop operations on ancient versions of Windows that Xojo doesn’t even support!
- make sure you are using a boolean in the Change event to avoid running code in there until all operations are done or the ListBox might slow down to a crawl!
Note: It seems that the number of columns has a much greater effect on the scrolling speed of the ListBox than the number of rows.
4 Likes