Performance of Big Object Index

Utilizing big objects for large passive data sets is a common practice in Salesforce implementations. We know that big objects require a composite primary key to be deployed to a Salesforce instance. The composite primary key is also referred to as an Index for the particular big object.

Big objects do not support querying data using query filters on fields that do not make up the index. The big object’s index is used to query and/or delete records from the big object. It is crucial that the index be made up of fields that can uniquely identify records. Also note that the Index can only be up to a maximum of 100 characters in length. If the Index is created and does not provide satisfactory results, you will have to start from scratch with a new big object because once created, big object indices cannot be modified.

Creating an Index

1. First create at least one required custom field for the big object.
2. Optionally, create up to four more required custom fields that can make up the index.
3. Make sure that the combined length of all the fields you plan on adding to the Index does not exceed 100.
4. Click on the New button in the Index section of the big object detail page.
5. Type in the Index’s label and name.

This is where it starts getting serious. The custom required fields that you have available on the big object will be displayed. Setting the index position and index direction will determine the future SOQL and DML structure that interact with the big object. The index direction is important for performance benefits. Set the index direction for fields to ascending or descending based on whether the big object records with field’s lower order values or higher order values will be most accessed. The index position is critical as well because big objects will only allow querying records with filters specified for all the fields that make up the index. You cannot apply query filters in SOQL that skip the fields at lower positions.

Example Scenario

Let us consider a scenario where Sales Managers have requested maintaining an archive of all the closed opportunities, and they would like to review the archive every quarter. Depending on the number of opportunities created in the organization every month, it might make sense to utilize a big object. You have automation that will create a new big object record when an opportunity’s stage is set to either Closed Won or Closed Lost. Once functional, records in the big object might look similar to the following table.

Index Direction

Specifying the index direction according to usage will provide significant performance benefits. We know that sales managers will want to query and/or run reports on the big object based on Close Date for the most recent quarter, so the index direction for Close Date should be descending. This will assist the database in scanning just the latest quarter’s results first, instead of scanning older records first and causing sluggish performance.

Index position

Index position is critical as well. For example, even though we know that we want to routinely query opportunities based on close date, setting the close date to be at the first position of the index and setting another field to be at the second position of the index will prevent you from specifying a range of dates in the SOQL query. To specify a range of dates as a SOQL filter clause, the field will have to be the last field that makes up the index. So for this scenario, we can create the index with just one field, Close Date at the first position, or create a more robust index by setting Account at the first position in the index, and Close Date at the second position in the index. This way you will have the additional capability to query closed opportunity archive records for specific accounts as well as over a period of time.