Identifying Tableau query performance issues through the log files

There are some good reasons to look at the actual queries Tableau sends to a live relational database:

  1. Performance is not good and your DBA needs to know what is happening so they can optimize. This goes for live connections and slow extract generation.
  2. Your viz still isn’t performing well enough even with extracts, but you don’t understand the TQL language you see in the Performance Recorder (it’s okay, no one does!). Seeing the same logic in SQL can help you understand what exactly is going on
  3. You just want to marvel at the amazing ability of the VizQL engine to translate your actions into SQL. You should check out what LOD calculations,  Sets, or calculated fields look like sometime just to marvel at what is going on

Read more for how to accomplish #1

The best method for testing queries is using Tableau Desktop. It has two benefits over Tableau Server — (1) You can very quickly clear out your log files from My Documents\My Tableau Repository\Logs (2) Only your actions are written into the logs with a single VizQL session going. Server logs have multiple processes and you don’t want to go deleting them to single things out.

Note: You should always test with a Live Connection that is embedded into the workbook i.e. don’t use a source that is published to Data Server. This will show you the SQL queries. If you use a Published Data Source, you’ll see an XML ‘theoretical query’ in the Desktop logs and Performance Recording. If you use an extract, you’ll see TQL, the proprietary Extract Engine query language that is not documented anywhere.

One method to see the queries that are sent is to use the Performance Recorder, documented well on the Tableau website. There is also a document on interpreting the performance recording. The only thing I’ll add, is that the Query is often cut off in the viz, so you should do “See Underlying Data” and then copy the results into a text editor or your SQL management tool of choice.

The other method is to look at the Tableau logs. As mentioned earlier, the logs for Desktop live in My Documents\My Tableau Repository\Logs . You want to look for the ones labeled ‘tabprotosrv‘ to see the details of the querying, although sometimes log has the basic query and elapsed time. Since Tableau 8.2 or so, the logs are in JSON format for ease of processing using machine reading tools. I recommend using a tool that cleans up the JSON and makes it easy to read through. The equivalent logs for Tableau Server live in C:\ProgramData\Tableau\Tableau Server\data\tabsvc\logs\vizqlserver\ or the equivalent of that in the zipped up log files (they will be directly under vizqlserver ).

  1. Close Tableau Desktop
  2. Delete all the log files in the Logs directory
  3. Open up Tableau Desktop. Do whatever action gets you to the query where you see slowness.
  4. Stop doing anything in Tableau, minimize it
  5. Go to the log file and look for the latest end-query.

You want to look for the keys that say end-query . These show the full query that was sent and the elapsed time. You can take that query and put it into your SQL tools (using EXPLAIN or other tools) to see why in particular it is running slow. You can only affect the query Tableau generates by changing what you show in the viz — and sometimes a slight change can make all the difference. However, most people then go to their DBA(s) and have them work out optimizations — whether that is doing maintenance, adding indexes, or creating views to move more of the logic down into the database.

Optimizing Custom SQL

Custom SQL leads consistently to particularly poorly optimized queries, because Tableau must wrap its queries around the ENTIRE Custom SQL query every time. As the Tableau queries get more complex, the optimizer in the database system tends to have more trouble creating optimal query execution plans, and thus things get slower.

There are two solutions:

  1. Translate your Custom SQL into a set of JOINs and the appropriate filters in Tableau. Some of the things you’ve built in, you may be doing in Calculated Fields or aggregations in Tableau. This is okay, and can often significantly increase performance and flexibility
  2. Put the Custom SQL into the database as a View. Tableau will see the view as a regular table, and then send a more simplified quickly. This can be useful when using the Custom SQL as the basis for an Extract. It also gives an opportunity for the DBA to optimize where they can.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s