Monthly Archives: November 2014

SOQL Query Optimization

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, from OpportunityLineItems) 
     from Opportunity where closedate >= THIS_QUARTER and ID IN 
       (select opportunityId from opportunityLineItem 
          where = '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:


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:


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.

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:


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.

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.

SFDC KB article (using Developer Console)
Using REST explain

S2S (Salesforce-to-Salesforce) Adventures

To paraphrase talk radio – “long-time thinking about, first-time actually doing it”.

An application came up to implement S2S between my org and another org at the same client. Something seemingly simple – just forward Accounts and Opportunities to the target org. The problem statement called for automatic forwarding / automatic acceptance, no user-initiated forwarding.

Several gotchas were uncovered and I’ll keep a running list here. Most resolved through Google searches and Developer Console investigation.

No upserting of PartnerNetworkRecordConnection (PNRC)

You might run across this blog post with a helpful code example. Unfortunately, the sample uses the upsert DML operation. This is great if the PNRC is being inserted but not if you are trying to update one. Stick to using DML insert and avoid trying to modify an existing PNRC. See SFDC doc.

Don’t try and share an Opportunity before sharing its Account

If you use APEX to share an Opportunity before the parent Account is shared, the DML insert of PartnerNetworkRecordConnection will fail with an exception ‘Invalid status for partner network operation’.

You can share an Opportunity without specifying a value for ParentRecordId. In this situation, the Opportunity will forward and be accepted as an orphan (no parent Account). Yes, the schema allows for this but most orgs probably have validation rules to prevent this from happening. This can be a workaround (with trigger on the target org) for the multiple Lookups on Account problem (see below).

Deleting a PNRC doesn’t actually delete it

If you go into Developer Console and query on PartnerNetworkRecordConnection, then delete selected records through the DC user interface, the records don’t actually delete, they have their status changed to Inactive.

INVALID_PARTNER_NETWORK_STATUS invalid status for partner network operation

  • Even if you have an active connection to the remote org, you need to publish the sObjects you insert in your code/testmethods and, the remote org has to subscribe to those sObjects.
  • If you are sharing a child record (like an Opportunity) and the parent record has not yet been accepted, this error ensues. In testmethods, unless using SeeAllData=true and a queryable parent record successfully shared, there is no way to get around this error except to ignore it.

It is impossible to auto-accept (without coding) an Opportunity if it has two or more lookup fields to Account

This is a weakness of the PNRC schema wherein you can’t specify the relationshipname used for the ParentRecordId field. Since the target SFDC org has no way of knowing which relationship to instantiate, it tosses the decision into the target user’s lap. The pending record is locatable only through the Opportunity tab, “Opportunities from Connections” section:


If you’ve overridden your Opportunities tab page (like I had in my dev org), then you’ll need to add a button or link to get you to Yep – the fcf parameter is all zeroes.

By the way, the SFDC Help on this is here.

The only way I could think of to resolve this problem was to do the following:

  1. Publish the Opportunity.accountId field
  2. On the Subscribing (target) org, map that field to a String field called Partner_Oppo_AccountId__c
  3. Don’t include the ParentRecordId field when, on the publishing org, you create a PNRC for the Opportunity
  4. On the subscribing (target) org, create a before insert trigger that examines the value of Partner_Oppo_AccountId__c, and looks for the corresponding PNRC record with the same PartnerRecordId and also matches the PNRC ConnectionId to the triggered object’s ConnectionReceivedId. In the row you find, take the LocalRecordId and assign it to the triggered Opportunity’s accountId field. Then, when the trigger ends, the Sobject has the proper relationship. Now, I didn’t control the target org so I haven’t had a chance to test this.

On the target org, the running user is called Connection User and it has some quirks

  • This user can’t be queried in the User sObject
  • If you need to look at a debug log, you’ll need to monitor Connection User but it can’t be selected in the lookup for Add Users to Debug Log page. To resolve this, see this tip
  • If you need to know in Apex if some user is the Connection User, use the Apex below. See also
   public Boolean isRunningUserConnectionUser() {
      return UserInfo.getUserType() == 'PartnerNetwork' && 
           UserInfo.getName() == 'Connection User'; // localize appropriately

Don’t share a record back to the org that shared it to you

You’ll need to test to see if the object to be shared has the same ConnectionReceivedId as the connection you intend to share (forward) the record to. A good example of this is covered in this very helpful SFDC best practices guide.

You can’t add the External Sharing relatedlist to a VF page

You might think you could with:

<apex:relatedList list="PartnerNetworkRecordConnection"/>

But you can’t. This special related list, like field histories, either has to be rebuilt with a custom component or you need to let the user escape to a standard page layout.

You can only establish S2S connections between sandbox-sandbox, PROD-PROD, Dev Edition-Dev Edition or PROD<>Dev Edition
This makes deploying Apex code tricky from your sandbox to Production if you have testmethods that cover your S2S logic – as they will require an active PartnerNetworkConnection in order to try and insert any PartnerNetworkRecordConnection . You’ll either have to:

  • Establish a PROD-PROD S2S connection with some friendly PROD org
  • Establish a PROD-DevEdition S2S connection with a captive Dev Edition org

Both options aren’t great – the remote PROD org might not be ready when you are and it is not best practice to have a PROD<->Dev Edition connection, especially as it can share records!

See Also
S2S Stackexchange question on test isolation