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.
Let’s recap what LastModifiedDate and SystemModStamp dates are. They are both system fields that store date and time values for each record.
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.
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.
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.
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.