Doyouhaveanintegrationthatretrievesasetofrecordsthatwereupdatedsincethelastintegrationjob?

DoyouhaveaVisualforcepagethatlistsrecordsthathave(orhaven’t)beenupdatedrecently?

AreyoumakingconsciousdecisionswhenchoosingLastModifiedDateovertheSystemModStampfieldtofilteryourqueries?

TherearedifferencesbetweenSystemModStampandLastModifiedDatethatgobeyondjustbeingtwoseparatesystemfields。

Tohelpyourapplicationscaleandperformwithlargedatavolumes,itisimportanttounderstandtheperformanceimplicationswhenchoosingonefieldovertheotherasyouusetheminyourSOQLfilters。

Let’srecapwhatLastModifiedDateandSystemModStampdatesare。

Theyarebothsystemfieldsthatstoredateandtimevaluesforeachrecord。

LastModifiedDateisautomaticallyupdatedwheneverausercreatesorupdatestherecord。

LastModifiedDatecanbeupdatedtoanyback-datedvalueifyourbusinessrequirespreservingoriginaltimestampswhenmigratingdataintoSalesforce。

SystemModStampisstrictlyread-only。

Notonlyisitupdatedwhenauserupdatestherecord,butalsowhenautomatedsystemprocesses(suchastriggersandworkflowsactions)updatetherecord。

Becauseofthisbehavior,itcreatesadifferenceinstoredvaluewhere‘LastModifiedDateSystemModStamp’。

So,howdoesthisaffectperformanceofaSOQLquery?

Underthehood,theSystemModStampisindexed,butLastModifiedDateisnot。

TheSalesforcequeryoptimizerwillintelligentlyattempttousetheindexonSystemModStampevenwhentheSOQLqueryfiltersonLastModifiedDate。

However,thequeryoptimizercannotusetheindexiftheSOQLqueryfilterusesLastModifiedDatetodeterminethelowerboundaryofadaterangebecauseSystemModStampcanbegreater(i。

e。

alaterdate)thanLastModifiedDate。

Thisistoavoidmissingrecordsthatfallinbetweenthetwotimestamps。

Let’sworkthroughanexampletomakethisclear。

Thefollowingquerywillperformwell,aslongasthefiltermeetstheselectivitythresholdbecausethequeryoptimizerwillutilizetheindexonSystemModStamp:

SelectId,NamefromAccountwhereLastModifiedDate

TheexamplesbelowcannotbeoptimizedforperformancebecausethequeryoptimizercannotutilizetheindexonSystemModStamp。

SelectId,NamefromAccountwhereLastModifiedDate>2014-11-08T00:00:00Z

SelectId,NamefromAccountwhereLastModifiedDate=CustomDate__c

Thesamelogicapplieswhenusingdateliterals。

Asareminder,ifyouhaveoneormoreselectivefiltersinyourSOQL,thequeryoptimizercanusethatastheleadingfilter,soyourquerywillperformwellevenifyouhaveanon-optimalLastModifiedDatefilterdefiningthelowerboundaryofadaterange。

FormoreinformationonselectivityandhowtotuneyourSOQLqueries,Ihighlyencourageyoutoreadthroughtherelatedresourcesbelow。

ThemostsimpleandeffectivesolutiontooptimizeperformanceistouseSystemModStampinsteadofLastModifiedDatetofilterdata。

However,SystemModStampmaynotbeavailablefortheobjectyou’requeryingagainstoryourbusinessrequirementmaynotallowyoutosimplysubstitutethetwofields。

Youalsomaywanttoconsideralternativeapproachesthatwouldaddressotherscalabilityconcernstogether。

HerearesomeoptionsthatwillhelpyouoptimizeperformanceandscalabilityofyourSOQLqueries。

Useacustomdatefield:UseworkflowfieldupdatesortriggerstocopytheLastModifiedDatevalue,thencontactSupporttorequestaddingacustomindexonthecustomfield。

Then,usethatasyourfilter。

Addaskinnytable:Ifyouhaveperformanceissuesduetolargedatavolume,consideraddingaskinnytable。

SkinnytablesincludeindexedLastModifiedDatebydefault。

FilteronLastActivityDate:IfyourbusinessrequirementistolistupAccountorContactrecordsrelatedtoactivities,andyouhave(orplantohave)askinnytable,contactSupporttorequestanindexontheLastActivityDatefield。

UsetheDataReplicationAPI:Forexample,usegetUpdated()toretrieveupdatedrecords。

Underthehood,theAPIusesSystemModStamptodeterminethematchingrecords,andifitdoesn’texistitwillautomaticallyuseLastModifiedDateorCreatedDate。

Regardlessofwhichapproachyoutake,alwayskeepinmindthatyoushouldcarefullyevaluatebothyourbusinessrequirementsandpossibleperformanceimplicationswhenusingLastModifiedDateoverSystemModStamp。

DaisukeKawamotoisanArchitectEvangelistwithintheTechnicalEnablementteamofthesalesforce。

comCustomer-CentricEngineeringgroup。

Theteam’smissionistohelpcustomersunderstandhowtoimplementtechnicallysoundsalesforce。

comsolutions。

CheckoutalloftheresourcesthatthisteammaintainsontheArchitectCoreResourcespage。

ThisentrypassedthroughtheFull-TextRSSservice-ifthisisyourcontentandyou’rereadingitonsomeoneelse’ssite,pleasereadtheFAQatfivefilters。

org/content-only/faq。

php#publishers。

SalesforceDevelopersBlogs