3 Replies Latest reply on Mar 20, 2013 3:42 AM by Daniel Ong

    Performance of filter when using multi-select

    Nathan Schofield

      Hello,

       

      I am testing 3 different ways to filter for performance and noticed some
      peculiar behaviour I wanted to validate with some experts.
      I am testing:


      1. standard Tableau filter (multi select)
      2. multi-select parameters as explained here (http://community.tableau.com/message/202351#202351)
      3. actions as filters


      I am testing using a data source that is published to Tableau Server which
      is a connection to a Postgres DB. What is peculiar is how option 1 varies
      wildly when comparing it's performance with single select against multi
      select. Most of the result times are around 20 seconds or less (sometimes
      much less). But for option 1, when I use multi select it is over 7 minutes!
      I have not looked into the code produced as I am not proficent at
      understanding performance by looking at code, but wanted to see if anyone
      understood why this occured. Also, I note that the large times dissappeared
      on repeat. I imagine that Tableau Server cached the results?

      Looking forward to hearing any explaination.

       

      Nathan Schofield

        • 1. Re: Performance of filter when using multi-select
          Daniel Ong

          Hi,

           

          Is the dimension that you want to filter on the row/column shelf, level of detail or anywhere else in the view (other than filter shelf)?

           

          If the filter is only on the filter shelf, then each time a selection is made, say a or b, then a new query would be sent to the database similar to for example select sum(some measure)  where dimension not a or b. This is highly inefficient as the aggregation has to be computed at the database each time the selection is changed. Furthermore, this type of query with the where clause, goes line by line to compute the summation where the matches appear. This is slower than using a group by clause mentioned below.

           

          However if the filter is part of the view, then the query would be sent once to the database amounting to something like select sum(some measure) group by dimension. This would produce an aggregated already grouped by the members of the dimension. Subsequently any selection changes would not resend the query to the database, but calculated locally. Also, group by queries are much faster than the one in the paragraph above.

           

          Also, if there any other quick filters with relevant values set, that would also send queries to the database to populate that filter list whenever a selection is made.

           

          Daniel Ong

          • 2. Re: Performance of filter when using multi-select
            Nathan Schofield

            Thank you Daniel,

             

            That really helps, thank you for taking the time to explain it. I have one follow on question:

             

            I have previously heard that quick filters can be inefficent. Does what you've explained mean that if the item that is being used as a quick filter is as well included in the view that after initial viz population, quick filter calculations would take place locally, and be more efficent?

             

            Regards,

             

            Nathan

            • 3. Re: Performance of filter when using multi-select
              Daniel Ong

              You're welcome, Nathan.

               

              Yea generally too many quick filters can slow down the workbook. If it is included in the view, then as you select the different options in the filter, it will not resend queries to database, and the 'slicing' will be done locally.

               

              An alternative to quick filters is using action filters.

               

              In v8, we will see more improvements in the efficiency of quick filtering.

               

              Daniel Ong

              1 of 1 people found this helpful