In this session we talk about how to Optimize Salesforce query for large data volume Salesforce. We also cover the Skinny Table, Indexing Table and Optimize the SOQL in Salesforce.

Agenda

• Multi Tenant Architecture
• Skinny Table
• Indexing Table & Index Statistics
• Upper Limit on
• Standard & Custom Indexed Field
• Limit on AND , OR and LIKE operator
• SystemModstamp vs LastModifiedDate
• Query Plan Tool
• Q&A

Multi Tenant Architecture

Why Traditional Database optimization Technique would not work for Salesforce?

Lightning Platform Query Optimizer friendly Data Model

• No need to perform joins between Standard and Custom Object
• Don’t consider record in database
• There would be actual table containing data only for your Org
• Throughput would be increased
  • In same time Salesforce Query Engine can return more record, as it has to scan less records
• Get a solution for Object bloat (Just in Case Field)

That’s what it is – Skinny Table 

Considerations & Limitations – Skinny Table

• Contact Salesforce to enable it
• Can contain only 100 fields
• Change in field type used in Skinny table would make it invalid
• Skinny Table is not copied to sandbox after refresh (Except Full Copy Sandbox)
• Useful for read operations
• Cannot contain fields from other object / Parent

Index Table

• Traditional Indexing would not work for Salesforce
• Maintains different table about data and its type – Index Table
• Standard Database Index on Index Table
• Contains Upper limit about how much record can be returned by Search
• Lightning Query optimizer maintains statistics about Index Table
• Statistics gathering process runs nightly
• Few fields are already Indexed (Standard Index)
• We can ask Salesforce to create Custom Index
• Custom Index also created on External Id fields

Index Table – Upper Limits

Standard Index Field

Use Indexing only if – Max 1 Million ●Filter matches less than or equal to 30% of first Million record ●And less than or equal to 15% of additional records

Custom Index Field

Use Indexing only if ●Filter matches less than or equal to 10% total records upto max 333,333

Recording

If you are new in Salesforce. Please check our free Salesforce Admin and Salesforce Developer training. Subscribe to the channel if you haven’t already