The Tableau Performance Checklist: Custom SQL – Avoid Parameters

Data

The Tableau Performance Checklist: Custom SQL – Avoid Parameters

by Anthony Ball

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.

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.

Create Custom SQL Parameter in Tableau

 
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.

Edit Custom SQL in Tableau

Performance Problems

Performance problems come from two places when using parameters in custom SQL:

  1. 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.
  2. 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.

Contact Us!

Want more The Tableau Performance Checklist

  1. The Tableau Performance Checklist
  2. The Tableau Performance Checklist: Data – Keep Analysis Simple
  3. The Tableau Performance Checklist: Data – Bring in Only Needed Data
  4. The Tableau Performance Checklist: Data – Use ‘Describe’ to Explore
  5. The Tableau Performance Checklist: Data – Remove Unused Columns from Extracts
  6. The Tableau Performance Checklist: Data – Use One TDS File
  7. The Tableau Performance Checklist: Data – Use Extracts
  8. The Tableau Performance Checklist: Filtering – Minimize Quick Filters
  9. The Tableau Performance Checklist: Filtering – Avoid ‘Only Relevant Values’ in Quick Filters
  10. The Tableau Performance Checklist: Filtering – Avoid High-Cardinality Quick Filters
  11. The Tableau Performance Checklist: Filtering – Avoid Quick Filters That Drive Context Filters
  12. The Tableau Performance Checklist: Filtering – Keep Range Quick Filters Simple
  13. The Tableau Performance Checklist: Filtering – Use Dashboard Filter Actions
  14. The Tableau Performance Checklist: Filtering – Don’t Be Lazy with User Filters
  15. The Tableau Performance Checklist: Custom SQL – Limit in Live Connections
  16. The Tableau Performance Checklist: Custom SQL – Avoid Parameters
  17. The Tableau Performance Checklist: Custom SQL – Watch for Useless Clauses
  18. The Tableau Performance Checklist: Calculations – Use Calculated Fields Carefully
  19. The Tableau Performance Checklist: Calculations – Limit Blended Calculations
  20. The Tableau Performance Checklist: Calculations – Avoid Row-Level Calculations Involving Parameters
  21. The Tableau Performance Checklist: Rendering – Avoid High Mark Counts
  22. The Tableau Performance Checklist: Rendering – Limit Text Tables With Lots of Marks
  23. The Tableau Performance Checklist: Rendering – Minimize Image & Shape File Sizes
  24. The Tableau Performance Checklist: Rendering – Use Transparent Background PNGs
  25. The Tableau Performance Checklist: Local Computations – Server Performance
  26. The Tableau Performance Checklist: Local Computations – Table Calculations
  27. The Tableau Performance Checklist: Dashboard Layout – Limit Number of Worksheets
  28. The Tableau Performance Checklist: Dashboard Layout – Fix Dashboard Size

More About the Author

Anthony Ball

Analytics Consultant
The Tableau Performance Checklist: Local Computations – Table Calculations Here’s the second item under Local Computations: “Table calculations are powerful, but they can be slow. They are dependent on ...
Tableau Conference 2015 Told in Selfies Tableau Conference 2015 officially ended a little over a week ago, and I’m already missing Vegas and all my fellow InterWorkers. ...

See more from this author →

Subscribe to our newsletter

Share This