Optimization Tips In a query activity that queries data extensions, use the primary key field in your select statement, or in any join as a predicate. If this query activity is part of an automation, do not add other activities on the same step of the automation. Remove other activities that occur on this step.
Full Answer
How does the query optimizer work with multiple conditions?
The query returns statistics about the distribution of Opportunity records across every week of every year by CloseDate. For filter conditions that combine two or more conditions (using AND ), the query optimizer considers the overall filter condition selective when the filter targets less than:
How can I better understand selectivity in soql?
To better understand selectivity, let’s use an example. You build a SOQL query, report, or list view for the Opportunity object, one of the largest objects in your org. You have a filter condition, for example, a WHERE clause that fetches only the rows you want from the object.
What is the Lightning Platform query optimizer?
The Lightning Platform query optimizer helps the database’s optimizer produce effective queries by providing efficient data access in Salesforce. Where possible, we changed noninclusive terms to align with our company value of Equality.
When does the optimizer consider record visibility when determining a query?
A: The optimizer considers record visibility when determining a query execution plan. If sharing rules do not limit the visibility of data for the user executing a query, then the optimizer can only consider other approaches for record selectivity (indexes, skinny tables, etc.).
How do I improve query performance in Salesforce?
How to improve SOQL query performance in Salesforce?Primary keys (Id, Name and Owner fields).Foreign keys (Lookup or Master-Detail relationship fields).Audit dates (such as SystemModStamp).Custom fields marked as External ID or Unique.
What is Salesforce Query Optimizer?
The Query Optimizer evaluates SOQL queries and SOSL searches. It acts as a sort of traffic cop by routing queries to the appropriate indexes. It looks at every incoming query and assigns a cost value for each potential query path that it identifies. It then uses these costs to determine which execution plan to use.
How are queries optimized?
SQL Query Optimization Techniques There are some useful practices to reduce the cost. But, the process of optimization is iterative. One needs to write the query, check query performance using io statistics or execution plan, and then optimize it. This cycle needs to be followed iteratively for query optimization.
Which feature in Salesforce can be used to optimizer SQL performance?
The Lightning Platform query optimizer helps the database's optimizer produce effective queries by providing efficient data access in Salesforce.
How do I make SOQL selective?
If a SOQL query contains at least 1 selective filter, the query is said to be selective. If the SOQL query doesn't contain a selective filter, the query is said to be un-selective and will require a full table scan. From the perspective of the query optimizer, a filter can be simple or composite.
What is Query Plan in Salesforce?
The Query Plan tool gives you some insight into how Salesforce is going to look for the data. Some fields in Salesforce are indexed by default. By including one or more of those indexed fields in your query, it is faster to retrieve the data (in most cases). Think of it like the phonebook.
How do I make my query run faster?
The way to make a query run faster is to reduce the number of calculations that the software (and therefore hardware) must perform. To do this, you'll need some understanding of how SQL actually makes calculations.
How do you optimize query execution time?
How Can You Select Which Queries to Optimize?Consistently Slow Queries. ... Occasionally Slow Queries. ... Queries With Red Flags. ... Queries That Majorly Contribute to Total Execution Time. ... Define Your Requirements. ... Reduce Table Size. ... Simplify Joins. ... Use SELECT Fields FROM Instead of SELECT * FROM.More items...•
How do you check query performance?
Use the Query Store page in SQL Server Management StudioIn Object Explorer, right-click a database, and then select Properties. Requires at least version 16 of Management Studio.In the Database Properties dialog box, select the Query Store page.In the Operation Mode (Requested) box, select Read Write.
How do I query more than 50000 records in SOQL?
You cannot retrieve more than 50,000 records your SOQL calls in a single context. However, with Batch Apex your logic will be processed in chunks of anywhere from 1 to 200 records in a batch. You'd need to modify your business logic to take the batching into account if necessary.
How do you solve too many SOQL queries 101 in Test class?
Resolve the "Too many SOQL queries: 101" error To fix the issue, change your code so that the number of SOQL fired is less than 100. If you need to change the context, you can use @future annotation which will run the code asynchronously.
What is indexing in Salesforce?
When a field is indexed, its values are stored in a more efficient data structure. This takes up more space but improves performance when using two or more filters with indexed fields in a query. The fields indexed by default include: Primary Keys: Id, Name, Owner, Email (Contacts, Leads).
Optimization Tips
In a query activity that queries data extensions, use the primary key field in your select statement, or in any join as a predicate.
Intermediate Tables
Use intermediate tables to divide a query that gathers data from multiple data extensions into smaller queries. Each smaller query writes results to a holding table. Once the smaller queries have run, a final query unites the results in usable form.
What is Salesforce multitenant architecture?
The Salesforce multitenant architecture uses the underlying database in such a way that the database system’s optimizer can’t effectively optimize search queries. The Lightning Platform query optimizer helps the database’s optimizer produce effective queries by providing efficient data access in Salesforce.
Can you determine if a filter condition is selective?
With some simple SOQL queries, you can easily get the necessary statistics to determine whether a specific filter condition is selective. After you index a selective field, your queries with related filter conditions can execute more efficiently and your users can be more productive.
Deltas
Above all other concerns, minimizing the data being processed is the best approach to improving performance.
Contention
Query Activities operate within the data environment utilized by the Marketing Cloud. Similarly, most other Marketing Cloud processes operate within this environment.
Relational Data
The Marketing Cloud data environment, exposed via Data Extensions, is relational. It is possible to designate fields in Data Extensions as a Primary Key. The values stored therein can similarly be placed in another Data Extension to operate as a Foreign Key.
Indexes
SQL code that relies upon fields designated with an index performs best.
Optimize the "source"
For a specific Query Activity, it is possible to eliminate any concern about contention at the source data set, gain a desired index via a Primary Key, gather data to be used as a foreign key, and constrain the data set to only the needed rows. This is done by "staging" the source data for the Query Activity, often using a preceding Query Activity.
Optimize the "SQL"
Writing effective and performant SQL code is a topic beyond the scope of these best practices, but three key considerations apply to the SQL used by all Marketing Cloud Query Activities: target behavior, joining, and SARGable conditions.
Optimize the "destination"
If the above considerations are achieved, then there are few performance considerations for the destination of a Query Activity. Avoiding contention with other processes is, as always, critical.
