14 Replies Latest reply on Nov 13, 2013 5:39 PM by Matt Lutton

    Performance Tips on Workbook

    andy.yu

      Hello Everyone,

       

      I was attempting to research performance tips and found some documentation through the Tableau website and various forums, but wanted to see if anyone else had some idea's as well.

       

      • The workbook is basically a pivot table, with 7 worksheets, and 6 quick filters on each worksheet.
        • The quick filters are set as "Global filters"
      • The underlying data has about 5,000,000 rows
      • We created an extract file for this workbook as one way to improve performance
      • The workbook is published to Tableau Server

       

      The performance issue we are having is:

      • When selecting / deselecting a quick filter, it takes almost 30+ seconds just to have the workbook return data
        • I know we can pause automatic updates and then renable it after we select all of our quick filters, but performance still lags when doing that
      • When selecting / deselecting a hierarchy in the pivot table, again, it takes almost 30+ seconds just to expand or minimize the hierarhcy

       

      So for our users, just to do some simple analysis and look at the data, they're basically sitting there for 1 - 2 minutes to have data returned to them. I wanted to see if there were anything I could do to somehow speed this up.

       

      • Using Tableau 7 (Server and Professional)
      • Using a custom SQL, but only joining 2 tables

       

      • I was thinking of putting some index's on the table, but since it's an extract not sure if that will even help the situation.
      • Look at the SQL generated to see if we could somehow analyze the query to improve it?

       

      Any help or ideas would be greatly appreciated!

       

      Thanks so much for any help or guidance provided.

        • 1. Re: Performance Tips on Workbook
          Matt Lutton

          Are you utilizing any context filters?  Would it be possible to join your tables using the multiple tables option inside Tableau, instead of using Custom SQL?  Both of these could have an impact, although I'm not sure how much the latter matters when using an extract.

           

          Any complex table calculations that could be optimized?  Any huge text tables/crosstabs?  Are many filters set to "only relevant values"?  Any string calculations that could be revised?

           

          It would help to see the actual workbook; I understand that may not be possible, but it would be the best way for you to get help.

           

          Lots of good info and links here: Performance | Drawing with Numbers

           

          Additionally, upgrading to the latest versions will likely increase performance.

          1 of 1 people found this helpful
          • 2. Re: Performance Tips on Workbook
            Russell Christopher

            If you are using extracs, filter seletion shouldn't cause a 30 second lag.

             

            You mention "Pivot-Table" visualizations. If you look at each one by itself in Desktop, how many "Marks" are being drawn in each?  You want to keep this number as low as possible. If you are showing a 50 column table with 100K rows, you're drawing alot of Marks (cells) that no one will really look at anyway:

             

            ScreenHunter_10 Nov. 13 18.21.gif

            If you are drawing 30-40K+ marks in multiple sheets, you're asking Tableau to render lots of data...and that takes a while.

             

            My real guess is that you have set all your quick filters to "Relevant Values" which causes them to fire extra queries:

             

            ScreenHunter_11 Nov. 13 18.24.gif

             

            Let us know what your "biggest" sheets look like in terms of the # of marks, and how many of your quick filters use "Relevant Values" instead of "All Values in Database". I suspect that by lowering marks and use of "Relevant Values", you'll be fast in no time at all.

            • 3. Re: Performance Tips on Workbook
              andy.yu

              - Matthew,

               

              Thanks for the link, I read a little about context filters, but haven't tried implementing it yet. May try to implement it to see if there are any improvements.

               

              - Russel

               

              1. Out of those 7 worksheets, the largest number of marks on a worksheet is 295,000 marks with 12 columns
                • When you first open it up, there are about 9,300 marks, but as the user expands the hierarchy it comes up to 295,00 marks
              2. Out of the 6 quick filters, 5 of them are using "Only Relevant Values" instead of "All Values in Database"

               

              When in Tableau Professional it feels a bit quicker, but once I publish it onto Tableau Server and look at the workbook from the server, that's when everything seems to be slow down.

               

              The "Pivot-Table" screenshot you had is exactly more or less what our workbook looks like.

               

              Thanks everyone for the assistance

              • 4. Re: Performance Tips on Workbook
                Matt Lutton

                Russell's suggestions are good ones, and I'll let him guide you through the rest--first, changing a few of those "only relevant" values to "all values in database" will have in impact.  I typically only set one or two "only relevant values" filters.  As an example, in my dashboards, I might have a quick filter for a school name, a teacher list, and a course list.  I'd set the school filter to be a Context filter (because we want to focus on one school at a time, and doing so reduces the data being returned in a significant way), the teacher list to "all values in context", and the course list to "only relevant values".

                 

                Hope you get it figured out. Note that there are also "performance recordings" for both Desktop and Server that can help you analyze what is happening behind the scenes (requires some knowledge of SQL/what queries look like)

                • 5. Re: Performance Tips on Workbook
                  Russell Christopher

                  Right. Desktop can use “tricks” (like leaning on your Video Card) to render “lots of stuff” faster. Server generally can’t. 300K marks isn’t great, but not too bad, either.

                   

                  I’d change all your filters back to “All Values in Database”, re-publish and see how much a of a difference you see.

                   

                  New Tableau report writers tend to re-create reports that they’ve made before (in Excel). These “column and row” reports don’t perform well when they have to render lots of marks. I bet the 9.3K mark reports renders fairly quickly on Server, but not the 300K one, yes? It actually is pretty rare for users to REALLY be able to use something  that shows that much data – they just ask for it all because that’s what they’re used to

                   

                  I’d trim down how many rows you show, or show it graphically instead of like an Excel spreadsheet.  Also, you generally don’t want to stick too many views in the same dashboard. We have to render each one – so the more you stick in there, the longer it’ll take

                  1 of 1 people found this helpful
                  • 6. Re: Performance Tips on Workbook
                    andy.yu

                    Thanks Matthew for the advice!

                     

                    I'll definitely try the context filters to see if it improves performance and limit some of my quick filters (where it makes sense) instead of putting them everywhere.

                     

                    Do you know where I can find these "performance recordings"? Is it part of Tableau or a 3rd party software? I would love to have something like that to see the SQL it's firing off.

                    • 7. Re: Performance Tips on Workbook
                      Matt Lutton

                      You're welcome.

                       

                      The performance recording ability is built right into Desktop and Server.  A 3rd party tool exists as well.  I think you can also find the underlying queries within your log files in your "My Tableau Repository"

                       

                      Tableau Software Performance Analyzer | InterWorks, Inc.

                       

                      The built in performance recording info is here:

                      Create a Performance Recording

                      Interpret a Performance Recording Workbook

                      • 8. Re: Performance Tips on Workbook
                        andy.yu

                        Thanks Russel for the advice!

                         

                        Let me change all the filters back to "All Values in Database" and hopefully the performance is a bit better.

                         

                        You're correct the 9.3K mark reports does perform a bit quicker, but as they expand the hierarchy it and change filters around, it starts slowing down and users are basically sitting there waiting for the report to render.

                         

                        And you're right about the reports ... these are simiilar reports that the users had before in Excel, so we replicated the way they saw them ... . That's one of the reasons we have 7 worksheets in our workbook.

                         

                        It's basically the same identical data, displayed a little differently in each worksheet.

                         

                        Thanks again for both of your assistance on this! I'll have to play around with this and incorporate some of the changes you guys suggested.

                         

                        I'm hoping it'll improve performance

                        • 9. Re: Performance Tips on Workbook
                          andy.yu

                          Thanks Matthew, I never even knew that feature existed in Tableau!

                          • 10. Re: Performance Tips on Workbook
                            Matt Lutton

                            Not a problem! You'll be saying that for some time to come.  The number of "features" and the extensibility of the software is unbelievable (overwhelming at times), and the only way to discover them is to explore--this forum is a great place to learn!

                            • 11. Re: Performance Tips on Workbook
                              Russell Christopher

                              Careful with context filters on fields which also act as quick filters. There’s a not-trivial cost associated with creating the context, and you’ll pay it each time users change the context by playing with  the quick filters. At 5M rows, your users may not notice it, however. The best candidates for context filters are fields that:

                               

                              Will eliminate 90% or more of the rows  in your db

                              Won't be modified by users consistently.

                              • 12. Re: Performance Tips on Workbook
                                Matt Lutton

                                This is a good example of how the documentation can be confusing, and even downright misleading if you don't look in the right place.

                                 

                                The 1/10th stipulation is listed here:

                                http://onlinehelp.tableausoftware.com/v8.0/pro/online/en-us/performance_contextfilters.html

                                 

                                But no mention of not using as a quick filter that users will change.

                                 

                                Neither stipulation is listed here:

                                Using Context Filters to Improve Performance | Tableau Software

                                 

                                And the main Online Help article on Context Filters states simply:

                                 

                                You may create a context filter to:

                                • Improve performance – If you set a lot of filters or have a large data source, the queries can be slow. You can set one or more context filters to improve performance.
                                • Create a dependent numerical or top N filter – You can set a context filter to include only the data of interest, and then set a numerical or a top N filter.


                                If a beginner user is reading that, they're going to start setting context filters all over the place!

                                 

                                While I've heard the 1/10th stipulation before, I have never read not to use them for quick filters users will be changing frequently.  I'm not saying the documentation does not exist, but it is certainly not clear.

                                 

                                I should have noted that in my example, the school filter is typically a user filter, which appears like a context filter inside Tableau (adding to my confusion)

                                 

                                Russell: Thank you for posting here.

                                • 13. Re: Performance Tips on Workbook
                                  Russell Christopher

                                  This is by no means a hard and fast rule. Just try it against a “big” data set and you’ll see. When you add the filter into context a query dialog will open, but instead of saying (“executing query”) it’ll say “creating context” or somesuch.

                                   

                                  Interestlingly enough, I just tried this with perf recorder turned on, and I don’t see extra “creating context” events like I’d expect – so maybe this part isn’t true anymore! Will have to play.

                                  • 14. Re: Performance Tips on Workbook
                                    Matt Lutton

                                    Thank you for clarifying that, this is good to know.  It may be that the data I've worked with isn't quite "big" enough for me to have run into this.

                                     

                                    Anyway, best of luck Andy!