How Salesforce Query Optimizer works for LDV

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 records in database
• There would be an actual table containing data only for your Org
• Throughput would be increased
  • At the same time, Salesforce Query Engine can return more records, as it has to scan fewer records.
• Get a solution for Object bloat (Just in Case Field)

Learn about Salesforce SOQL Best practices.

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

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

How Salesforce Query Optimizer works for LDV Video

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.