Tableau CRM faster Dashboard – Efficient Queries

In the previous section, we explored cosmetic changes which can help make your dashboard load faster. In this part of the series, I’ll talk about how you can make your queries more efficient to improve your dashboard performance.

Avoid Post Projection Grouping/Filtering

Pre-projection queries, particularly those dealing with rows numbering in the hundreds of thousands or more, will execute much faster than post-projection queries dealing with the same number of rows as tabular data. So, instead of –

…where the filtering and grouping occur after projection (foreach), change the order so the filtering and grouping occur before projection –

Conclusion: A “slow query” can be improved by rewriting the query to perform grouping and filtering before projection.

Redundant Filters

Is your query doing more work than it needs to? Check to see if you have redundant filters. Logically, it’s easy to write multiple filters to achieve your goal, but often you end up with redundant filters. It’s even possible to generate redundant filters when setting up binding and faceting.

Consider the query in the image below –

Even though the filters in this example occur before projection—before the foreach statement—and so are highly optimized, the second filter is redundant and causes unnecessary work for the query engine. Why is it redundant? The results will be the same even without the “5 years ago” filter.

Multi-Value Dimensions

If you use picklists and find your queries are slow, consider the impact of multi-value dimensions. 

Multi-valued dimensions (for example, those used in multi-select picklists) may cause poor performance because multi-value field behavior is undefined for group by or foreach. 

Conclusion: If you have bad performance due to multi-value fields used in foreach or group by, rewrite your query so multi-value fields are referenced only in filters.

Limit the use of unique()

Sometimes you need to use unique() in a query, but be aware that it can affect performance if there is a large number of unique values.

For example, suppose you want to count the number of different industries that you have opportunities with.

If your data contains a few thousand industries, this query will not negatively affect performance. However, suppose you want to count the number of unique customers (accounts) –

If you have millions of records, be aware that this query will have an impact on performance.

Use ETL Process

Is your dataset set up correctly for what you’re trying to do? You could be doing unnecessary work in your queries. When importing your dataset via the ETL process, it’s important to ensure that your dataset is optimized for likely queries. The ETL process allows the creation of derived fields using calculations based on the current dataset, or even other derived fields.

If you find yourself writing queries with a case statement in the foreach projection, then it’s possible your dataset could be optimized. For example, the following query changes the value JP to JAPAN in the output stream –

Executing this query multiple times can affect performance. It makes better sense to have the dataset reflect the required data accurately. In your ETL process, use the computeExpression transformation, and add your case statement in the saql Expression.

In the next section, let’s discuss how you can leverage advanced network capabilities to make your dashboards more performant.

Written by