Tableau Performance Series - Custom SQL Connections

We have updated our Power Tools branding. Any references to software/feature naming conventions may no longer be accurate in this article.

We’re starting a blog series dedicated to helping you improve the performance of Tableau in your environment. It all starts with the Best Practice Analyzer from Workbook Tools for Tableau. This feature lets you know exactly how you can improve your Tableau workbooks to run faster and smoother.

Each post in the series is related to specific performance issues that are flagged by the Best Practice Analyzer. If you don’t have it yet, get it. You can also check out more guidelines found on our Tableau Performance Checklist.

Let’s begin by talking about the pros and cons of Custom SQL connections.

We know, it’s so tempting. Trust us, we get it. For a long time, many of us used Custom SQL connections as our personal default when working in Tableau. We were wrong. In most other tools, this would actually be a way to increase performance. Unfortunately, in some cases, using a custom SQL connection in Tableau can actually degrade performance.

More Flexibility

Yes, you do have more flexibility when you write your own query into the Custom SQL dialog. In some cases, you can write a query here that you can’t get otherwise (like a UNION query or in some connections a FULL OUTER JOIN). The best practice in these cases is to move that query you would have used in the Custom SQL connection into your database and save it as a view or stored procedure. If you don’t have the option of saving to the database, you need to understand that the flexibility of the Custom SQL connection comes at a price.

Poor Performance

Imagine you have a view that shows sum of Sales by Region:

Normally, Tableau would write a query like the following to build this view:

In cases where you’ve used a Custom SQL connection, Tableau will return the result set for the query you put in the Custom SQL dialog. THEN, it will filter that result set to only the fields it needs for the view you’ve created. In other words, your query will become a subquery to the outer query that Tableau writes in order to build the view you designed. That query might look more like the following:

As you can see, this would be more work for the database and would probably result in poor performance.

Lessons Learned

If you can move a query from the Custom SQL dialog to the database, do it. If you can’t AND you’re working from an extract, don’t worry about it. You might have a slightly slower query, but it will only run once as you build (or refresh) your extract. If you can’t move the query to the database and you have to use a custom query (maybe for a UNION join), understand this: with great power, comes great responsibility.

Curious about other Tableau workbook performance best practices? We have a growing list of posts to help you out. Here are the topics we’ve covered so far:

Power Tools for Tableau