Einstein Analytics uses SAQL behind the scenes in lenses, dashboards, and explorer to gather data for visualizations. We can modify the existing SAQL queries & write new queries according to the requirement. But there are some cases where the performance of SAQL query is impacted. So, in this article we will discuss how to improve the performance of the SAQL so that you can improve the performance of the Dashboard.
Below are some concepts which are very helpful to improve the performance of SAQL:
Use Projection Efficiently
Before proceeding to this point, we will discuss what is projection? When we use the foreach statement in the query it creates a new stream and the first used foreach statement in the query is called the projection
Pre-projection queries execute faster than Post-projection queries. See below example:
In the above query, filtering and grouping executes after projection (foreach), and if we change the order of the filtering and grouping, it will execute before projection (foreach):
So changing the order of the functions in the SAQL query helps efficiently in improving performance.
Reduce Network Traffic and Latency
Some of us are curious regarding how to manage the Network Traffic & latency as it is not under our control. But we can acquire this by simply following some procedure.
In order to achieve this, we have to optimize our SAQL query. The SAQL query should not perform the Transformation task in the query as it adds a new network route to transform the data. For example, if we want to change the Date format in the query then it will have an impact on query performance.
Remove the Redundant Filters
You are building a query and the query is very complex as the desired data is available only after performing some steps.
So in order to get the desired data, we write multiple filters in the queries. But the queries written can be involved in performing some unnecessary tasks and not the required work. Sometimes we write the steps which are redundant, that means one or more than one steps are having the same logic as the previous step thus creating an extra overload. So we have to make sure that our query is performing only the required work as the number of redundant filters affects the performance of the query.
Use the ELT Process effectively
ELT stands for Extract, Load & Transform. In the previous point, we read about reducing the Network traffic by Transforming the Data. So, this is an important step that you should consider in order to enhance the SAQL query.
While performing this, you should always consider that this step is going to affect the future queries of the SAQ. So before projection or writing any queries, please ensure that your ELT process is performing everything that is required.
Also, please try to use a minimal number of decimal places while creating the Dataflow and sort the query data, as SAQL query works faster in the Sorted data than the Random Data.
Avoid the Multi-Value Dimensions
If you are thinking that the queries are processing at low speed and if you are using a picklist then look for a multi-value picklist as it degrades the performance.
Also, multivalue dimensions are not indexed as well as their behavior is not defined for GroupBy & Foreach. So it is recommended not to use Multi-Value fields other than the filters.
Limit the use of Unique
We should be very careful while using the Unique() because if we are having a large number of unique records, it will affect the performance of execution. For example, if we have millions of opportunities & we want to fetch unique opportunities then it will slow down the execution speed