The Tableau Performance Checklist: Custom SQL – Limit in Live Connections

Data

The Tableau Performance Checklist: Custom SQL – Limit in Live Connections

by Zack Gorman

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.

The rule that we’ll cover today is:

Limit custom SQL in live connections as they can be inefficient. Where possible, create a view on the database server to implement your custom SQL and connect Tableau to your view.

In other environments and in other tools, using your own custom SQL connections would be a way to improve and optimize performance. Not necessarily in Tableau. Instead, it might actually be a significant impediment to your workbook’s performance.

Here are some insights on why limiting the amount of your custom SQL connections will create faster visualizations.

Performance Impact

The reason that custom SQL connections can potentially cause a performance impact is that the SQL query is issued to the database inside of a subquery. Even though your SQL statement may run extremely efficiently on its own, when placed in a SQL subquery that is already burdened with other GROUP BY, ORDER BY, WHERE and more types of clauses, it can become cumbersome and slow quite quickly.

Custom SQL

Above: An example of some custom SQL.

Be very careful adding custom SQL connections, because the nature of a subquery means you’re not operating in a vacuum.

Custom SQL also prevents Tableau from using Join Culling. Often times, for things like quick filters, Tableau will only be grabbing information from one table. When Join Culling is active, Tableau will only send a query to the necessary table(s) instead of all tables in the connection. Since Tableau can’t use Join Culling with custom SQL, this means every table gets hit on every query.

Move It to the Data Source

Rather than using custom SQL connections inside of Tableau Desktop, we suggest an alternative. Use your SQL statements to create a view inside of the database, then connect that to Tableau. This will streamline your efforts without hunkering it down with all of the other SQL that Tableau generates without the custom SQL subquery.

Extracts

If you cannot create a materialized view in the database, another option is to use a data extract with your custom SQL connection. Your query will only run once when you build or refresh your extract. The overall impact on your visualizations performance should be minimized.

Finding Custom SQL Connections

If you’re worried that some of your existing or legacy workbooks are being negatively impacted for performance by custom SQL connections, then fear not. You do not need to go through them manually one at a time to determine if they are using custom SQL. Our Workbook Tools for Tableau comes with the Best Practice Analysis tool, which can pinpoint this exact issue in a matter of seconds. Even better, Workbook Tools comes with a free 14-day trial.

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

Zack Gorman

Analytics Consultant
The Tableau Conference 2016 Session Finder Tableau Conference 2016 is rapidly approaching, and data visualization aficionados from around the world will descend upon Austin. ...
Wheel of Presidents: A Different Way to Visualize Presidential Data in Tableau People working with Tableau come from many different backgrounds. They’re analysts, data journalists, graphic designers or just curious ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK