Category Archives: soql

REST SOQL OFFSET and LIMIT – 2000 records

Leave a reply

I recently ran into an issue with an Apex batchable class that executed a REST SOQL query performed from SFDC org A against SFDC org B

The batch Apex code in org A was written to handle REST responses that looked like this:

{"totalSize":12,
"done":true | false,
"nextRecordsUrl" : "/services/data/v30.0/query/01g8000001J2eXvAAJ-2000",

"records":[
{"attributes":{
"type" : "the sobject",
"url" : "/services/data/v30.0/sobjects/the sobject/the id"
},
"field0 in query" : "value of field 0",
"field1 in query" : "value of field1",
...},
next record ...
]
}

If "done" : false was present, then, once the iterator reached the end of the retrieved SObjects, it would do an HTTP Request against the value in nextRecordsUrl .

Hence, the batch program could retrieve a large number of records.

And your problem was what?

Turns out my query string used in the original REST GET was of this form:

select id,name from Opportunity where closeDate = THIS_QUARTER Limit 10000 OFFSET 0

Even though there were more than 2000 Opportunities in Org B and even though the coding for the batchable class’s iterator could deal with the queryMore logic, the total number of records retrieved was exactly 2000.

Analysis

Turns out, if you code OFFSET 0, the maximum value returned in the totalSize JSON property of the REST response body is 2000 – regardless of there being more than 2000 rows in the queried org.

Solution
As there was already logic to deal with queryMore(), I simply removed the OFFSET clause. I verified there was no logic to increment OFFSET and repeat the query.

Why was there queryMore() logic in the code in the first place – why not implement LIMIT and OFFSET per SFDC spec?

Turns out, if the retrieved payload from a given query exceeds the maximum transfer size per REST response, SFDC will break up the query results into segments, indicated by the presence of "done": false. These segments will be a maximum of 2000 records but could be (and in my case were – do to payload mass) fewer. The queryMore logic had to be added anyway. Once this was present, the OFFSET portion of the SOQL became superfluous and should have been removed everywhere. Unfortunately, a vestigial OFFSET 0 was present causing ther REST query to only retrieve 2000 rows before saying – I’m done.

Invalid Query Locator

Leave a reply

One of my users reported today that they were unable to display a page for a specific ‘company’. Upon investigation, the error was ‘Invalid Query Locator’ and was in a place in the code where such an error was unexpected.

Here was the cause and solution

Foo_c is the parent object, Bar__c is the child.

Foo__c f = [select id, 
             (select id, name, fld1__c, fld2__c, fld3__c 
                  from Bars__r order by name asc)
            from Foo__c where id = :someId];
for (Bar__c b: f.bars__r) 
    res.add(new MyInnerClass(b));

After the 60th Bar__c, the code threw an exception ‘Invalid Query Locator’.

So, why did this happen?

  • I knew from experience that the record in question had more than 60 children and that I had successfully viewed them in the past.
  • So, it must have been something introduced
  • I verified via SOQL in Workbench that there were in fact 70 children – and I could retrieve them all in Workbench

I noted that others had this issue and even SFDC Help had a specific knowledge article on this subject although it didn’t seem to apply to my use case as I was neither taking too long nor had any intermediate queries whilst processing the list of children Bar__c.

I recalled that I had added to the subquery some additional fields. And, those fields were of type Text Area (long). In this case, 32768 long even though most rows had less than 25 characters and in the use case in question, those fields were all null. I also noted that the class was written a long time ago and was at V16.0.

Looking for the easy way out, I tried updating the class version to V33.0. This had no effect.

Then, I tried to see if the field length of the Text Area (long) fields made a difference, even if there was no data that came anywhere close to the limits. So, I reduced the field sizes down to 4096 from 32768. This had some effect as I could process and display all 70 children without error. However, when I changed the test example to have 200 children, the Invalid Query Selector error recurred. Clearly, there is some calculation by SFDC as to when it will return the queryMore attribute based on the expected size of the SOQL result payload.

The fix

I rewrote the code to the following by eliminating the subquery:

Foo__c f = [select id from Foo__c where id = :someId];
for (Bar__c b: [select id, name, fld1__c, fld2__c, fld3__c 
                  from Bars__r where foo__c =: f.id order by name asc) 
    res.add(new MyInnerClass(b));

Now there is no subquery and when tested with 2000 children, there were no errors. Of course, it took two queries to resolve instead of one but as this was a VF controller doing only those queries, it was an easy penalty to accept.

SOQL Query Optimization

Leave a reply

Pardner, you sure got some ‘xplaining’ to do

In PROD, I had a daily REST query that executed against another SFDC org. It retrieved about 1000 or so Opportunities and had been executing just fine, night after night. But today, the Apex Jobs log showed ‘Read timed out’. In fact, three days’ worth – ouch.

First thought – timeout argument too low
Perhaps the timeout needed to be bumped up in the HTTPRequest call?

This wasn’t it, I was using this Apex to set the timeout:

hRqst.setTimeout(120000);  // max is 120 secs = 120000 ms

Second thought, the REST SOQL query needed optimization
Of course, I wanted to deny this possibility as the REST query had been running along just fine for weeks now. But what if the target (of the query) SFDC org had gotten much bigger and this was causing the query to slow down. After all, the target org (not managed by me) actually has hundreds of thousands of Opportunities but I thought I was using an index so why would there be a problem?

Let’s take a look at the query (simplified) for discussion purposes:

   select id, stageName, 
      (select  unitPrice, totalPrice, pricebookEntry.name from OpportunityLineItems) 
     from Opportunity where closedate >= THIS_QUARTER and ID IN 
       (select opportunityId from opportunityLineItem 
          where pricebookEntry.name = 'FOO' OR 
                (bar__c = 'BAR' and is_Active__c = true))

You can see that the query does a semi-join – it is looking for Opportunities where at least one Opportunity Product meets some criteria.

Now, distant genetic memory kicked in and I remembered that SFDC introduced Query Plan explains back in V30. I hadn’t had a reason to look at this as I wasn’t experiencing any performance issues until now.

Using SFDC Workbench REST Explorer, I logged into the target SFDC org (the one where the query executes). I created this REST GET:

 
/services/data/v32.0/query?explain=select+id+(select++unitPrice,+totalPrice,pricebookEntry.name+from+OpportunityLineItems)++from+Opportunity+where+closedate+>=+THIS_QUARTER+and+ID+IN+(select+opportunityId+from+opportunityLineItem++where+pricebookEntry.name+=+'FOO'+OR+(bar__c+=+'BAR'+and+is_active__c+=+true))

And here was the result – three possible plans, the first one has a relative cost of < 1.0 so it meets the 'selective query definition'. It will use an index. SoqlExplainInitial

So, although 0.826 isn’t great, there are over 500,000 records to consider. So why was the query timing out?

Third thought – The Explain feature doesn’t consider the semi-join portion
Since there are really two queries being executed and joined together, I hypothesized that the query on OpportunityLineItem wasn’t ‘explained’. So, I decided to see what that query would look like ‘explained’. Back to REST Explorer and I entered this:

/services/data/v32.0/query?explain=select+opportunityId+from+opportunityLineItem++where+(pricebookEntry.name+=+'FOO'+OR+(bar__c+=+'BAR'+and+is_active__c+=+true))

And the plan (only one) came back being a table scan of 2.1 million rows! Uh-oh. Here was the problem. As you might suspect, the culprit is the OR clause on non-indexed custom fields.
SoqlExplainProblemQuery

So, how to make this query use an index? I had naively thought (or perhaps it was epistemic arrogance) that SFDC would automatically exclude any OpportunityLineItem that was prior to the current quarter as specified by the semi-join’s left hand query; and I knew closeDate was indexed. Wrong!

So, I added into the semi joins right hand query a condition to only include OpportunityLineItem whose parent Opportunity’s closeDate was in the current quarter or beyond: 

 
/services/data/v32.0/query?explain=select+opportunityId+from+opportunityLineItem++where+opportunity.closeDate+>=+THIS_QUARTER+and+(pricebookEntry.name+=+'FOO'+OR+(bar__c+=+'BAR'+and+is_active__c+=+true))

SoqlExplainFinal
The explain’s relative cost for this optimization now falls below 1.0 and hence it too will use an index. The semi join will join the results of two index result sets.

Peace
I adjusted the daily job’s query using the properly selective semi-join right hand side and reran the daily job. No more timeout!

Update! – after writing all this, I learned I could access the Query Plan from the Developer Console. I tend to avoid using that on orgs I don’t control for fear of writing some anonymous Apex by mistake or using DML through the Query Editor results.

Resources
SFDC KB article (using Developer Console)
Using REST explain

Versioning Woes – No such column – Field is valid

Leave a reply

Here’s a tip to help resolve a System.QueryException ‘No such column’ when the field exists in the schema

Turns out I was generating a SOQL Select in class A using Schema.Describe to get all fields from OpportunityLineItem resulting in:

Select createdbyid, hasrevenueschedule, 
    description, isdeleted, quantity, systemmodstamp, 
    totalprice, hasschedule, createddate, subtotal, discount, 
    lastmodifiedbyid, currencyisocode, pricebookentryid, 
    lastmodifieddate, id, unitprice, servicedate, 
    listprice,  sortorder, hasquantityschedule,  
from OpportunityLineItem

Yet, when this query was executed by class B, i got an error System.QueryException ‘No such column subtotal’

‘subtotal’ is not a field I normally use in OpportunityLineItem so I wondered if it had been removed or added in the most recent SFDC Version (31). Nope.

Here was the issue:

  • Class A, that generated the SOQL from the Schema.Describe was at V27.0, recently upgraded from V13.0
  • Class B, that executed the generated SOQL from the Schema.Describe was at V16.0 (!)
  • Test Class T, that executed Class A and B was at V27.0

So, the Schema.describe in class A found all the fields known to SFDC version 27.0 which included OpportunityLineItem.subtotal but when executed by a much older class B (version 16), the subtotal field wasn’t part of the schema at that point in history so QueryException ensued.

Updating class B to V27.0 resolved the issue