Performance Considerations with Suggest Filters

done
low_priority
reply

(Josh Siegel) #1

When you open a filter in Looker (i.e. on a string field), Looker runs a simple SELECT DISTINCT <field name> FROM <table> LIMIT 1000 query to get the first 1000 distinct options as suggestions.

If that first query returns before the user starts / stops typing in the filter box, and there are fewer than 1000 results, Looker will make subsequent suggestions very quickly because it knows the universe of possibilities for that field.

If some other text is written in the filter box (e.g. “goodlooker”) and the user stops typing (for 300ms), any previous running suggestion query will get killed and a new one will run with a filter on it: WHERE <field> ilike 'goodlooker%'

If this is a concern because you are suggesting on large fact tables, or don’t want extra queries running on your database, you have a few options:

  1. Add suggestable: false in the LookML for a dimension to turn off suggestions for that field
  2. Manually list the suggestion options you want with suggestions: [foo, bar]
  3. Point to a different table for the suggestion lookup using suggest_explore: and suggest_dimension: in the dimension’s LookML

How do the suggestion for filters populate?
Why won't my filter suggestions populate? (Prior to 3.48)
What is the limit for suggest_dimension element in Looker filter?
How to override disabled suggestions with access_filter_fields and sql_always_where (prior to 3.34)
Using Looker with partitioned columns
How do dashboard field_filters populate with values?
(Cyril Marques) #2

If I may I would also add that Suggestions are deactivated when the explore is using an ALWAYS_WHERE clause.
I understand the concern but this is quite brutal to remove all suggestions.


(lloyd tabb) #3

@Cyril , you should be able to selectively turn selections on. Looking at the code it doesn’t appear to work that way. I’ll write up a bug so we can take a closer look. This is always scary from a security point of view.


(Cyril Marques) #4

Thanks for the reply @lloydtabb. Actually Lindsey came back with a solution in my case to avoid the sql_always_where clause by using another sql condition in the join. Not quite the exact same effect (some minor data discrepancies between the two solutions) but at least suggestions are back on!

Anyway, that does not prevent Looker from still begin awesome to me!


(lloyd tabb) #5

Wow @lindsey, great hack. We’ll get suggest working better, but that’s a fantastic work around.


#6

There is also a workaround by creating a derived table that performs the filtering. Then, suggestions are available when querying that derived table. Not as pretty, but gives the exact same data! So there are a couple possibilities for this currently.


(Zev Lebowitz) #7

As an additional note here, if you want to use suggest_explore and suggest_dimension but don’t already have a table with the distinct values for your dimension, you can create one using a persistent derived table.

In your existing icecream_orders view:

  - dimension: flavor
    type: string
    sql: ${TABLE}.flavor
    suggest_explore: flavors
    suggest_dimension: flavors.flavor

As a new view file:

- explore: flavors
  hidden: true

- view: flavors
  derived_table:

    sql: |
        SELECT distinct flavor
        FROM icecream_orders 

      indexes: [flavor]
      sql_trigger_value: SELECT CURRENT_DATE()

      - fields:
        - dimension: flavor
          sql: ${TABLE}.flavor


(Nicole Beyer) #8

If you are using Zevs workaround and are making use of a User Defined Dashboard rather than a LookML Dashboard take note that if you have added the flag hidden: true to your explore then this explore will not be available in the Edit Filters and Listeners section.


(Maximilian Roos) #9

Is there any way of increasing that 1000 cached suggestions?

We have a BQ table with 100_000 distinct items which we frequently use as a filter. Because of BQ latency, it takes a good 3-4 seconds to get a list of suggestion, and because of humans it frequently requires 3-4 attempts to get the name right - so it takes 10-15 seconds to fill out the suggestion.

Or other ideas for speeding this up?


(sam) #10

Interesting question @Maximilian_Roos. Are you envisioning that if we showed 100,000 results instead of 1000, then
it would take less time to fill out the suggestions?

We show 1000 “per query”, which means that you’ll get 1000 results for any particular search term that you put in.

How frequently is that list of 100,000 changing? I’m wondering if hard-coding is a viable option here, if this is a frequently used and large suggestion list.


(Maximilian Roos) #11

I think I was asking for something more than currently happens - that we could cache all 100k, and then select from those when typing a suggestion. But that would require an extra filter on the Looker side based on the text in the text box.

The list isn’t changing that often - are you thinking we could hardcode that in a file in git? I’m open to it if you think the app could handle it?


(sam) #12

I see what you’re saying, it’s like Looker would cache the results and then run a query on that cached set. There isn’t really a notion of Looker running queries over cached results; we just cache one query at a time. Definitely an interesting idea, though!

I was indeed thinking of hardcoding all the suggestions in a “suggestions” parameter in the lookML. It should be no problem from the git/LookML side. I’m interested to see whether it causes the browser to slow down, but the app itself shouldn’t mind. Let me know how it goes if you try this approach!


(Ryan Schork) #13

Looker is only giving the the first 100 (one hundred - not a typo) distinct options as suggestions. Is there a way to change this limit? I’ve seen 500 in some places and 1000 in others so I am confused as to why it is only 100 for me.


(Izzy) #14

I’m also seeing 100 consistently, @rschork— But, interestingly, the query that’s run on the backend for the suggestion has a limit of 1000, so it looks like the UI is imposing the 100 option limit. Are you looking for more options, or just curious what the limit is for everyone?


(Ryan Schork) #15

Both – although ideally the limit on the front-end would match the backend. I get there needs to be a limit to prevent excessive querying but IMO that should be closer to 1000 than 100. It seems that was the original intent with making the backend limit 1000… is it safe to label this a bug?

Also - I want to point out that this limit is enforced even when specifying a hard-coded list of options using the suggestions: parameter.

It seems like this would be a good case for an option that allows a user to override this limit.


(Izzy) #16

From what I can tell, I don’t believe this is a bug. It was an intentional choice to limit back down to 100 on the frontend to make things snappier.

That’d make this a feature request to bump it back up (or give you control over it). There’s a few posts floating around Discourse on the subject, but none are a plain and simple request in the Feature Requests section— If you make one, I’ll vote for it :smile:


(Ryan Schork) #17

Done!

https://discourse.looker.com/t/allow-control-over-the-number-of-suggestions-that-populate-on-dashboards/11205


(Izzy) #18

I held up my part of the bargain! Don’t forgot to vote on your own request :wink: