17 thoughts on “Why is my Tableau viz slow?”

1. A really great post. I hadn’t come across many of the calculation suggestions before, so it was nice to see all of them collected in one place. Is there a reference somewhere that explains why these things help performance? For example, why is a double equal sign better than a single equal sign? I’m also curious about the “show tabs” on server…seeing the tabs is a great advantage for the user, so I’d rather not hide them if possible. But if there is a performance impact, I’m curious to understand it.

   Reply

   1. Thanks for your comment. Most of those calculation tips are general programming principles rather than Tableau specific things. In the case of the double equals, == tests for identical items, whereas = computes whether they are equivalent. The difference is probably negligible unless you’re working with billions of rows.

      The unticking of show tabs means that Server won’t load the whole workbook on loadup. I thought you could get around that by putting in navigation buttons (using action filters) but it seems that doesn’t work. Damn. Only relevant when you’re showing 1 dashboard then.

      Reply

2. Hi Robin,

   Great post, very useful resource to have this all in one place.

   One thing we found when profiling our queries against SQL server was that if you have DateTime columns, do NOT adjust the Field Properties to change the type in Tableau from DateTime to Date.

   You might be tempted to do this because SQL server prior to 2008 R2 only has type DateTime and not Date. However, if you do, and then you try and filter on your date, you will see this in your SQL WHERE clause: CAST( FLOOR( CAST( CAST([MyDateTimeColumn] as datetime) as float) ) as datetime)

   This is bad because it means certain indexes can’t be used and your queries are slow. We saw a big speed boost by leaving the data type as DateTime in Tableau, and changing the default format instead to strip the time. That way you don’t get the CAST and FLOOR, and you can use your indexes.

   Thanks,
   Sheel

   Reply

   1. Ah very interesting. That’s a really good point in general there… looking at the logs to see what SQL Tableau is passing back to the data source and killing anything odd.

      Thanks for taking the time to post!

      Reply

3. Can you please walk me though me why “Use IIF(…) rather than IF … THEN …” makes Tableau faster?

   Here are my initial thoughts:

   Tableau formula with:
   IF [Profit]>100 THEN "100+" ELSE "Less" END

   Tableau produces a query like:
   SELECT IIF(([Orders$].[Profit] > 100),'100+','Less') AS [none:Calculation_7450117074311365:nk]
FROM [Orders$]
GROUP BY IIF(([Orders$].[Profit] > 100),'100+','Less')

   while

   Tableau formula with:
   IIF( [Profit]>100 , "100+" , "Less" )

   Tableau produces a query like:
   SELECT IIF(([Orders$].[Profit] > 100),'100+',IIF(NOT ([Orders$].[Profit] > 100),'Less',NULL)) AS [none:Calculation_6140117074356271:nk]
FROM [Orders$]
GROUP BY IIF(([Orders$].[Profit] > 100),'100+',IIF(NOT ([Orders$].[Profit] > 100),'Less',NULL))

   IIF has more logic built in, for the return argument when the expression is unknown, eg null or invalid.

   IF does not have this logic built in, and will produce a query to the underlying data source with less SQL.

   My first thought would be IF is faster to evaluate than IIF, but of course this would require testing to say for sure.

   Why do you think IIF is faster?

   Reply

4. Also, just saying “Use WINDOW_SUM rather than TOTAL” is not useful.

   In some cases it is true, and in others it is not. Why not explain why it is faster or slower?

   Also they are very different computations and purposes (agg of an agg or agg at a different level), outside the scope of this post, but I feel like your comparison here ignores that.

   Reply

5. And another that I wonder about:
   “Use == (double equals) rather than = (single equals)”

   Why do you think this is so?

   I also wonder about:
   “Avoid lots of CASE statements”

   I normally think of CASE statements as shorthand for IF/ELSEIF statements. If you need the logic of a CASE statement, what would be more efficient? (or course the ideal is to have it be a row level calc, and materialize with optimize, or compute prior to Tableau)

   There are more cases here where your suggestions are not always true, sometimes yes, but you do not mention the exceptions or explain why these suggestions work. I feel this sets someone up for failure when they blindly follow your suggestions and get a slower interaction.

   Most of what you have here is good suggestions, and I hope my comments here are seen in the light of http://mjtsai.com/blog/2012/12/27/the-hypercritical-way/ If I have phrased something poorly, or got something incorrect, please let me know.

   Reply

6. Hi Joe, always happy to receive your comments, please keep them coming!

   I made a mistake with the IF vs IIF — IIF will compute both sides of the output regardless of the result of the condition whereas IF…THEN usually doesn’t. However, in Tableau 7, IF…THEN does not have this advantage (according to Richard Wesley’s presentation on high-performance calculations in San Diego). I think this is what confused me into thinking IIF must be quicker. This is apparently fixed in V8.

   Have to give credit to Mr Drummey for also pointing this one out

   Reply

7. From my programming days I learnt that a double equals was only ever a test for equality i.e. 2==2 evaluates to TRUE.

   A single equals can also be used for assignment e.g. a=2 so has multiple functions.

   I admit I haven’t evaluated whether Tableau has any difference in the way it differentiates between these two, and if there is it’s likely to be infinitesimal anyway. Probably one more for helping readability instead.

   Reply

   1. What “=”, “==”, or “===” evaluate to depends on the language. In the Tableau calculated field formula editor, “=” and “==” are equivalent. There is no assigning values to variables in Tableau formulas.

      I believe Tableau made them the same so users from from different environments would be more comfortable, “=” for users from Excel like backgrounds, and “==” for those with a programming background. So users can keep their convention and use the style that makes the most sense to them.

      I think it is false to claim that one is faster to evaluate than the other.

      Reply

8. My mentions around multiple CASE statements was prompted by workbooks I have been handed that use 50+ conditions on multiple string fields, in multiple custom calculations. If there are that many re-definitions to, do then a joining table in the data source is likely to be a much better option.

   No doubt that CASE is a very useful function and I make use of it all the time when there are simpler groupings to be made.

   Very happy to be corrected, I have made an edit to the article on the IF statement bit. Apologies to anyone who used the tips but didn’t get a result.

   Reply

9. Why shouldn’t you use “Show sheets as tabs”?

   Reply

   1. Eric,

      When you use this option, Tableau has to retreive the entire workbook from the repository, rather than just the specific sheet, thus more compute power is required which may not be necessary if you don’t use the other tabs.

      Reply

10. I see from your article that you do not recommend using custom SQL.

    What would you suggest as a best practice for extracting data from a Microsoft SQL Server?

    Would you suggest using stored procedures?

    Thank you,

    Connor Singerline

    Reply

    1. Hi Connor,

       The best option would be to utilise the multiple tables option in the Tableau data connection dialogue. Tableau will only use the tables it needs to return the data you have asked for (thru a method called join culling)

       If you can’t use the multiple tables then custom SQL with an extract would usually work OK. In those cases I prefer to write a database view and connect to that thru as the view is easier to maintain and document.

       Robin.

       Reply

11. There are some good points here. Regarding the equality operators ‘=’ and ‘==’, I don’t understand why you suggest Use == (double equals) rather than = (single equals). As far as I’ve been able to determine in reading Tableau documentation and doing my own experiments, the two operands are equivalent.

    Reply

    1. Phillip,

       You’re right, there is no difference there, that was a mistake. The article needs to be updated, thanks for the reminder 🙂

       Reply