Dealing with HUGE datatables while keeping a listbox manageable

Here’s one for the super brains and a good idea for a web tutorial (hint, hint, @Paul Lefebvre )

I’ve started working on a new project that may interface with datatables that contain literally millions of records. I need to provide a ListBox for the user to interact with that has the potential of accessing the entire datatable while scrolling.

I’ve been trying to wrap my head (and the Xojo IDE) around a scenario where the listbox contains the number of currently visible rows and 100 previous and 100 following records with the cached records sliding as the user scrolls the listbox. I see this as the way to keep both the UI responsive and the database accesses reasonably quick.

So far, the best that I have achieved is to crash my app with some tough to sort recursion nightmares (at least it crashes quickly :slight_smile: ).

Does anyone have a model they could share on this? Paul - any tutorials?

Assuming you could get it to work, is anyone going to actually scroll through millions of records? Why not offer some sorting and categories to narrow things down first?

I don’t have specific code examples… but basically look at the LIMIT (or equiv) function in what ever database engine you are using
Read in X number of records (a few hundred at most), when the user scrolls toward the upper limit of what you have in cache, alter the limit and read more records, adjusting the listbox so it “looks” like it didn’t change (just the data behind it, stored in a dictionary perhaps)… .the same for when they approach the upper limit.

This way you never have more than a few hundred records on call at a given moment, and page the data in as required.

A team I am on has a website (not Xojo built) that does exactly this. The user has access to 1.6 MILLION records, but rarely looks at more than a hundred in a given day.

Tim,

I’d suggest having a look at Kem’s Data-On-Demand ListBox . We’ve used in in a project that sounds similar - maybe not millions in our case, but hundreds of thousands of records - and it’s worked out very nicely on both Mac and Windows builds…

You should also check out Jim’s Dataview

@Peter Truskier - the embarrassing thing here is that I believe that I actually have Kem’s DoD Listbox class.

@Richard Berglund - I’m also a beta tester for the piDog Scrolling Canvas (which includes the DataView class).

Sometimes you discover things where you least expect them…

I did do a webinar on how to use paging with a WebListBox to handle lots of rows from a DB.

This is in the Good Guy ListBox webinar, which was also a session I presented at XDC last year:

http://youtu.be/1o39SA5sA8o?t=45m

http://documentation.xojo.com/index.php/Videos

What we did was do Paging of data so you limit the about of data being put into the listbox. This works in both desktop and web and so far, we’ve found that users are used to it. We have a training video for this for that covers both web and desktop. I think the training video is roughly an hour.

[quote=163234:@Tim Jones]Here’s one for the super brains and a good idea for a web tutorial (hint, hint, @Paul Lefebvre )

I’ve started working on a new project that may interface with datatables that contain literally millions of records. I need to provide a ListBox for the user to interact with that has the potential of accessing the entire datatable while scrolling.
[/quote]
Kems data on demand listbox ?