Hi folks, In this article we’re going to learn all about is Async SOQL, what is SOQL, what’s the difference between Async SOQL and SOQL, and how to use Async SOQL. Before you dive into this article, a great pre-requisite prior to moving forward would be an article on Salesforce Big Objects. Nidhi recently covered what are Salesforce Big Objects, and how to use them. Be sure to check this one out.

What is SOQL?

SOQL stands for Salesforce Object Query Language. It allows you to search your organization’s Salesforce instance for data or specific information. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is designed and specifically used for Salesforce data.

With SOQL, you can construct simple but powerful query strings in the following environments:

• In the queryString parameter in the query() call
• In Apex statements
• In Visualforce controllers and getter methods
• In the Salesforce CLI or the Salesforce Extensions for Visual Studio Code

What is Async SOQL?

Yes, You guessed it right, Async SOQL means Asynchronous SOQL. It’s an asynchronous process. It’s a way to run SOQL queries in the background over a Salesforce entity data, including subjects, BigObjects, and external objects. The external objects can be accessed through the use of Lightning Connect. So we can say, An Async SOQL is a way to process SOQL processes in the background or when the user can’t wait for the result in a synchronous process. Async SOQL is a great way to query large amounts of data stored in Salesforce. We can query up to 1 million records and for extra capacity, we need an add-on license.

Use Cases for Async SOQL

A few use cases where you would use Async SOQL are as follows:

1. Real-time Event Monitoring: When users query an object using any API. You can determine when the object was last accessed, who accessed it, and what part of it was accessed.
2. Event monitoring: to identify all users who accessed a sensitive field, such as Social Security Number or Email.
3. Field Audit Trail: This allows you to define a policy to retain archived field history data up to 10 years from the time the data was archived. This feature would be helpful if you need to comply with industry regulations related to audit capability and data retention.
4. Customer 360 Degree and Filtering: This allows you to load data from external sources such as Salesforce Big Objects. A great use case would be needing data such as customer transactions, interactions, point-of-sale data, and orders, stored in your big objects, and need to take that data and enrich your customer profiles in Salesforce.

How is Async SOQL Implemented?

Async SOQL is implemented in the form of RESTful API, this allows you to run queries in the. similar syntax o the SOQL language. As it is an Async SOQL (Asynchronous Process) we can exceed the governor limit for CPU time limit which means we can process complex queries. We can use the Big Object, Custom Object, or Standard Object to store the results.

The Async SOQL is the concept of creating a small data set from the large data set. The large data can come from one object or multiple objects or can come from complex Aggregations queries. We can store the small data set and working data set to the custom object and then use it in reports, dashboards, or any other feature.

For example, Processing the historical data to analyze the customer behaviors, creating marketing strategies, Event Monitoring, Field Audit Trail, and Data Archiving.

What are the limitations of Async SOQL?

We can only run one concurrent Async SOQL at a time.

When Should you use Async SOQL Versus SOQL

Use standard SOQL when:

• You want to display the results in the UI without having the user wait for results.
• You want results returned immediately for manipulation within a block of Apex code.
• You know that the query will return a small amount of data. When you know which objects the data resides in, and you want to:

• • Retrieve data from a single object or from multiple objects that are related to one another.
  • Count the number of records that meet specified criteria.
  • Sort results as part of the query.
  • Retrieve data from number, date, or checkbox fields.

Use Async SOQL when:

• You are querying millions of records.
• You want to ensure that your query completes.
• You do not need to do aggregate queries or filtering outside of the index

Syntax for Submitting the Async SOQL

URL: https://yourInstance.salesforce.com/services/data/v38.0/async-queries/
Method: POST
Request Parameters

Example Request :

{
“query”: “SELECT firstField__c, secondField__c FROM SourceObject__b”,

“operation”: “insert”,

“targetObject”: “TargetObject__c”,

“targetFieldMap”: {“firstField__c”:”firstFieldTarget__c”,
“secondField__c”:”secondFieldTarget__c”
},
“targetValueMap”: {“$JOB_ID”:”BackgroundOperationLookup__c”,
“Copy fields from source to target”:”BackgroundOperationDescription__c”
}
}

In the response will get the “jobId” to track the status of Async SOQL.

Example Response

{
“jobId”: “08PD000000003kiT”,

“message”: “”,

“query”: “SELECT firstField__c, secondField__c FROM SourceObject__b”,

“status”: “New”,

“targetObject”: “TargetObject__c”,

“targetFieldMap”: {“firstField__c”:”firstFieldTarget__c”,
“secondField__c”:”secondFieldTarget__c”
},
“targetValueMap”: {“$JOB_ID”:”BackgroundOperationLookup__c”,
“Copy fields from source to target”:”BackgroundOperationDescription__c”
}
}

Syntax for Tracking the Status

URL : https://yourInstance.salesforce.com/services/data/v38.0/async-queries/<jobID>
Method : GET
Example Response:
{
“jobId”: “08PD000000000001”,
“message”: “”,
“query”: “SELECT firstField__c, secondField__c FROM SourceObject__b”,
“status”: “Complete”,
“targetObject”: “TargetObject__c”,
“targetFieldMap”: {“firstField__c”:”firstFieldTarget__c”,
“secondField__c”:”secondFieldTarget__c” }
}

Syntax for Canceling An Async SOQL

URL: https://yourInstance.salesforce.com/services/data/v38.0/async-queries/<jobID>
Method: HTTP DELETE

For a list of supported SOQL commands or additional information on Async SOQL be sure to check out the Salesforce developer’s site for more great information. As always, if you still have some questions feel free to reach out to us. 

Psst… If you’ve just gone live with your Salesforce implementation, be sure to check out this helpful post on what you need to do after implementing Salesforce.

About Roycon
We’re an Austin-based Salesforce Consulting Partner, with a passion and belief that the Salesforce platform’s capabilities can help businesses run more efficiently and effectively. Whether you are just getting started with Salesforce or looking to realize its full potential, Roycon specializes in Salesforce Implementations, Salesforce Ongoing Support, and Salesforce Integrations, and Development. We’re the certified partner to guide the way to increase Salesforce Adoption, make strategic decisions, and build your Salesforce Roadmap for success.