How do you deal with large lists?

You could also start with all customer names starting wit ‘A’, clicking ‘B’ on the keyboard displays all customer names starting with ‘B’ and so on …

1 Like

a) Pidog Dataview ( I had some mixed results with the DataView some years ago. SQLite rocked and Valentina was slow.
b) For macOS only there is a NSTableview implementation in the MBS plugin. There I only need to give the listbox the cursor and the data is loaded for the visible rows only. I’m using this with a row count of 100k.

1 Like

with a little modification this example works

Lazy Loading Data with ListBox

so you hold all data in your object list and the listview show only a few at screen.
if you like to sort columns you need a compare method as example
.Sort(AddressOf CompareColumnName)
if you need a select you need a boolean property.
if you need to edit a row it could be a separate input form.

I’m not using third party software, so I did it myself with a linked list, where I store the first and the last ID if the row and it reads only rows as visible in the listbox too.
On the other hand you have to see how the data is used. for lots of customers, I can do with displaying by alphabet …

Just looking for this as well but it has a big issue, usually when you use list boxes most of the users use scroll part of the mouse, in which here fails to work, at least the demo example in XOJO and on that blog you cannot use the scroll part of the mouse to scroll that scroll bar which is added separate for the lazy loading part, as well if using the mouse on the listbox it will scroll the 50 loaded and it gets stuck like there is nothing else there while if that could be done seamlessly then that would be a nice thing. So far no idea why but mostly all the users hate the scroll bars and ask why it does not work with the mouse and why they have to drag the mouse on that , any valid reply on that ?

Do yourself a favor and use a class somebody else has debugged. My two favorites for this are:

The piDog DataView is written from the ground up using a container control, so can do LOTS of things a Xojo Listbox cannot. Amazing product. The more I use it, the less I use a Xojo Listbox for anything.

Kem’s Data-on-Demand shareware is subclassed from a Xojo Listbox, but handles all the dirty work for you. Near instant loading and scrolling in any size recordset. And while on his download page, look over his other stuff too. Very useful stuff.


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.

I was asking @Kristin_Green actually. To help figure out why sqlite was so slow.

Data On Demand:
isn’t it included in ?

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()
  StartTime = System.Microseconds
    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 _
  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.