Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Performance Considerations with Suggest Filters

  • 8 January 2015
  • 26 replies
  • 2107 views
J
Userlevel 3

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


26 replies

C

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.

lloydtabb
Rank
Userlevel 6
Badge

@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.

C

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!

lloydtabb
Rank
Userlevel 6
Badge

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

L
Rank
Userlevel 5
Badge

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.

zlebowitz
Rank
Userlevel 3

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
nbeyer
Rank
Userlevel 3

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.

M

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
Rank
Userlevel 5
Badge

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.

M

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
Rank
Userlevel 5
Badge

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!

R

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
Userlevel 7
Badge +1

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?

R

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
Userlevel 7
Badge +1

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 😄

R

Done!


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

izzy
Userlevel 7
Badge +1

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

D

@izzy @zlebowitz curious about the ice flavor solution provided above to speed the suggestions field? is that option actually works. I have a filter where i have suggested a dimension which is a unique facility name (company name) and it take 30 sec (sometimes more) to load the suggestions when user starts typing… i was thinking to speed it up but not sure how?? any help will be appreciated

D

@izzy anyone here?

izzy
Userlevel 7
Badge +1

Zev’s approach is to improve performance of the suggestions by creating a persistent derived table that’s very simple and stores only the data necessary for the filters, so that it’s super lightweight to query.


Note that he’s also adding an index, which will speed things up, and the most important part: He’s persisting the derived table by using the sql_trigger_value parameter. If your filter suggestions take that long, then yes, I think creating a small derived table that contains only those suggestion values could be super valuable. The persistence is key, though, or you might actually see increased load times!


Just make sure you persist it on an appropriate timeframe so that your users aren’t confused by missing data. The approach described above is in old lookml gasp so it might look a little funky. I took a stab at converting it below to something that looks more familiar, but definitely check my syntax before copying/pasting.


 dimension: flavor {

    type: string

    sql: ${TABLE}.flavor ;;

    suggest_explore: flavors

    suggest_dimension: flavors.flavor

}



explore: flavors {

  hidden: yes

}



view: flavors {

  derived_table: {



    sql: SELECT distinct flavor

        FROM icecream_orders ;;



      indexes: [flavor]

      sql_trigger_value: SELECT CURRENT_DATE()

}

       dimension: flavor {

       sql: ${TABLE}.flavor ;;

}
D

thanks @izzy this is very helpful!

scarnley

When using this method in the “new” dashboards, filters produce the error attached.  The “Tiles to Update” gets blanked out on every edit.  It seems to work fine in the “old” filter editor.

IanT
Userlevel 7
Badge

Reported this a couple of months ago, it messes up cross filtering as well. No idea if this was going to be resolved.

scarnley

Reported this a couple of months ago, it messes up cross filtering as well. No idea if this was going to be resolved.

According to Looker support, this bug was resolved in version 21.6. 

tuddin

I could not make suggest_explore work. I have created an hidden explore exactly as mentioned above. But when I go to filter, it does nothing.

Reply


Terms of Service