Category Archives: REST

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.

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.

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

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

Salesforce to Salesforce Using REST – Part II

Salesforce to Salesforce Using REST – Part I

1 Reply

Here was the business problem:

Migrate Product2-PricebookEntry-Pricebook2 data from a sandbox to staging sandbox and then onto production.

My previous brute force solution was to use the Excel Connector, querying rows from the Pricebook2, PricebookEntry, and Product2 tables using a connection to the sandbox, then successively inserting rows into the target org while replacing ID fields in the upload PricebookEntry dataset to match the just uploaded Pricebook2 and Product2 datasets. (PricebookEntry is a junction record between Pricebook2 and Product2). This involved Excel VLOOKUP functions and lots of care to make sure that I didn’t reorder rows by accident. Doing this once wasn’t so bad but the business changes the product line regularly and I was finding this tedious.

Solution Outline

Why not build an admin VF page in my org that could read from my source dataset sandbox and insert rows into the target sandbox/prod org? To make this happen, the Visualforce controller code would need to be able to do SOQL and DML against a different org. The way to do this is by exploiting the SFDC REST API and Apex HTTP Callouts.

The SFDC REST API documentation is written assuming that the calling application is not a Salesforce org but there is no restriction that this be so.

Step 1 – Authentication

The relevant SFDC REST API authentication documentation is here. I chose the username-password method as I’m the admin and would use this to get authentication on the target orgs using my own username and passwords on those orgs. I also opted for OAuth 2.0 authentication as I wanted to try this rather than using sessionIds.

public with sharing class HttpRest {

	//	Class to handle HTTP Rest calls to other SFDC instances
	//
	//	[HTTP-00]	- Reached limit on callouts
	//	[HTTP-01]	- Unable to get OAuth 2.0 token from remote SFDC
	//	[HTTP-02]	- Error in SOQL REST query

	String	accessToken;					// OAuth 2.0 access token
	String	sfdcInstanceUrl;				// Endpoint URL for SFDC instance
						
	private HttpResponse send(String uri,String httpMethod) {
		return send(uri,httpMethod,null);
	}
		
	private HttpResponse send(String uri, String httpMethod, String body) {
		
		if (Limits.getCallouts() == Limits.getLimitCallouts())
			throw new MyException('[HTTP-00] Callout limit: ' + Limits.getCallouts() + ' reached. No more callouts permitted.');
		Http		h		= new Http();
		HttpRequest	hRqst	= new HttpRequest();
		hRqst.setEndpoint(uri);						// caller provides, this will be a REST resource
		hRqst.setMethod(httpMethod);				// caller provides
		hRqst.setTimeout(6000);	
		if (body != null) 
			hRqst.setBody(body);					// caller provides
		if (this.accessToken != null)				// REST requires using the token, once obtained for each request
			hRqst.setHeader('Authorization','Bearer ' + this.accessToken);
		return h.send(hRqst);					// make the callout
	}	
	//	----------------------------------------------------------------------
    //	authenticateByUserNamePassword		: Returns a map of <String,String> of the OAuth 2.0 access token; required before REST calls on SFDC instances can be made 
    //	----------------------------------------------------------------------
    public void authenticateByUserNamePassword(String consumerKey, String consumerSecret, String uName, String uPassword, Boolean isSandbox) {
    	// Reference documentation can be found in the REST API Guide, section: 'Understanding the Username-Password OAuth Authentication Flow'
    	// OAuth 2.0 token is obtained from endpoints:
    	//	PROD orgs	: https://login.salesforce.com/services/oauth2/token
    	//	SANDBOX orgs: https://test.salesforce.com/services/oauth2/token
    	
 		//	OAuth 2.0 access token contains these name/values:
 		//		access_token		: used in subsequent REST calls
 		//		instance_url		: to form the REST URI
 		//		id					: identifies end user
 		//		issued_at			: When signature was created
 		//		signature			: HMAC-SHA256 signature signed with private key - can be used to verify the instance_url	 

		String uri 			= 'https://' + (isSandbox ? 'test' : 'login') + '.salesforce.com/services/oauth2/token';
		String clientId 	= EncodingUtil.urlEncode(consumerKey,'UTF-8');
		String clientSecret = EncodingUtil.urlEncode(consumerSecret,'UTF-8');
		String username		= EncodingUtil.urlEncode(uName,'UTF-8');
		String password		= EncodingUtil.urlEncode(uPassword,'UTF-8');

		String body =	'grant_type=password&client_id=' + clientId + 
						'&client_secret=' + clientSecret +
						'&username=' + username + 
						'&password=' + password; 

		HttpResponse hRes = this.send(uri,'POST',body);
		if (hRes.getStatusCode() != 200) 
			throw new MyException('[HTTP-01] OAuth 2.0 access token request error. Verify username, password, consumer key, consumer secret, isSandbox?  StatusCode=' +
												 hRes.getStatusCode() + ' statusMsg=' + hRes.getStatus());
			
		
		System.debug(FlowControl.getLogLevel(),'response body =\n' + hRes.getBody());
		

		Map<String,String> res = (Map<String,String>) JSON.deserialize(hRes.getBody(),Map<String,String>.class);

		this.accessToken		= res.get('access_token');		// remember these for subsequent calls
		this.sfdcInstanceUrl 	= res.get('instance_url');
		
		
    }


    //	-----------------------------------------------------------------------
    //	doSoqlQuery: Executes a REST query on a remote SFDC and returns a list of SObjects
    //	-----------------------------------------------------------------------
    public List<SObject> doSoqlQuery(String query) {
    	List<Sobject>	res;		
		PageReference 	urlPg	= new PageReference(this.sfdcInstanceUrl + '/services/data/v29.0/query');
		urlPg.getParameters().put('q',query); 

		String uri 				= urlPg.getUrl();				// let APEX do the URL encoding of the parms as necessary
		HttpResponse hRes = this.send(uri,'GET');
		if (hRes.getStatusCode() != 200) 
			throw new MyException('[HTTP-02] Error in query ' + uri + ' StatusCode=' +
												 hRes.getStatusCode() + ' statusMsg=' + hRes.getStatus());
		
		// Response body comes back as:
		// {"totalSize":10,
		//	"done":true,
		//	"records":[
		//				{"attributes":{
		//					"type"	: "the sobject",
		//					"url"	: "/services/data/v29.0/sobjects/the sobject/the id"
		//				},
		//				"field0 in query"	: "value of field 0",
		//				"field1 in query"	: "value of field1",
		//				...},
		//				next record ...
		//				]
		//	}
		JSONParser jp = JSON.createParser(hRes.getBody());
		do{
			jp.nextToken();
		} while(jp.hasCurrentToken() && !'records'.equals(jp.getCurrentName()));
		jp.nextToken();  // token is 'records'
		res = (List<SObject>) jp.readValueAs(List<SObject>.class);		// Let caller cast to specific SObject
		return res;
    }
 
 
 	//	--------------------------------------------------------------
    // 	TEST METHODS - MOCKS
    //	---------------------------------------------------------------	
	public class authenticateByUserNamePasswordMock implements HttpCalloutMock {
    	
    	Boolean	isMockResponseSuccessful;
    	
    	public authenticateByUserNamePasswordMock(Boolean isMockResponseSuccessful) {
    		this.isMockResponseSuccessful	= isMockResponseSuccessful;
    	}
    	
    	public HttpResponse respond(HttpRequest rqst) {
    		HttpResponse hResp		= new HttpResponse();
    		if (this.isMockResponseSuccessful) {
    			hResp.setStatusCode(200);
    			hResp.setBody('{' +			// data copied from SFDC example
    							' "id":"https://login.salesforce.com/id/00Dx0000000BV7z/005x00000012Q9P", ' +
								' "issued_at":"1278448832702",' +
								' "instance_url": "https://na1.salesforce.com",' +
								' "signature":"0CmxinZir53Yex7nE0TD+zMpvIWYGb/bdJh6XfOH6EQ=",' +
								' "access_token": "00Dx0000000BV7z!AR8AQAxo9UfVkh8AlV0Gomt9Czx9LjHnSSpwBMmbRcgKFmxOtvxjTrKW19ye6PE3Ds1eQz3z8jr3W7_VbWmEu4Q8TVGSTHxs"' 
							+'}');			
    		}
    		else {
    			hResp.setStatusCode(400);
    			hResp.setStatus('Bad request');
    		}
    		return hResp;
    	}
    }
    
    public class doSoqlQueryMock implements HttpCalloutMock {
    	
    	Boolean	isMockResponseSuccessful;
    	
    	public doSoqlQueryMock(Boolean isMockResponseSuccessful) {
    		this.isMockResponseSuccessful	= isMockResponseSuccessful;
    	}
    	
    	public HttpResponse respond(HttpRequest rqst) {
    		HttpResponse hResp		= new HttpResponse();
    		if (this.isMockResponseSuccessful) {
    			hResp.setStatusCode(200);
    			hResp.setBody('{' +			// data copied from SFDC example
    							' "totalSize": 2,' 		+ 
								' "done" 	: true,' 	+
								' "records" : ['		+
								'              { 	"attributes" 	: {' +
								'										"type"			: "Account",' +
								'										"url"			: "/services/data/v29.0/sobjects/Account/00id"' +
								'									},' +
								'				"ID"	: "00id"' +
								'				},' +
								'              { 	"attributes" 	: {' +
								'										"type"			: "Account",' +
								'										"url"			: "/services/data/v29.0/sobjects/Account/01id"' +
								'									},' +
								'				"ID"	: "01id"' +
								'				}' +
								'			]' +
								'}');			
    		}
    		else {
    			hResp.setStatusCode(404);
    			hResp.setStatus('Not found');
    		}
    		return hResp;
    	}
    }
    
    
   	//	--------------------------------------------------------------
    // 	TEST METHODS - tests
    //	--------------------------------------------------------------- 
	@isTest
    private static void testAuthenticateByUserNamePassword() {
    	HttpRest hr = new HttpRest();
    	try {
    		Test.setMock(HttpCalloutMock.class, new authenticateByUserNamePasswordMock(true));	
    		hr.authenticateByUserNamePassword('somekey','somesecret','someusername@somedomain','somepw+token',false);
    		System.assertEquals('00Dx0000000BV7z!AR8AQAxo9UfVkh8AlV0Gomt9Czx9LjHnSSpwBMmbRcgKFmxOtvxjTrKW19ye6PE3Ds1eQz3z8jr3W7_VbWmEu4Q8TVGSTHxs',hr.accessToken);
    		System.assertEquals('https://na1.salesforce.com',hr.sfdcInstanceUrl);
    	}
    	catch (Exception e) {System.assert(false,'shouldnt. Exception:' + e.getMessage() + e.getStackTraceString());}
    	try {
    		Test.setMock(HttpCalloutMock.class, new authenticateByUserNamePasswordMock(false));
    		hr.authenticateByUserNamePassword('somekey','somesecret','someusername@somedomain','somepw+token',false);
    		System.assert(false,'shouldnt happen, mock should produce error');
    	}
    	catch (Exception e) {System.assert(e.getMessage().contains('[HTTP-01]'),e.getMessage());}
    }

	@isTest
    private static void testDoSoqlQuery() {
    	HttpRest hr = new HttpRest();
    	List<Sobject>	sobjResList;
    	try {
    		Test.setMock(HttpCalloutMock.class, new doSoqlQueryMock(true));	
    		sObjResList = hr.doSoqlQuery('select id from Account');
    		System.assertEquals(2,sObjResList.size());
    	}
    	catch (Exception e) {System.assert(false,'shouldnt. Exception:' + e.getMessage() + e.getStackTraceString());}
    	try {
    		Test.setMock(HttpCalloutMock.class, new doSoqlQueryMock(false));
    		sObjResList = hr.doSoqlQuery('select id from Account');
    		System.assert(false,'shouldnt happen, mock should produce error');
    	}
    	catch (Exception e) {System.assert(e.getMessage().contains('[HTTP-02]'),e.getMessage());}
    }


}

Implementation Notes

You’ll need your own Exception class for this to compile. The example calls it MyException but I usually name these after the project or business unit wherein my code is deployed.
Note that method authenticateByUserNamePassword takes five arguments. You will need to set up the consumerKey and consumerSecret on the target org using Setup | Develop | Remote Access. Starting in V29.0, this is now called a ‘Connected App’. Your source org will need access to these values – perhaps through custom settings, directly-entered into your VF page, or for more security, as encrypted fields in an custom SObject.
Your source org will need to Setup | Security Controls | Remote Site Settings for both the OAuth 2.0 authentication access token provider(s) as well as the target instance(s).

Connected App Setup in the target org

There are several OAuth scopes available to you. I chose ‘Access and manage your data (api)’ as that met my requirements.

Remote Site settings in the source org

In my source org, I set up four Remote Sites – two for the production and sandbox OAuth 2.0 access token providers and one each for the target PROD and target sandbox.

Cropredy SFDC

Practical advice for the 'all singing, all dancing' SFDC Admin with an emphasis on Apex and VF

Category Archives: REST

REST SOQL OFFSET and LIMIT – 2000 records

SOQL Query Optimization

Salesforce to Salesforce Using REST – Part II

Salesforce to Salesforce Using REST – Part I

Connected App Setup in the target org

Remote Site settings in the source org