Synchronous queries described in the previous section are generally only suitable for when you know specifically what the user is looking for and are confident you can reduce the query results to a level that can be handled in real time and within the synchronous Apex limits. The key features of Async SOQL are as follows:
- Asynchronous queries bypass query limits because they run in the background. They are focused on allowing you to perform aggregations to learn more information from the full dataset of billions of records, or a smaller subset of the data.
- You can also use them to extract a filtered subset of the raw data and place it into a Custom Object for further reporting and querying using the full SOQL query language, standard UIs, and platform reporting tools.
- Asynchronous queries also offer more flexibility within the filter criteria, allowing you to reference non-index fields, for example, the Milliseconds field.
- SOQL functions such as MIN, MAX, and AVG can also be used.
In this section, we will focus on an aggregation use case. To determine the fastest lap for each race, we will use the MIN SOQL aggregation function and group by the RaceId__c field. This will query over half a million records in one query!
Async SOQL queries do not return results to the calling code, as the results could take some time to calculate and could be too large for the client context to manage. Instead, the results are returned into a target Custom Object of your choosing. In this case, the sample code in this chapter contains a Race Lap History Summaries Custom Object for the target object. The following steps perform an Async SOQL query to find the fastest lap time for every race:
- Race Lap History Summaries are related to their respective Race record using an External ID field on the Race object. Edit the Spain race record and enter 904 in the Race Id field in order to associate the correct aggregate query result record. Note that the Async SOQL API does not have the ability to form this relationship itself when writing to target objects, so this is accomplished by an Apex Trigger; see RaceLapHistorySummaries.cls.
- The Async SOQL API is a Salesforce REST API, so we must log in to the Developer Workbench tool (https://workbench.developerforce.com/login.php) to invoke it. Use the username and password of your scratch org and remember to select Sandbox for the environment. Use the sfdx force:user:password:reset command to obtain the password if you have not done so already.
- From the queries menu, select Async SOQL Query.
- Complete the query fields as shown in the following screenshot:
- Click Next and complete the fields as shown in the following screenshot:
- Click Submit to start the query process and monitor it until completion using the View Status tab and by clicking on the active job.
Once the Async SOQL job completes, follow these steps to navigate to the results:
- Click on the Race Lap Histories Summaries tab.
- Click on the Race column to sort by records associated with known races.
Have a look at the following screenshot:
It shows the resulting Async SOQL calculated record.