Table limits record retrieval to 11,000 records in a table that has more than that

  • 1
  • Idea
  • Updated 2 years ago
  • Under Consideration
I have a table that will have millions of rows.  I built a stock Tab page for the object and started clicking "load more."  Unfortunately, when the record count got to 11,000, the load more button disappeared and there was no indication that more records were available.

I can understand that there are some reasons why limiting the number of records in memory in a model would be desirable.  That said, having no indication that there is more could lead to business decisions being made (e.g. think Inventory tables) that would be wrong (e.g. oh, we must not have any more widgets, time to go buy some).

Questions:
1) Why did it stop allowing rows to be retrieved
2) Why did it not give any indication that there are more records even if it wouldn't allow more to be retrieved?

Steps to reproduce
1) Put a lot of records in any object (e.g. hundred thousand or so)
2) Build a stock tab page for the object
3) Preview page
4) click load more

Expected Behavior
All records in object are available for loading OR at least some indication that there is still more out there

Actual Behavior
Only 11,000 records are available and NO indication that there are more

Thanks!
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb

Posted 2 years ago

  • 1
Photo of JD Bell

JD Bell, Senior Product Engineer

  • 2,996 Points 2k badge 2x thumb
Skuid uses SOQL's OFFSET keyword to handle pagination, but there are limits on how many records we can offset. These limits are mentioned in Skuid's Table Export documentation.

I agree, however, that we could do a better job reporting when we've hit those limits. Obviously, Skuid is a presentation layer, and so is ill-suited for arbitrarily large ETL-like tasks. If your users need to download more than the current limits, it'd be better to use a tool with higher memory and computational limits than that of a browser.

We'll look into better error reporting in these situations.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Ben -

First things first - This is great information, thank you (and JD from earlier) for providing the details behind how Skuid works and also why the decisions were made the way they were.  The picture is much clearer now and reading through information vs. digging through code to figure this out (server side wouldn't even be possible) makes the "learning curve" way more efficient.  To re-iterate my comment to JD above, not trying to make Skuid an ETL tool, just trying to leverage as much as possible from it.  Keeping users in "Skuid" is the preference vs taking to other tools/pages.  Understanding how Skuid functions is critical to accomplish that goal - getting the most from it as possible while while also not pushing it beyond it's intended limits.

Secondly, I'm glad you feel that the lack of messaging/warning regarding truncated data is a bug (I agree it's a bug and not an enhancement/idea).  To that end, I've created a new issue to track that since this thread has taken multiple tangents.

Thirdly, appreciate the PK Chunking pointer, very cool stuff that very well (albeit unfortunately) might come in handy for some of my use cases.

Fourthly (is that a word? lol), You are correct, Skuid exports UI fields, templates, etc.  Also, there's external data sources to consider so the way Skuid handles exports makes perfect sense.  

And lastly, a couple of questions - 

1) Agreed that modern browsers handle large datasets well (as long as everything isn't sitting in the DOM which Skuid does a great job of avoiding).  Since Skuid uses Set Controllers most of the time, why the 10,000 row limit for model data then?  As long as the page size is small enough, why not let users pull data in a paged manner to the very end?  I believe this limit was in-place prior to UI Only formula fields so the only thing I can think of is protecting against exporting huge quantities of data (maybe export just gets disabled after 10,000 rows are in model).  Am I not connecting enough dots on this?

2) I think I'm getting 11,000 because my initial model row count is 5000, wasn't using an order by (as you suspected)  and therefore it would go from 5,000 to 7,000 to 9,000 and finally 11,000.  Adding an Order by effected it to go from 5,000 to 6,000 then 8,000 and finally 10,000.  Unclear why the order by changed the first page to be only 1,000 instead of 2,000.  Thoughts?  Also, should the unordered scenario stop at 10,000 or be allowed to go to 11,000?

Thank you!
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Barry,

1. This 10,000 limit is not a Skuid enforced limit, it's a Salesforce limit. We would love it if the Standard Set Controller could be initialized with QueryLocators with more than 10,000 rows. The only way around this is with PK Chunking or some other way to change the conditions. You could have a condition that asked for records with names starting with A-D, then move on through E-G and so on. 

2.

Scenario 1: No Order By

a. SOQL Loads in 5000 records

b. Skuid Limits Standard Set Controller Page Size to 2000 and Page Number to 3 (Math.Ceil(Model.data.length/pageSize)). Standard Set Controller's default ordering is different than SOQL, so we get 2000 new records that were not previously in the model.

c. We continue to get 2000 new records with each batch until pages 3,4, and 5 are loaded. If we had loaded in pages 1 and 2, we could have loaded a total of 15,000 records if Skuid didn't find duplicates. 5,000 from SOQL and 10,000 from the Standard Set Controller. I've never researched what the exact default ordering for SOQL and Standard Set Controllers are, but I do know that they are different.

Scenario 2: Order By Included

a. SOQL Loads in 5000 records

b. Skuid Limits Standard Set Controller Page Size to 2000 and Page Number to 3 (Math.Ceil(Model.data.length/pageSize)). Since the ordering is the same, we get records 4,000 through 6,000. 1,000 of these records are duplicates that are already in the model. Skuid simply updates the 1,000 duplicate records with possible new data, and adds in the new 1,000 records.

c. The rest of the loads are pretty straightforward because 2,000 new records are added each load until the 10,000 limit is reached.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Ben -

Thanks again for quick reply and info.

#1 - Ok, I see the 10,000 limit with Standard Set Controllers (https://developer.salesforce.com/page/Paginating_Data_for_Force.com_Applications).  

Question - Why does a standard SFDC Tab page allow for more than 10,000 records to be returned (page size 200)?  What technique is standard tab page using?

#2 - Makes sense now.  The dot I wasn't connecting on this one was the first call is SOQL, the rest are controller methods but based on what the math says the next page should be.

Thanks!
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Unfortunately, Salesforce often "cheats" and they don't use their own apis in their standard pages. Maybe something like queryMore could get us to more than 10,000 records, but we haven't explored that yet.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Never liked cheaters lol

I created a new issue to explore adding the functionality of going beyond 10,000 rows.  Thanks again for all your help!