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.
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 analternative. 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 optionis to use a data extract with your custom SQL connection. Your query will only run oncewhen 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 PracticeAnalysis 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 ourPerformance 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!
