Optomizing SOQL Flashcards
How does Salesforce store Tenant data
Salesforce stores the application data for all virtual tables in a few large database tables, which are partitioned by tenant and serve as heap storage. The platform’s engine then materializes virtual table data at runtime by considering the corresponding metadata
Why does traditional performance-tuning techniques not work in Salesforce?
The Salesforce plaform storage model manages virtual database structures using a set of metadata, data and pivot tables
How long does it take for new data to be searchable within Salesforce?
15 minutes or more
What are divisions?
Divisions are a means of partitioning the data of large deployments to reduce the number of records returned by queries and reports
How can you enable divisions?
Contact Salesforce Customer Support
What is the difference between SOQL and SOSL
A SOQL query is the equivalent of a SELECT SQL statement and a SOSL query is a programmatic way of performing a text-based search
What does the SOQL statements execute with
Database
What does the SOSL statements execute with
Search indexes
What call does SOQL statements use?
query() call
What call does SOSL statements use
search() call
When will you use SOQL (name 5)
- You know in which objects or fields the data resides
- Retrieve data from a single object or from multiple objects that are related to one another
- Count the number of records that meet specified criteria
- Sort results as part of the query
- Retrieve data from number, date or checkbox fields
When will you use SOSL (Name 3)
- You don’t know in which object or field the data resides and you want to find it in the most efficient way possible
- Retrieve multiple objects and fields that might or might not be related to one another
- Retrieve data for a particular division in an organization using the divisions feature
Which search (SOQL or SOSL) is faster when the search expression uses the CONTAINS term
SOSL
How can you improve the performance of a SOQL query that has multiple where filters?
Decompose the single query into multiple queries, each of which should have one WHERE filter, and then combine the results
What type of where filters should be avoided?
Using a where filter that has null values for picklists or foreign key fields
Why would the following query perform poorly (or is it written correctly)?
SELECT Contact__c, Max_Score__c, CategoryName__c, Category__Team_Name__c
FROM Interest__c
WHERE Contact__c != null
AND Contact__c IN :contacts
AND override__c != 0
AND ((override__c != null AND override__c > 0)
OR (score__c != null AND score__c > 0))
AND Category__c != null
AND ((Category_Team_IsActive__c = true OR CategoryName__c IN :selectvalues)
AND (Category_Team_Name__c != null AND Category_Team_Name__c IN
:selectTeamValues))
Nulls in the criteria prevented the use of indexes, and some of the criteria was redundant and extended execution time.
Why would the following query perform poorly (or is it written correctly)?
SELECT Contact__c, Max_Score__c, CategoryName__c, Category_Team_Name__c
FROM Interest__c
WHERE Contact__c IN :contacts
AND (override__c > 0 OR score__c > 0)
AND Category__c != ‘Default’
AND ((Category_Team_Name__c IN :selectvalues AND Category_Team_IsActive__c = true)
OR CategoryName__c IN :selectvalues)
This query is written correctly :)
What problem can you run into if your query uses dynamic values in the WHERE fields
Null values can be passed in. Don’t let the query run to determine there are no records, instead, check for nulls and avoid the query (if possible)
Is anything wrong with the following pseudo code?
SELECT Name
FROM Account
WHERE Account_ID___c = :acctid;
if (rows found == 0) return “Not Found”
If acctid is null, the entire account table is scanned row by row until all data is examined
Is anything wrong with the following pseudo code?
if (acctid != null) {
SELECT Name
FROM Account
WHERE Account_Id\_\_\_c = :acctid
}
else {
return "Not Found"
}
It’s good! And much better than the following
SELECT Name
FROM Account
WHERE Account_ID___c = :acctid;
if (rows found == 0) return “Not Found”
What is the best practice when you want to reduce the number of records to return while searching
Keep searches specific and avoid using wildcards if possible. For example, search with Michael instead of Mi*
What is the best practice when you want to reduce the number of joins while searching
Use single object searches for greater speed and accuracy
What is the best practice when you want to improve efficiency while searching (Hint: What do you need to configure within Salesforce)
Use the setup area for searching to enable language optimizations, and turn on enhanced lookups and auto-complete for better performance on lookup fields
What is the best practice when you want to improve search performance (hint: If you have a lot of region based data)
In some cases, partition data with divisions
What is the best practice when you want to allow indexed searches when SOQL queries with multiple WHERE filters cannot use indexes
Decompose the query - if you are using two indexed fields joined by an OR in the WHERE clause, and your search has exceeded the index threshold, break the query into two queries and join the results
What is the best practice when you want to avoid querying on formula fields, which are computed in real time
If querying on formula fields is required, make sure they are deterministic formulas. Avoid filtering with formula fields that contain dynamic, non-deterministic references
