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
1,624 total views, 4 views today
Amit Chaudhary is Salesforce Application Architect and working on Salesforce Platform since 2010. He is Salesforce MVP since 2017 and have 13 Salesforce Certificates.
He is a active blogger and founder of Apex Hours.