1 Reply Latest reply on Mar 4, 2014 7:33 AM by Zach Leber

    Quicker 'Quick' filters

    Ayman Teradata

      When filtering large data-sets, it can become a big headache when using quick-filters. The reason for this is that the quick filter fires a SELECT DISTINCT on the column you are filtering on, which can take some time with larger data-sets and slower database systems!

       

      To make your quick-filters 'quicker' I will try to compile a list of hints:-

       

      1. Use the quick-filter on a lookup table: The idea here is to create a multiple-tables data connection with the main details table joined to a lookup table for a specific column you will filter on. The quick filter should then be created on the lookup-table column and not the main table column. This way, SELECT DISTINCT from a few rows lookup table will be 'quicker'.
      2. Put an index (e.g. secondary index in most DBMSes) on the columns you will use with a quick filters (it will select distinct from the list of values in the subtable for the index which should be 'quicker'!!)
      3. Don't use a quick filter altogether! Alternatively, use something else like:-
        1. A parameter: Create a parameter, show parameter control for the sheet, then create a filter on the column with a condition that the column's value is equal to the parameter value. The parameter can store 'hard-coded' values for the column which you know in advance are the only allowed values. The parameter can act like the quick-filter with the same selection options like drop-down list, check-box, etc.
        2. Use the filter itself, without the quickf-filter ... i.e. you filter with specific values hard-coded into the defined filter without needing to show a quick-filter on the sheet.

       

      Anybody wants to add or correct those points?