function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
d3developerd3developer 

10000 Record Limit Revisited

As far as I can tell, there is no way to natively iterate through more than 10,000 records without using an @future method or an action poller from a Visualforce page. There is a page documenting a for loop which uses QueryMore but as far as I can tell that does not actually work with > 10,000 records.

 

So, if I want to construct a json String including my 40,000 records what should do and output it to a Visualforce page what should I do?

 

It seems I have the following options;

 

(1) Asynchronously construct a Blob with a list of Strings as each reaches the maximum String size. The problem then? No way to save the blob...

 

(2) Create a text file via APEX that is not dependent on a VF page to store the data? But I don't think you can do this via a VF page (cannot be generated asyncronously) or text file (no idea how to do this)...

 

(3) Export the information to another webservice, convert it into a text file, bring it back in as a Static Resource, read the contents, in from the text file (seems possible).

 

(4) Create an action poller which gradually brings in the information I need for my page (will not be available on load and probably will require multiple rerenders which could make things ugly).

 

 

Anyone have more and/or better ideas or care to recommend one of the above?  Sample code also greately aprpeciated....

 

SuperfellSuperfell

Really, what are you going to do with 40,000 records on a single VF page ?

d3developerd3developer

 

The critical client requirement is in a web-to-lead form that provides autocomplete where a student will type in the name of the school they currently attend (this autocomplete implementation is a part of my open sourced jQuery for Salesforce library).

The database of schools includes 40+ thousand records. 

 

 

Beyond this, I would like to provide live client side searching and pagination for large datasets.

 

 

Abhinav GuptaAbhinav Gupta

Not sure about other such requirements, but for Autocomplete, we can make the widget ajaxified. i.e. ajax calls will go after typing first 2-3 chars and will keep on going like that. 

 

You can implement your own ajax inside visualforce for speed reasons. 

SuperfellSuperfell

Hmmm, have you prototyped this yet? my gut feel is that shipping all 40k records to the client is a significant cost, both in time/bandwidth to ship the data, and in processing time at the client. perhaps something where you do a search after the first few chars, or break the dataset up into smaller chunks might work better.

nnewbold-sfdcnnewbold-sfdc

Few things:

You may be able to work around this using a query locator and a StandardSetController.  You're likely to hit viewstate limits, though (128k).  The new viewstate inspector can help you out.

 

Working with 10,000+ records seems a little unfriendly.  For example, when was the last time you really went past the fifth page on Google before retrying your search?

 

Autocomplete should be even shorter.  If you're pulling 10k records into autocomplete, the user experience is probably going to be really terrible in just about any system.

 

Also, if you're implementing search, make sure you're using SOSL.  For something like autocomplete, you'll probably get better results without hitting selectivity limits.

Abhinav GuptaAbhinav Gupta

I have not done exactly the same, but implemented a overlayed lookup using custom vf ajax, it works super fast.

 

Here are a few pointers

 

YUI XHR Autocomplete : http://developer.yahoo.com/yui/examples/autocomplete/ac_basic_xhr.html

Jquery Autocomplete has a URL option for "source" : http://docs.jquery.com/UI/Autocomplete#options

 

These all will rarely work with VF's native ajax so here is how to build your own Ajax front in visualforce

http://www.tgerm.com/2010/02/visualforce-salesforce-jquery-ajax-how.html

 

d3developerd3developer

I'm not quite sure which would be fastest. I've seen jQuery powered datatables with upwards of 10K records and they seemed to have virtually no load time. My implementation requires only name + id for the autocomplete as well, so this implementation would likely have significantly less data than the datatable I saw. So my gut is that I could load everything with the page w/o chunking everything up (and if so, that would be my preference instead of ajax queries). 

 

I'll prototype it tomorrow and see what I come up with.

 

Also, thanks for that Json processing code Abinhav. Will be useful at some point if not this time around.

d3developerd3developer

Woud anyone reading this thread mind raising the storage limit for the dev org I am testing this functionality in so I can test w/ an appropriately large data set?

d3developerd3developer

Well, I noticed one somewhat bizarre thing. The load time in ms per record increased rather substantially for increased numbers of records:

 

Number of Records LoadedLoading Time in ms per record
  
90004.034229829
90002.358301845
90003.61002445
80003.228307077
80003.003250813
70003.094741355
70003.28308088
60002.449149717
60002.162720907
40001.776388194
40002.756878439
30001.382921948
30001.732488326
20001.755755756
20001.436436436
ca_peterson_oldca_peterson_old

Dumb question, isn't this a job for an onChange handler that runs a SOSL search based on the first 2-3 characters the user typed? SOSL is generally pretty snappy, I've done autocompletes with it before and never had a real problem.

d3developerd3developer

As far as I can tell SOSL is or may be only marginally faster for searches over large datasets and occasionally drops records from the set so that you may miss results. That's reason enough not to use it although I really would like to benchmark SOQL vs. SOSL at some point.

 

For this specific use case, you are right, it is easy enough to run a sosl/soql query for the autocomplete. I did this and wrote up the results (http://bit.ly/9XgvQs).

 

For the other use case I mention, I still haven't identified a good way to bring back more than 10K records. This may be a moot point because of the speed. From what I have observed from my own tests, Salesforce chunks up when attempting to return large record sets. This is not the case with Javascript / MySQL which I have seen load ~12K records into a datatable in under a second. (as a point for comparision, my Salesforce attempt took upwards of 30 seconds).

hemmhemm

Since there are 40k to choose from, I imagine it's ok if there is a small lag until a record makes it into that 40k list?  If so, maybe Batch Apex could help?

 

Imagine something scheduled daily or several times daily.  It would run through all the records and build a JSON string within the batch apex run. Make sure to use Database.Stateful in the Batch Apex class.

 

In the finish method of the batch apex class, you'd have a monster JSON string.  Now you have to find a place to put it so you can pull it directly into a VF page.  Some ideas...

 

1) Have a Temp__c object with a bunch of long text fields on it.  Create a record there and fill in the long text fields. Get the ID of that to the VF page or setup the record so the VF page always knows how to find it.  Pull in the JSON.

 

Batch Apex sample code:

 

// Calculate the number of chunks to produce
String json = //the big JSON string from earlier in the batch class
Integer length = json.length();
Integer chunkSize = 32000; // long text fields are 32k
Integer chunks = Decimal.valueOf(length / chunksize).intValue() + 1;

if (chunks > 0) {

Temp__c t = new Temp__c();
t.Name = 'Auto Complete Data');

// Correct the chunks calc for a string of the exact chunk size
if (math.mod(length, chunksize) == 0){chunks = math.max(0, chunks - 1);}
				
if (chunks >= 1){ t.Long_Text_1__c = json.substring(chunkSize * 0, math.min(chunkSize * 1, length)); }

if (chunks >= 2){ t.Long_Text_2__c = json.substring(chunkSize * 1, math.min(chunkSize * 2, length)); }

if (chunks >= 3){ t.Long_Text_3__c = json.substring(chunkSize * 2, math.min(chunkSize * 3, length)); }

if (chunks >= 4){ t.Long_Text_4__c = json.substring(chunkSize * 3, math.min(chunkSize * 4, length)); }

if (chunks >= 5){ t.Long_Text_5__c = json.substring(chunkSize * 4, math.min(chunkSize * 5, length)); }

insert t;

}

 

2) Use an HTTP call and send it out to an external web server that creates a TXT file on it.  In your VF page, download the TXT file and turn the JSON into JavaScript objects

 

3) Somehow get it to be a Static Resource that you pull into your VF page and turn the JSON into JavaScript objects

 

 

d3developerd3developer

Great suggestions. Yes, some lag time would acceptable although it would be ideal if I could write to the file a bit at time so that some records (if not all) records are shown.

 

(1) Thought of this but I don't think folks want to deploy an extra custom object.

 

(2) Didn't think of this one. Quite likely the best option were I to need to load everything client side in the original example I gave. Not so much when I can't plan ahead.

 

(3) Thought of this and like this one a lot but don't know how to write to a Static Resource using APEX. Do you?

hemmhemm

 


d3developer wrote:

Great suggestions. Yes, some lag time would acceptable although it would be ideal if I could write to the file a bit at time so that some records (if not all) records are shown.

 

(1) Thought of this but I don't think folks want to deploy an extra custom object.

 

(2) Didn't think of this one. Quite likely the best option were I to need to load everything client side in the original example I gave. Not so much when I can't plan ahead.

 

(3) Thought of this and like this one a lot but don't know how to write to a Static Resource using APEX. Do you?


 

regarding (3), no I don't, but maybe mixing #2 and #3 would work.  I haven't personally worked with the meta data API, but you could look into calling out to an external web server and then using the metadata API to update/deploy the static resource back into your main org?  I'd love to see the code for this once you get this working! ;)

 

Apex doesn't get the metadata API, but you can use the PHP Toolkit or some other one to use it, I think.

 

It's a thought.  This would actually be a good use case for VMForce to call out to a Java server that does this for you using the meta data API!

d3developerd3developer

Heh, I'd love see the code if I got it working that way too...  Certainly possible.