Force.com SOQL Performance Tips: LastModifiedDate vs SystemModStamp

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?

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 imported with 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 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:

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

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.

For more information on selectivity and how to tune your SOQL queries, I highly encourage you to read through 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

• Database Query & Search Optimization Cheat Sheet
• Webinar: Inside the Force.com Query Optimizer
• Maximizing the Performance of Force.com SOQL, Reports, and List Views
• Force.com SOQL Best Practices: Nulls and Formula Fields
• Force.com SOQL Best Practices: Sort Optimization
• Architect Core Resources

About the Author and CCE Technical Enablement

Daisuke Kawamoto is an Architect Evangelist within the Technical Enablement team of the salesforce.com Customer-Centric Engineering group. The team’s mission is to help customers understand how to implement technically sound salesforce.com solutions. Check out all of the resources that this team maintains on the Architect Core Resources page.