A blog dedicated to Salesforce Ohana

LastModifiedDate Vs SystemModStamp


In Salesforce, when we create or update a record, there are so many things happen in the background. In this post, I am going to share my knowledge regarding two important fields present in each Salesforce record. I will start with a basic explanation regarding these fields and then at the end, I will explain how these fields are going to impact your query performance.

Let's start with basic explanation -

LastModifiedDate:

This is a standard field present in each Salesforce record. This field will be updated with current timestamp when the record is created or edited by Salesforce user. This field also has a very unique feature i.e. this field can be imported with some previous date. Let's say you want to load data from your old legacy system, but you want to preserve the dates from your legacy system. In this situation, you can load the records from your legacy system into Salesforce by updating the LastModifiedDate field with the previous date.

SystemModStamp:

This is a read-only field. This field will be updated with current timestamp when the record is updated by the user. But the important difference is that this field will also be updated when the record is updated by Automated System Process. So in mathematical term -
LastModifiedDate <= SystemModStamp    This is possible.
LastModifiedDate > SystemModStamp      This is not possible.

How LastModifiedDate and SystemModStamp affect SOQL performance:

LastModifiedDate field is not indexed, but SystemModStamp field is indexed. So when SOQL query uses LastModifiedDate field in the where clause, Salesforce's Query Optimizer will internally use SystemModStamp to improve the performance. But when LastModifiedDate is used to determine the upper boundary of a date range, then Salesforce's Query Optimizer will not be able to use SystemModStamp. Confused???
Let's understand the concept with below examples -

Consider one account record - ABC Technologies
Record created by user on Jan 31st, 2018 LastModifiedDate: Jan 31st, 2018
SystemModStamp: Jan 31st, 2018
Record updated by Automated Process on Feb 2nd, 2018 LastModifiedDate: Jan 31st, 2018
SystemModStamp: Feb 2nd, 2018

Query: Select Id From Account Where LastModifiedDate > 2018-01-30T00:00:00Z
Can Salesforce's Query Optimizer replace LastModifiedDate with SystemModStamp? Answer is YES
Salesforce's Query Optimizer will change the query to -
Select Id From Account Where SystemModStamp > 2018-01-30T00:00:00Z to improve the performance and definitely we will get out ABC Technologies back from the SOQL.

Now consider another situation where the query is:
Select Id From Account Where LastModifiedDate < 2018-02-01T00:00:00Z.
Can Salesforce's Query Optimizer replace LastModifiedDate with SystemModStamp? Answer is NO
Why?
With original query (Select Id From Account Where LastModifiedDate < 2018-02-01T00:00:00Z) we are expecting ABC Technologies will come out, but if Salesforce's Query Optimizer replaces LastModifiedDate with SystemModStamp, then the query will become Select Id From Account Where SystemModStamp < 2018-02-01T00:00:00Z. So definitely with this query, we will not get our ABC Technologies back.

That is why Salesforce's Query Optimizer will not be able to use SystemModStamp instead of LastModifiedDate when LastModifiedDate is used to determine with the upper boundary of a date range, otherwise, the records in between these two dates(SystemModStamp and LastModifiedDate) will be missed from the query result.



Share:

No comments:

Post a Comment

Follow Me

Enter your email address:

Delivered by FeedBurner

Popular Posts

Labels

Salesforce (99) Apex (43) admin (27) ADM (20) visualforce (20) dev 501 (19) integration (18) learn salesforce (17) 501 (16) SOAP (13) tutorial (11) Certification. (9) lightning (8) Trigger (7) test class (7) unit testing (7) design pattern (6) report (6) security (6) trailhead (6) Advanced Admin (5) New Features (5) SOQL (5) css (5) dashboard (5) debug (5) developer (5) formula (5) javascript (5) mobile (5) salesforce release (5) service cloud (5) solution management (5) use case (5) JSON (4) Lightning Experience (4) WebSphere (4) best practice (4) cast iron (4) component (4) github (4) html (4) polymer (4) profiles (4) responsive (4) tdd (4) ui (4) Certification (3) Live Chat (3) Performance (3) Products (3) Sales Cloud (3) Study Notes. (3) Summer15 (3) Tips (3) deployment (3) dynamic apex (3) event (3) license (3) map (3) mapbox (3) singleton (3) version controlling (3) Advanced Apex (2) Bulkify (2) Data Architecture and Management Certification (2) Distributed Version Controlling (2) Eclipse (2) Einstein (2) Force.com IDE (2) Governor Limit (2) IBM (2) Kitchener Developer Group (2) Lightning Design System (2) Live Agent (2) Online Event (2) Price Book (2) REST (2) SOSL (2) Spring 15 (2) Summer17 (2) ant (2) automation tool (2) basic (2) chatter (2) coding (2) communication (2) console (2) controller (2) documentation (2) flow (2) git (2) jquery (2) logging (2) permission (2) process builder (2) release (2) salesforce1 (2) strategy (2) xml (2) Agent Productivity (1) Analytics (1) Architect (1) Asynchronous callout (1) Bots (1) Browser (1) Bulk data load (1) CTA (1) Calendar (1) Canon (1) Case Management (1) Classic (1) Contact Center (1) Continuation (1) Continuous Integration (1) Cookie (1) Custom Metadata (1) Custom Object (1) Decorator Design Pattern (1) Diwali (1) Email (1) FSC (1) Financial Services Cloud (1) Goals (1) Groups (1) Guide (1) Household (1) Ideas (1) Implicit Sharing (1) Improvement (1) JourneyToCTA (1) KPIs (1) Kitchener User Group (1) Large Data Volume (1) LastModifiedDate (1) Metadata (1) Metrics (1) Omni-Channel (1) Opportunity (1) Person Account (1) Photo (1) Platform Developer I (1) Presentation (1) Product Schedule (1) Profile (1) Public Site (1) Query Plan (1) QuickReference (1) Reports (1) Role (1) SFDX (1) Salesforce DX (1) Salesforce Optimizer (1) Scratch Org (1) Session (1) Sharing (1) Site (1) Skills (1) Snap-ins (1) Spring 17 (1) Summer14 (1) Summer16 (1) Switch (1) SystemModStamp (1) Users (1) Webservice (1) Winter'15 (1) Winter'17 (1) access (1) agile (1) app (1) approval process (1) aura (1) awesome (1) backup (1) bitbucket (1) book (1) campaign (1) change set (1) code (1) code coverage (1) configuration (1) csv (1) custom button (1) custom settings (1) customization (1) data loader (1) database (1) delegate Admin (1) describe (1) dom (1) dreamforce (1) duplicate (1) dynamic (1) equals (1) error (1) field-level security (1) folder (1) ftp (1) generic (1) gift (1) global describe (1) hashcode (1) import wizard (1) jenkins (1) keynote (1) long running requests (1) monitoring (1) mysql (1) object (1) page layout (1) personal (1) power of one (1) record type (1) relationship (1) request (1) review (1) sub-tab (1) tab (1) username (1) visual workflow (1) workflow (1)

Total Subscribers

Total Pageviews