The Tableau Performance Checklist: Custom SQL – Watch for Useless Clauses

Data

The Tableau Performance Checklist: Custom SQL – Watch for Useless Clauses

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 Overview

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):

Add new custom SQL query in Tableau

Once you click on this option, you’ll see a screen where you can enter in whatever SQL you wish:

Edit Custom SQL in Tableau

Extra SQL

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.

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

Jubail Caballero

Analytics Consultant
Alteryx Tool Spotlight: Compare Values Across N Groups with Dynamic Replace Imagine you are supplied financials from multiple vendors and have the wonderful task of comparing them to each other? With two ...
Alteryx Batch Macros – Email Customized Reports to a List of Users Have you ever had the pleasure of emailing customized sales reports to 25 regional managers? How about 100 customers? It probably took ...

See more from this author →

Subscribe to our newsletter

Share This