3

From Force.com SOQL Performance Tips: LastModifiedDate vs SystemModStamp:

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.

I do not understand how it can miss records? Can someone please explain?

3

The article covers it pretty well.

  • 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

The key is:

SystemModStamp can be greater (i.e., a later date) than LastModifiedDate.

So let's say you have a record with these values:

LastModifiedDate = 2016-07-21 12:46:00
SystemModStamp   = 2016-07-21 13:30:00

In this case, you have:

records that fall in between the two timestamps


Using the above example, let's say you want to use a query filter like:

LastModifiedDate < 2016-07-21T13:00:00Z

The filter date is between the two Datetime values. If the query optimizer uses SystemModStamp as a proxy for LastModifiedDate, the record described above will not be found. So the optimizer will not do any indexing because, while it would improve performance, it would cause incomplete results.

| improve this answer | |
0

I'm not sure I understand the explanation given here.

For ex. if we change the filter to as below,

LastModifiedDate > 2016-07-21T13:00:00Z

If the query optimizer uses SystemModStamp as proxy for lastModifiedDate, the above record will be FOUND but it is NOT correct since LastModifiedDate is still 2016-07-21 12:46:00

How are we then saying that SystemModStamp can be used an index for this use case?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.