Loading

Know the difference between the LastModifiedDate and SystemModStamp

Udgivelsesdato: Nov 27, 2024
Beskrivelse

SystemModStamp and LastModifiedDate are system fields with similar behavior, but slightly different properties. To help your application scale and perform with large data volumes, it is important to understand the performance implications of choosing one field over the other as you use them in your SOQL filters. 

Løsning

What is the difference between LastModifiedDate and SystemModStamp?

Let’s recap what LastModifiedDate and SystemModStamp dates are. They are both system fields  that store date and time values for each record.
  • LastModifiedDate is automatically updated whenever a user creates or updates the record. LastModifiedDate can be updated to any back-dated value if your business requires preserving original timestamps when migrating data into Salesforce.
  • SystemModStamp is strictly read-only. Not only is it updated when a user updates the record, but also when automated system processes (such as triggers and workflow actions) update the record. Because of this behavior, it creates a difference in stored value where ‘LastModifiedDate <= SystemModStamp’ but never ‘LastModifiedDate > SystemModStamp’.
 

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 selectivity threshold  because the query optimizer will utilize the index on SystemModStamp:

1Select Id, Name from Account where LastModifiedDate > 2014-11-08T00:00:00Z
1Select 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.
1Select Id, Name from Account where LastModifiedDate < CutoffDate__c
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 lower boundary of a date range.
For more information on selectivity and how to tune your SOQL queries, please see the related resources below.

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.
Regardless of which approach you take, always keep in mind that you should carefully evaluate both your business requirements and possible performance implications when using LastModifiedDate over SystemModStamp.

Related Resources

Vidensartikelnummer

000386977

 
Indlæser
Salesforce Help | Article