4 Replies Latest reply on Nov 12, 2015 9:59 AM by Rajeev Pandey

    Performance Aspect: Simple Quick Filter or Set based Quick Filter - Which one is Better?

    Rajeev Pandey

      Dear Tableau Lovers,

       

      Could you please explain me which approach is better in terms of Performance?

       

      Question 1 : Suppose I have created a 8 Quick filter in my report which is taking so much of time for creating my Viz . The obvious reason is I am using  too many Quick Filters which is not at all good approach /advisable from Tableau Perspective. Next What I did , I  created a same no of Quick Filter but by using the Set .I just wanted to know will it improve any performance.

      Question 2: What if I am converting Set based Filters into Context Filter.

      Process to be followed :

      • To create the Set-based filter, I simply dragged my  dimension to the filters shelf, then selected all the values and then  right-mouse select the filter pill and selected Create Set...
      • Drag the set to the filters shelf
      • Then again performed the right-mouse click and selected the pill on the filter shelf and performedShow Members in Set.
      • Right-Mouse select the pill and choose Show Quick filter
        • On the quickfilter options, I customized my filter to  multiple values options .Set Filters.png
      • Note : I am using SQl Server 2012 as my database that's contains around 50M records
        •    I am using Tableau 8.3  version.
        •    Also, I am filtering my data through Custom SQL technique.

      It would be great if anyone can explain me by taking some examples because I am failed to understand what the actual difference between these two approaches ?

        
        • 1. Re: Performance Aspect: Simple Quick Filter or Set based Quick Filter - Which one is Better?
          Steve Martin

          Hi Rajeev,

           

          Having not tested this but having a very good idea of Tableau's architecture, I suspect that quickfilters might just be faster than Sets simply as a set is a pre-filtered list of members, Tableau would still have had to perform a select distinct against the relevant db fields in order to obtain the list of underlying members that support the Set.

           

          Think of a Set like this:

           

          1. Identify the distinct members of a field which takes time as every record in the table must be touched
          2. Build a list of these members
          3. From this list, identify which of these members exist within the set

           

          Unfortunately, when it comes to filtering, your options are quite limited to dynamic filters and hard-coded parameters.

           

          Other more complex operations can be used such as using a summary table inner joined to the source table and using this for the source of your parameters though this is where you begin to introduce unnecessary complexity.

           

          Steve

          2 of 2 people found this helpful
          • 2. Re: Performance Aspect: Simple Quick Filter or Set based Quick Filter - Which one is Better?
            Rajeev Pandey

            Dear Steve Sir,

            Thanks for your valuable thoughts. Actual there is one scenario where I have to use 9 quick Filters but because of these   filters my Viz is taking so much of time to load which is an expected Filter. Even i cant use Parameter in custom SQL as there are 9 to 11 Fields are present under one dimension which I m using as Quick Filter.So I thought may be set based Filter can help me ?

            Is there any other approach you would like to suggest that can solve your problem.

            • 3. Re: Performance Aspect: Simple Quick Filter or Set based Quick Filter - Which one is Better?
              Steve Martin

              When you say load, do you mean in terms of first opening?

               

              If this is the case, you can stall the process by pre-filtering and selecting a smaller data set.

               

              Just as an example, when Tableau loads a workbook, it shall scan every record in the dataset to identify the distinct members, but if you can limit the set, you can limit this activity:

               

              One of my smaller tables is around 400 million records - a beast of data based on experiment results made up of around 130 live tests. Naturally scanning the complete table will seriously affect performance so using custom sql and a type-in parameter hooked into the experiment id where clause, I can have the workbook open and ready as Tableau is unable to render against no data.

              Once the user enters their experiment id, the result-set of this experiment becomes the primary set so instead of performing a search against 400M records, Tableau is invariably performing its search against 10M records instead (the tests vary in number of records); result is, the process is faster.

               

              In order to speed things up further, we are using Tableau Server, I have a summary table with the list of experiments and use a url filter that when the user selects the experiment id, it opens the proper workbook inserting the experiment id as it is opening.

               

              So, if you have an overall level your users may want to initially filter on, region or division for example, you could potentially heavily improve performance.

               

              You could also crack open the tableau logs - the data server ones are the ones not prefixed "Log n.txt", identify the code and paste it to your server solution, then use the explain plan function you can see if you need to optimise the source.

               

              Steve

              2 of 2 people found this helpful
              • 4. Re: Performance Aspect: Simple Quick Filter or Set based Quick Filter - Which one is Better?
                Rajeev Pandey

                Thank you so much sir. I will definitely follow your approach and will update you my result.