The desktop app that I’m building for my client uses a file-based SQLite database and one window is set up to list all Customers… of which there are currently over 3000. The window opens, a query is performed on the Customer table and the resulting RowSet is used to populate the listbox.
There is an unacceptably long delay when opening this window while the user waits for the list of customers to appear. I hate to think what that delay will become if they are even more successful as a company.
After doing some reading here, it seems that this issue is not uncommon.
Lazy Loading is often sited as the solution. The example that ships with Xojo illustrates what can happen to a ListBox when 100,000 rows of simple data are added without paging them in sync with a ScrollBar control… but even with this solution, other issues are raised such as sorting, column totals and bulk edit options. What if the user clicks a column header? What if the user chooses EditSelectAll?
Pagination is popular for web applications but I’ve never seen it in a desktop app so I’m not sure if this would be a good idea. It would also suffer from the same issues as Lazy Loading.
Another idea that I had was changing the nature of the list from a filtered list to a list of results that are returned from a given search. Instead of opening the window to a full list that could then be filtered down to the desired few, it would present an empty list that would only be populated once the user had provided some criteria. Google doesn’t show you a list of all websites and then filter it down as you type. It waits until you ask for something and then produces a list of search results that match. This seems a bit weird but removing any ability for the user to SelectAll (or at least warning the user against it) might be a workable solution.
My question is, what are folks here doing to solve this problem when it comes up in their own applications? Am I missing something and doing it all wrong? Is there a magic plugin that costs more than what I paid for Xojo that makes this problem go away? Or is Xojo simply a bad choice when developing applications that work with large data sets?
One thing to do and that gives already a big time gain is :
Before you load the data in the ListBox
First disable the vertical scrollbar ListBox.ScrollBarVertical = False
Then load the data into the ListBox ListBox.AddRow(…)
Then enable the vertical scrollbar again. ListBox.ScrollBarVertical = True
In my case I can load more than 14k rows into a listbox with no visible delay. But I only put two columns of data in, initially (and that may be because I need to put an image in those columns). Then I put a rowtag on each row with enough information for the CellTextPaint event to put in the other five of six columns later. Then as I scroll the listbox, the paint event does most of the work at that time (i.e., some time after the initial load). It will only fire for rows that are visible. I think you also have to put at least one char in column 1 for this to work properly (or maybe just a celltag).
I have some tables with almost 1 million rows, quite same speed.
I made a containercontrol with a listbox and 2 scrollbars. took me 2-3 years to make it work as I want but it was really worth it.
Xojo is not a bad choice, you will have the same problems with any other language.
you must deal with paginate, filters, searches, selectall, everything.
there are some plugins (I think of pidog at the moment) that have this implemented. but it cost $.
certainly less than making it yourself. again I don’t regret building it and the time it took me.
There also seems to be an issue in the included example with using the mouse scroll wheel. Despite turning off the vertical scrollbar on the listbox, the scroll wheel on the mouse still scrolls down to the end of the loaded list while the ScrollBar control allows scrolling through the entire list, loading new data as needed. Is there a solution to this dual scroll bar situation?
What are you putting into the RowTag? A custom class would allow you to create and store an object that would provide the data but this might cost too much overhead since the variant would need to be cast each time to access the data within. A dictionary? An array?
a containercontrol with a listbox and 2 scrollbars
and lots of code…
didn’t mention but it deals with sqlite or remote postgres database the same way.
first you have to make a list with invisible pagination, working independently of the database engine.
then when it works, make the sorts (on headers) and filters.
Are you saying that you load the list into memory and then refresh the listbox from there instead of getting it from the db every time? I believe that is what the lazy loading example does and there is still a significant delay when displaying the list of 100,000 rows without lazy loading.
Firstly, I have programmed such apps as well, means apps opening with a large listbox, but mainly because it was requested by a customer. So I am not questioning that you do need this approach.
However I’m trying to limit what is showing (at least at the beginning). For instance the 300 most recent customers, or the customers with open tickets, or the customers with the highest revenue.
I do know that customers like to scroll through large lists, but it is usually not even very productive for them. Think of large ERP systems like SAP and big companies like FedEx or Walmart … no one would hopefully every have the idea wanting to scroll through all their customers ;-).
Again I fully understand that there are needs for showing very long lists, but in your specific case of showing customers you might find a better approach how to design your UI.
I have a class with the necessary properties, so an instance of that. The Paint Event is supplied with the row number, so from the RowTag I get the data I need to get the info from the database for that row.
a) Pidog Dataview (https://www.pidog.com/wp/). 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.
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 ?
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.