The Tableau Performance Checklist series is designed to help you streamline your dashboard performance and Tableau Server configuration. Each post expands upon one item listed in the master Tableau Performance Checklist. As of 2024, you can find an updated Tableau Dashboard Performance Checklist here.
Currently, we’re going through the best ways to employ custom SQL and what to avoid. Here’s what we’ll cover today:
“Avoid parameters in custom SQL in Tableau. Tableau wraps the custom SQL in a subquery that many databases don’t handle well. Consider building a view in the database or use a multi-table join with filters.“
Let’s cut the chit chat and just jump right in, shall we? Great!
Parameters and Custom SQL in Tableau
Simply put, parameters are dynamic values that can be used to replace constant values. They offer a great deal of flexibility in controlling or editing your visualization from inside of Tableau. For example, you could create a parameter to set a performance goal and then use it in a calculation to compare actual results to goal.
Using parameters in custom SQL is a relatively new feature for Tableau, first appearing in Tableau 8. Before that parameters were limited to just filters and calculations. Now, you can use parameters to substitute dynamic values into a query for connections that use custom SQL.
Performance Problems
Performance problems come from two places when using parameters in custom SQL:
- The custom SQL itself – Custom SQL is placed inside of a subquery, and there’s the potential for your database to select more data than necessary as a result of the subquery. This requires more disk IO, more processing and more of your end users’ time to wait for the visualizations to render. Of course, not all databases will behave in this manner.
- The dynamic value in the query – Yes, this is the whole point of the parameter in the first place. However, imagine that Tableau checks its cache by looking for the full query, including that dynamic text. Since users get to control the parameter (again, that’s the point), there’s a higher chance that the cache won’t exist. As with any of these performance tips, this doesn’t hold true all the time. Frequently selected parameters may give you more cache hits than infrequently selected parameters.
Custom SQL reveals some of the internal workings of Tableau beneath the hood. It gives you a great deal of power in creating your dashboards, and some of the best Tableau consultants are by no coincidence custom SQL wizards. But as we learned from Uncle Ben, “With great power comes great responsibility.” So, be a smart superhero, and keep these things in mind when building your visualizations. Your dashboards will thank you for it.
Mastering Best Practices
If you’re interested in becoming a Tableau Server guru, then learning these performance best practices is essential. Check back frequently as we add new posts and dive deeper into each point in the Tableau Performance Checklist.
Another great way to identify best practices is to leverage the insights offered by our Performance Analyzer, part of Workbook Tools for Tableau. It will examine all of your workbooks, worksheets, dashboards and data sources against a list of best practices to ensure that you’re using all the tips and tricks to guarantee your visualizations are moving at light speed.
As always, feel free to get in touch with us if you have any questions regarding performance or anything Tableau related! We’d be happy to help.
