How Salesforce Query Optimizer works for LDV

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