Database
Database
Query & Search Optimization Cheat Sheet
Query Optimization Overview
When building queries, list views, and reports, it's best to create filter conditions that are selective so that Force.com scans the most appropriate rows in the objects that your queries target. This best practice is especially important when your queries target "large objects," objects containing more than one million records. When writing SOQL, consider using the following fields, which can make your query filter conditions more selective, and improve your query response times and your database's overall performance.
Selectivity Overview
Several things can affect the selectivity of a query filter's conditions. • Whether the field in the condition has an index • Whether the value in the condition is selective relative to the total number of records in the object. These numbers determine the selectivity threshold, which the Force.com query optimizer uses to ensure that the most appropriate index, if any, drives each of your queries. • Whether the operator in the condition permits the use of available indexes When writing your queries, remember the following selectivity conditions and tips.
SOQL Fields with Database Indexes
Index Selectivity Conditions and Thresholds
Primary Keys
Foreign Keys
Audit Dates
Custom Fields
Unary Condition: Standard Index
Unary Condition: Custom Index
AND Condition
OR Condition
LIKE Condition
• Id
• CreatedById
• CreatedDate
• Unique fields
• Name
• LastModifiedById
• LastActivityDate
• OwnerId
• Lookup fields
• SystemModstamp
• External ID fields
Force.com uses a standard index if the filter targets less than:
Force.com uses a custom index if the filter targets less than:
• 30% of the first million records
• 10% of the first million records
Force.com uses a composite index join if the filter targets less than:
Force.com uses a union if the filter targets less than:
• 15% of all records after the first million records
• 5% of all records after the first million records
• Twice the index selectivity thresholds for each field
• The index selectivity thresholds for each field
• 1 million total records
• 333,333 total records
• The index selectivity thresholds for the intersection of those fields
• The index selectivity thresholds for the sum of those fields
For conditions that don't start with a leading wildcard, Force.com tests the first 100,000 rows for selectivity.
• Master-detail relationship fields
Query Optimization Resources
Index Selectivity Exceptions
• Best Practices for Deployments with Large Data Volumes (white paper)
• The following filter operators
In addition to this cheat sheet's previous sections, we recommend reading the following related resources, which can help you retrieve the records you want from a large volume of data—and do so quickly and efficiently.
When you build a filter condition with the following operators, Force.com doesn't use an available index. Instead, it scans all records in the object to find the records that satisfy the condition. Feel free to use these operators, but be sure to add selective filter conditions.
• Force.com Apex Code Developer's Guide (guide)
»»not equal to
• Force.com Blogs: Engineering (blog posts)
»»contains
• How to Improve Listview Performance (Salesforce Knowledge article) • In the online help: »»"Build Effective Filters" »»"Getting the Most Out of Filter Logic" »»"Improve Report Performance"
»»does not contain
• When used with text and text fields, the following comparison operators »»Less than ()
»»Less than or equal to (=) Additionally, Force.com doesn't use available indexes when you use: • Leading wildcards • Non-deterministic or cross-object formula fields
SOSL Fields with Search Indexes
Search Selectivity Tips
General
General
Sidebar Search and Advanced Search
• Name fields
• Be as selective as possible. For example, use Michael*, not Mich*.
• Search for the exact phrase with an advanced search.
• Phone fields • Text fields • Picklist fields These fields vary by object. See "Search Fields" in the online help.
• Remember that Chatter feed searches aren't affected by the scope of your search; Chatter feed search results include matches across all objects.
• Limit scope by targeting: »»Specific objects »»Rows owned by the searcher »»Rows within a division, when applicable
See "Search Overview" in the online help.
http://developer.force.com
Query & Search Optimization Cheat Sheet
Query Optimization Overview
When building queries, list views, and reports, it's best to create filter conditions that are selective so that Force.com scans the most appropriate rows in the objects that your queries target. This best practice is especially important when your queries target "large objects," objects containing more than one million records. When writing SOQL, consider using the following fields, which can make your query filter conditions more selective, and improve your query response times and your database's overall performance.
Selectivity Overview
Several things can affect the selectivity of a query filter's conditions. • Whether the field in the condition has an index • Whether the value in the condition is selective relative to the total number of records in the object. These numbers determine the selectivity threshold, which the Force.com query optimizer uses to ensure that the most appropriate index, if any, drives each of your queries. • Whether the operator in the condition permits the use of available indexes When writing your queries, remember the following selectivity conditions and tips.
SOQL Fields with Database Indexes
Index Selectivity Conditions and Thresholds
Primary Keys
Foreign Keys
Audit Dates
Custom Fields
Unary Condition: Standard Index
Unary Condition: Custom Index
AND Condition
OR Condition
LIKE Condition
• Id
• CreatedById
• CreatedDate
• Unique fields
• Name
• LastModifiedById
• LastActivityDate
• OwnerId
• Lookup fields
• SystemModstamp
• External ID fields
Force.com uses a standard index if the filter targets less than:
Force.com uses a custom index if the filter targets less than:
• 30% of the first million records
• 10% of the first million records
Force.com uses a composite index join if the filter targets less than:
Force.com uses a union if the filter targets less than:
• 15% of all records after the first million records
• 5% of all records after the first million records
• Twice the index selectivity thresholds for each field
• The index selectivity thresholds for each field
• 1 million total records
• 333,333 total records
• The index selectivity thresholds for the intersection of those fields
• The index selectivity thresholds for the sum of those fields
For conditions that don't start with a leading wildcard, Force.com tests the first 100,000 rows for selectivity.
• Master-detail relationship fields
Query Optimization Resources
Index Selectivity Exceptions
• Best Practices for Deployments with Large Data Volumes (white paper)
• The following filter operators
In addition to this cheat sheet's previous sections, we recommend reading the following related resources, which can help you retrieve the records you want from a large volume of data—and do so quickly and efficiently.
When you build a filter condition with the following operators, Force.com doesn't use an available index. Instead, it scans all records in the object to find the records that satisfy the condition. Feel free to use these operators, but be sure to add selective filter conditions.
• Force.com Apex Code Developer's Guide (guide)
»»not equal to
• Force.com Blogs: Engineering (blog posts)
»»contains
• How to Improve Listview Performance (Salesforce Knowledge article) • In the online help: »»"Build Effective Filters" »»"Getting the Most Out of Filter Logic" »»"Improve Report Performance"
»»does not contain
• When used with text and text fields, the following comparison operators »»Less than ()
»»Less than or equal to (=) Additionally, Force.com doesn't use available indexes when you use: • Leading wildcards • Non-deterministic or cross-object formula fields
SOSL Fields with Search Indexes
Search Selectivity Tips
General
General
Sidebar Search and Advanced Search
• Name fields
• Be as selective as possible. For example, use Michael*, not Mich*.
• Search for the exact phrase with an advanced search.
• Phone fields • Text fields • Picklist fields These fields vary by object. See "Search Fields" in the online help.
• Remember that Chatter feed searches aren't affected by the scope of your search; Chatter feed search results include matches across all objects.
• Limit scope by targeting: »»Specific objects »»Rows owned by the searcher »»Rows within a division, when applicable
See "Search Overview" in the online help.
http://developer.force.com
