7

THE GOAL: Return as many rows as possible from a Remote method without hitting Heap limits.

We have a Visualforce page calling into a @RemoteAction method in Apex to return sObjects to the page. The catch is that the queries are very dynamic. The final SOQL statement is determined based upon how our customers have configured their app.

Other than the query portion, the remote method's Heap usage is extremely light.

The query is consistent except for one part (the customer's custom fields they select) SELECT <<10 standard fields>> + <<x Customer fields>> FROM sObjectName WHERE <<yada yada yada>> LIMIT <<what we want to determine>>

Upon testing, it is not a linear equation as to fields queried and heap. Data Type matters and some matter more than others. For example, we noticed that including an ID (Lookup field) in the query dramatically impacts Heap.

I am interested in understanding the inner workings of Apex to better understand how I can better predict Heap and get the most from my query.

Some things in anticipation of your responses.

  • Using OFFSET is not an option. We are looking to get rows of like 5000+ and OFFSET has limits of its own, so it's not an option.

  • We don't want to use the API so as to not use our customers API limits. It's a native app so we should use native functionality.

Thanks!


This is more of a question for the Salesforce Apex team, but wanted to start here so some knowledge can get documented for everyone to benefit from.

4

Since I am trying to return so many records, I am using a formula to try and predict Heap use. I use a test query of 200 records, measure heap and then calculate what my limit could be to stay under it. Seems to work well.

Turns out that there is a good amount of overhead in the query itself so a test query of 1 record is not good enough. Even 20 records has too much overhead. Based upon testing, I found 200 to be a reliable test query. Heap goes up by diminishing amounts for each additional record queried. 200 records in a test batch is a good indictor or actual heap usage, but is a conservative estimate. I still have ~10% of heap left afterwards as a safety buffer.

Here's my code to determine the limit for SOQL...

Integer origHeap = Limits.getHeapSize();
list<sObject> testQ = database.query(theQuery + ' LIMIT 200');
Integer newHeap = Limits.getHeapSize();
testQ = null;
Decimal dynLimitDec = (Limits.getLimitHeapSize() - Limits.getHeapSize()) / ((newHeap - origHeap)/200);
queryLimit = dynLimitDec.intValue();
2

Scott,

Here's a odd ball idea.

Using a chunking solution like greenstork suggested, but this time intentionally chunk them at a set iteration size. Say, 200 records. Immediately transfer the records to HTML5 local storage. Keeping the controllers' records Transient in the controller, or in the HTML5 LocalStorage you should be able to keep the heap down.

  • I'm really trying to max record counts. Depending upon how many fields I include, I get between 7000 and 12000 records in a single query and its FAST! Local storage is an interesting idea. – hemmeter Mar 27 '13 at 2:41
0

What about creating Visualforce pages that are readOnly? Are you needing to call email methods or conduct other CRUD DMLs on these pages?

  • It occurred to me that you may be doing this already, I think I remember talking to Jason about this. Hence your heap question, being the key limit. – greenstork Mar 26 '13 at 22:27
  • If we could make things dynamically read-only, we'd do that. But Visualforce is an all or nothing proposition. It's not viable to make it a read-only thing. – hemmeter Mar 27 '13 at 0:03
  • We edit custom settings mostly, but have a few places a user can save data to the page. We don't bind those records directly and do saves via other Remote methods. Wonder how that would work. Hmmm – hemmeter Mar 27 '13 at 0:05
0

Another idea... How about putting your SOQL statement into a do/while loop and chunking it, checking heap and number of SOQLs via limits methods in each loop. You would be limited to the number of SOQLs on a VF page of 100 but this might allow you to get farther along.

Another option might be rapid polling within your Javascript, thereby chunking out your queries into separate transactions. This would require you to know where you left off and I haven't really thought about how to best implement that.

  • 1
    We can get the most data by sending sObjects directly back to the page. We never even loop! If we did, we'd hit script statement limits well before we hit Heap ones. We used to loop. No longer! – hemmeter Mar 27 '13 at 0:04
0

I think one could predetermine the maximum size of each data type or dynamically using getByteLength method of Schema.DescribeFieldResult but this would get tricky as the heap size will have some other overhead as you mentioned.

But since you are using remoting I think you could query in smaller batches serially and add your results to a javascript array.

  • I probably won't do getByteLength, but someone on the Apex team suggested I check heap, query 1 record, check heap and compare the difference. Use that as a guide for maxing my LIMIT. My goal is more in the area of 8000-10000 records in a single query. – hemmeter Mar 27 '13 at 2:43
  • At that count, you can't really batch it because there's no good way of knowing where you left off. The OFFSET parameter maxes out at counts way below that. – hemmeter Mar 27 '13 at 2:44
  • Yes, you could sample your data to find the average heap size occupied by a record, the sample size would really depend on the number of records you got, querying 1 record wouldn't suffice in your case. – manubkk Mar 27 '13 at 6:15
  • Or you could always use a WHERE/ORDER BY clause instead of OFFSET to query in chunks – manubkk Mar 27 '13 at 6:16
  • I posted the answer that seems to work. Sampling 200 records has been reasonably reliable in basic tests. I think that will work for now. If we need to get even more data, we'll have to figure a way toi grab chunks of data. – hemmeter Mar 27 '13 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.