LastModifiedDate & SystemModstamp Available in Every Record Of Salesforce : Their Importance & Differences Between Them

Introduction

Do you have an integration that retrieves a set of records that were updated since the last integration job? Do you have a Visualforce page that lists records that have (or haven’t) been updated recently? Are you making conscious decisions when choosing LastModifiedDate over the SystemModStamp field to filter your queries? There are differences between SystemModStamp and LastModifiedDate that go beyond just being two separate system fields. To help your application scale and perform with large data volumes, it is important to understand the performance implications when choosing one field over the other as you use them in your SOQL filters.

What Is The Difference Between LastModifiedDate And SystemModStamp?

They are both system fields that store date and time values for each record.

1. LastModifiedDate is automatically updated whenever a user creates or updates the record. LastModifiedDate can be imported with any back-dated value if your business requires preserving original timestamps when migrating data into Salesforce.
2. SystemModStamp is strictly read-only. Not only is it updated when a user updates the record, but also when automated system processes update the record. Because of this behavior, it creates a difference in stored value where ‘LastModifiedDate <= SystemModStamp’ but never ‘LastModifiedDate > SystemModStamp’.

Possible Scenarios When The SystemModStamp Can Differ From The LastModifiedDate:

1. The archive date is extended by more than 365 days.
2. An existing picklist value is updated and not replaced with an existing picklist value.
3. A Contact’s email address is flagged, as per the ‘Email Bounce Management‘ configuration.
4. The ‘LastActivityDate‘ field is modified.
5. A Roll-up summary field is created, which will update all the parent records’ ‘SystemModstamp‘ asynchronously. Note: Recalculation will also take place if the ‘Summary Type’ is updated.
6. Some Salesforce internal backend processes will also update ‘SystemModstamp,’ as ‘SystemModstamp‘ is used internally to signal that a record, or related data, may have changed – and that internal processes may need to synchronize themselves to the new record data. 

Let’s understand this by an Example:

We will see how the SystemModstamp will change keeping the LastModifiedDate the same when we try to update the picklist option of a particular field in the Object’s Schema to a new value.

I have a custom picklist field in Account called Region__c. I will change one of the picklist values from EMEA to APAC. This field is also set for tracking.

Before I do that, let me query all the records where the region is in EMEA.

SELECT Id, Name, Region__c, LastModifiedDate, SystemModstamp FROM Account WHERE Region__c = ‘EMEA’

As we see here, all records have LastModifiedDate = SystemModstamp.

Now, let me update the picklist value from EMEA to APAC. Salesforce by default will auto update all records where Region__c match EMEA to APAC.

Then let’s do another query.

SELECT Id, Name, Region__c, LastModifiedDate, SystemModstamp FROM Account WHERE Region__c = ‘APAC’

We can clearly see that no history is registered when the Region__c changed from EMEA to APAC.

How Can LastModifiedDate Filters Affect SOQL Performance?

So, how does this affect performance of a SOQL query? Under the hood, the SystemModStamp is indexed, but LastModifiedDate is not. The Salesforce query optimizer will intelligently attempt to use the index on SystemModStamp even when the SOQL query filters on LastModifiedDate. However, the query optimizer cannot use the index if the SOQL query filter uses LastModifiedDate to determine the upper boundary of a date range because SystemModStamp can be greater (i.e., a later date) than LastModifiedDate. This is to avoid missing records that fall in between the two timestamps.

Let’s work through an example to make this clear:

The following queries will perform well, as long as the filters meet the business requirements because the query optimizer will utilize the index on SystemModStamp:

Select Id, Name from Account where LastModifiedDate > 2014-11-08T00:00:00Z

Select Id, Name from Account where LastModifiedDate = CustomDate__c

The example below cannot be optimized for performance because the query optimizer cannot utilize the index on SystemModStamp.

Select Id, Name from Account where LastModifiedDate < CutoffDate__c

Note: The query optimizer can’t use the index on SystemModStamp as some records (Account) having LastModifiedDate less than CutoffDate__c can have a greater (i.e., a later date) value in SystemModStamp.
The same logic applies when using date literals. As a reminder, if you have one or more selective filters in your SOQL, the query optimizer can use that as the leading filter, so your query will perform well even if you have a non-optimal LastModifiedDate filter defining the upper boundary of a date range.

Options To Optimize Performance For LastModifiedDate

The most simple and effective solution to optimize performance is to use SystemModStamp instead of LastModifiedDate to filter data. However, SystemModStamp may not be available for the object you’re querying against or your business requirement may not allow you to simply substitute the two fields. You also may want to consider alternative approaches that would address other scalability concerns together. Here are some options that will help you optimize performance and scalability of your SOQL queries.

• Use a custom date field: Use workflow field updates or triggers to copy the LastModifiedDate value, then contact Support to request adding a custom index on the custom field. Then, use that as your filter.
• Add a skinny table: If you have performance issues due to large data volume, consider adding a skinny table. Skinny tables include indexed LastModifiedDate by default.
• Filter on LastActivityDate: If your business requirement is to list up Account or Contact records related to activities, and you have (or plan to have) a skinny table, contact Support to request an index on the LastActivityDate field.
• Use the Data Replication API: For example, use getUpdated() to retrieve updated records. Under the hood, the API uses SystemModStamp to determine the matching records, and if it doesn’t exist it will automatically use LastModifiedDate or CreatedDate.

For more updates regarding Salesforce or if you are looking for any Salesforce consultancy services, Contact ABSYZ Inc at +1.415.364.8055 or +91 79979 66174. Visit our website at https://absyz.com/