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
