You need to sign in to do that
Don't have an account?

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....
Really, what are you going to do with 40,000 records on a single VF page ?
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.
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.
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.
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.
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
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.
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?
Well, I noticed one somewhat bizarre thing. The load time in ms per record increased rather substantially for increased numbers of records:
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.
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).
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:
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
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!
Heh, I'd love see the code if I got it working that way too... Certainly possible.