Showing a list of 100,000 records?

Classic Servoy
1

We’re in the process of moving a very, very large database installation from FMP to Servoy. A few important tables can have upwards of 100k records.

In most of these tables, our users are used to having a layout which uses a basic FileMaker table or list view. They can scroll quickly through even very large lists by dragging the scroll wheel, etc.

I see a few ways of attempting to replicate this in Servoy, but none of them are really workable.

  1. Load the ENTIRE table, and use a standard Servoy List/Table view. I’m not exactly sure the command to use, but I have a feeling this would take forever, scrolling performance would be abominably slow, and Servoy could well choke on the data returned by the SQL query the command generates.

  2. Use the ever popular SQL-to-HTML-Area technique. In our setup it takes between 4 and 5 seconds to load 1k records. So loading 100k records would take far, far too long, even if our Servoy/Postgres setup doesn’t choke on query sizes larger than 18 or 20k.

  3. Set up a layout where users can “page” through data. Either the next page could be the 200 you’d get in a list/table view, or perhaps we could page in 1k increments using SQL/HTML. Still, not a great user experience.

  4. Implement some method of filtering so that a user has to choose what records will be listed based on criteria in each record, or via relations. Or both. This might entail a tricky SQL query generator, but if users can narrow down a found set to, say, 5k records, it might be workable. Or it might not be.

So I’m stymied. Every technique I can think of seems to be unworkable, or offer a poor user experience, or both.

I’m sure I’m not the first FMP/Servoy migrator who’s run up against this problem. I’ve searched the forum and I haven’t been able to find a thread which touches on this more than peripherally.

Has anyone come up with a brilliant solution I can study?

Thanks very, very much.

Eric Taub

2

There is not really the brilliant approach, but I don’t really see the problem. In our solution the main table has almost 100.000 records with heavy calcs and I show that in a table view without problems.

But I wonder what the usage is of scrolling though 100.000 records? What do your users do with that? Who is looking through 100.000 records? I offer a simple search and that is what my users use.

3

Ummmmm… here’s what has worked on various FMP to Servoy projects that I have completed:

  1. Tell users something along these lines:
    a) THIS IS NOT FILEMAKER. Things will change.
    b) You cannot scroll through 100,000 records anymore. Do a “Find” instead.

  2. They will bitch, cry, scream - until they use the beta. Once they use the beta - they’ll love it.

There is just no efficient way to bring down 100,000 keys to a client computer from a SQL backend.

Sorry.

One thing that does seem to help is to show the number of records in the table and foundset - rather than just “200+”… but even then, NO ONE really scrolls through 100,000 of ANYTHING.

On Google - do they even get past the first 10 pages of search results? NO, of course not. They perform a more refined search.

4

Thanks, Bob. I was afraid it might be something like that.

I’ll remember to bring my chair, whip and pistol when I break the news to the users. Better yet, I’ll have my boss break the news to the users!

5

I have developed in FileMaker for more than 15 years and never met a user who scrolled through 100K records. Just because that little book was there doesn’t mean it really meant anything to anyone.

6

Yeah, well - people always resist change (especially end users).

Now would be a really good time to come up with some way to navigate that is even BETTER and more relevant.

Ideas:

  1. “Google” style search where you can parse the data and search for them in the relevant table(s);

  2. Drop down menu of “filters” that will save them from constantly finding data;

  3. #2 - but with an option to “reduce” the foundset (do a find within the found results)

  4. Find out why the hell they were using the scroll wheel to navigate through records in the first place. Maybe there was some sort of logic - and find out what it is - then use Servoy’s WAY BETTER control of the UI to make it even cooler.

7

I think we’ll end up doing something that takes a little bit of the “filtering” approach and spices it with a dash of “deal with it.”

8

Patrick said,

I have developed in FileMaker for more than 15 years and never met a user who scrolled through 100K records.

Indeed. However, users may very well want to peruse a “reasonable” number of records that happens to be > 200. (For whatever reason… that is their business).

So… why not make it easy to filter or find so as to narrow the found set, and then make it seem as much as possible like those 350 (or 3500) records really ARE there? One could use an approach similar to the one that I have been working through for “going to selected (high) record number” http://forum.servoy.com/viewtopic.php?t=5973 after finds & filtering, so that the keys are brought down and the records are actually available without weirdness in the scrollbar, etc. Additionally, one could test for the size of the found set, and if it is large enough to cause an irritating delay in looping through and bringing down the keys, then warn the user and let them choose to experience a delay, not load all keys, or refine their search.

It is extra work, but seems easier than looking at them and saying, “Deal with it”. But then, I am developing a product that I hope people (who have choices in the matter of purchase) will find real easy and intuitive to use. If I were doing a migration where, essentially, the purchase decision had been made already, it might be diifferent…

Jim