Trying to LIMIT a SOQL Statement based upon projected Heap size

Question

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.

Answer 1

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();

Answer 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.

Answer 3

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

Answer 4

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.

Answer 5

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.