3 Replies Latest reply on Jan 13, 2011 10:11 AM by Joe Mako

    Slow quick filter on 40 M rows compared to PowerPivot

    Hugo Lang

      I've imported 40 million rows to Tableau extract for testing its performance with a simple workbook setup and comparing it with the performance in Excel PowerPivot. What hits me is that it takes tableau at least one minute to quick filter a simple table with 30 marks with one column compared to instant result in powerpivot! Am I missing something or can't tableau handle my large amount of data without minutes of delay when using quick filter?

       

      Tableau is installed on a machine with dual 64 bit processor and 36 Gb RAM.     

        • 1. Re: Slow quick filter on 40 M rows compared to PowerPivot
          Joe Mako

          I believe Tableau and PowerPivot function differently and have different advantages. From my understanding in order to work with 40 million rows of data in PowerPivot, you need lots of memory, and in your case you have 36GB, so it is no issues for you to work with it all in-memory. I believe Tableau will only load into memory what it needs to answer the query, many times very little because the Tableau Data Engine is a columnar-style data store, so you can have only 3GB of memory and query billions of rows in Tableau. When you want to display millions of marks at once in Tableau, then you will need extra memory, but generally Tableau is used to aggregate data, so the number of marks is generally much lower.

           

          I can't say for sure without seeing your workbook, but your quick filter may not be built as efficiently as possible. In Tableau there are many ways to accomplish tasks, and and some are slower than others. It could be as simple as optimizing the extract (adding the results of database side calculations into the data extract, so you gain the columnar data store performance gains). In my opinion, many of the speed gains and data size capabilities come because the Tableau Data Engine is a columnar style data store.

           

          Can you provide more details on what you are doing your worksheet? or a screenshot of the entire Tableau window, with the formulas of any calculated fields in use?

          • 2. Re: Slow quick filter on 40 M rows compared to PowerPivot
            Hugo Lang

            I'm actually don't doing any special calculation in my worksheet more than adding a dimension, measure and a quick filter, see screenshot. I can't find any other way to do the filtering in tableau so can you please elaborate on what you mean with quick filter may not be built as efficient as possible. 

             

            When it comes to PowerPivot I can't find that powerpivot are allocating large amount of RAM.   

            • 3. Re: Slow quick filter on 40 M rows compared to PowerPivot
              Joe Mako

              I would recommend contacting Tableau Support, as I cannot reproduce the situation you describe, and they may be able to look at your log files to see exactly what is causing the unusual slowness.

               

              Is the field "WaterfallPosition" a calculated field? Have you tried optimizing the extract: from the menu, Date -> Extract -> Optimize

               

              I have a dataset with 40 million rows as a Tableau Data Engine extract, and when I create a layout similar to what you have, and I get instantaneous results when changing the filter.