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.
Today we’re examining the following guideline:
“Watch for useless clauses, e.g. ORDER BY. Tableau is going to re-sort the data once loaded anyway.“
Let’s get started!
SQL, or Structured Query Language, is designed for managing data within a relational database. It is essentially composed of two parts:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
As a complete language for data management, SQL includes a wide variety of operators and controls to query, present and edit existing data.
Tableau gives you the ability to write custom SQL for total control and flexibility on how your data is retrieved for your visualizations and dashboards. To do so, double-click on the New Custom SQL button on the data source page (NOTE: this option is not available for all data sources):
Once you click on this option, you’ll see a screen where you can enter in whatever SQL you wish:
Outside of Tableau, there are a number of useful SQL operators that are vital for database administrators. Inside of Tableau, some of these commands become, well, useless. That is because Tableau does a lot of what SQL does when it organizes and visualizes your data.
For instance, the command ORDER BY is important when producing a result set directly from SQL. You can order your results alphabetically to make it easier on the consumer to find relevant results. Tableau does all of this within Tableau Desktop.
Since custom SQL operates in a sub-query once it’s issued to the database, we would be instructing the database server to order the contents of the sub-query and then subsequently order the data in whichever way we’ve asked Tableau. Doing this work twice is sub-optimal and provides no extra benefit. Since there’s no benefit to the sub-query’s ORDER BY clause, it’s best to leave it out and not experience the performance hit.
Tableau is a very powerful tool, and the ability to write your own custom SQL only extends that versatility. The trick is to know how to change your SQL to give Tableau exactly what it needs to produce the desired result and nothing extra. Extra SQL, while seemingly harmless, may well slow down your visualization or dashboard. The faster you can render your dashboards to your report consumers, the better.
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.