In this session, we talk about how to Optimize Salesforce queries for large data volumes Salesforce. We also cover the Skinny Table, Indexing Table, and Optimize the SOQL in Salesforce. Let’s see How Salesforce Query Optimizer works for LDV.
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
Salesforce can create skinny tables to contain frequently used fields and to avoid joins. This can improve the performance of certain read-only operations. Skinny tables are kept in sync with their source tables when the source tables are modified.
Learn more about Skinny Table here.
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
- 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
How Salesforce Query Optimizer works for LDV Demo
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.