Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Reusing SQL results

I

I started using looker recently. I have a dashboard which has several looks. The data for the looks come from a single view that I created in looker which is many columns. Each look has related data or data that should be represented together. Everything works good but the issue that I have is that for every look on the dashboard same query gets executed even though the query is almost identical. The reason I am saying almost identical is that for each look, looker executes the query and simply selects only the columns that are there in that look. What I am looking at is a way so that my view/query gets executed only once and gets all columns and then I use needed columns in different looks.


Thanks

33 replies

jesse.carah
Rank
Userlevel 4

Hey Ima, check out this post on how to generate a single query which powers multiple tiles.

I

Thanks Jesse, I had seen that post earlier but thought that it may not be applicable in my case but after reading your post I did try it but I find that looker sends two identical queries to database and they both get executed. So it does not appear to be helping with what I am trying to do, which is executing query once and using that data into different tiles/looks on a dashboard.

Alex_Hancock
Userlevel 3

We’ve highlighted this before - re-utilising queries doesn’t really work because until the first response is cached, ALL tiles will initiate their own identical SQL queries (the same query running multiple times up to the limits of your DBs threads). Once the first query is returned, all the threads that have not initiated instantly receive the results, but those that were initially started churn away until they return.

I

Thanks, this confirms what I experienced. I have sent details to support after being asked by support to do so. May be the fix could be that looker should generate all sqls first and then send the unique ones to database.

menashe
Userlevel 3

This is very unfortunate. Looker folks (cc @bernard.kavanagh) please consider this a feature request for a change in implementation so that Looker sends a query once instead of multiple times in a situation like this.

izzy
Userlevel 7
Badge +1

Hey all,


I just gave this a shot, cause @bliu raised it again and an engineer said he agreed it should work as advertised in this topic (https://discourse.looker.com/t/shared-filters-on-single-value-charts-performance-technique/3074/11). I ran some tests, and it seems to be doing what it ought to!


dups


What it looks like @ima.adesi was running into is expected, since the queries were actually different (different columns selected)-- In order for this optimization to take place, the queries must be 100% identical, with only visualization/frontend options changed. Ima, you could try using “hide from visualization” to select the columns you want to show in each tile.


Lemme know if you’re all still seeing this not working!

I

I am not sure how you inferred that “What it looks like @ima.adesi was running into is expected, since the queries were actually different (different columns selected)” . If you read my post which says " I find that looker sends two identical queries to database and they both get executed. So it does not appear to be helping with what I am trying to do, which is executing query once and using that data into different tiles/looks on a dashboard."

Also I sent my case to support and they confirmed that It was not working as expected. But I have not followed on this to know what the current status is.

izzy
Userlevel 7
Badge +1

Gotcha, I jumped to conclusions from



thinking you might have been actually selecting different columns, not selecting everything and then hiding some from the visualization— My bad, thanks for clarifying.


It looks like we agree internally that this should work, so I’m doing some sleuthing to see what’s going on here. I’ll keep you posted!

B

Thank you for testing this. I’m able to produce the same outcome

izzy
Userlevel 7
Badge +1

If you’re seeing Looker send two identical SQL queries to the database and they’re both executed, I’m super curious what Looker thinks those queries look like.


If you can run that dashboard and get the history id’s for those 2 queries from the admin/queries page, then go to i__looker and add both ID’s to this explore’s filter (you’ll need to replace your.looker.com with your looker address):


https://your.looker.com/explore/i__looker/history?fields=history.created_time,query.id,history.render_key&f[history.id]=&sorts=history.created_time+desc&limit=500&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22history.id%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22history_id_1%2Chistory_id_2%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%2C%22error%22%3Atrue%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Do you get multiple rows + query_ids returned?

I

I had emailed romain.ducarrouge (looker support) last year and he was able to check it and said that it was bug.

fabio
Rank
Userlevel 7
Badge

Are you on a very old version of Looker? Or perhaps does your explore or model specify persists_for: 0 seconds (or 0 minutes, 0 hours, etc)?

Jonathan_Palmer
Userlevel 3

Hi @izzy, is there any news on this bug? I’ve been advocating the ‘one query, many tiles’ approach for years but having just tested it I see that it’s not actually working. Using i__looker I’ve validated that the SQL for the multiple tiles is identical in all cases but Looker doesn’t appear to return any of the results from cache in practice.

izzy
Userlevel 7
Badge +1

@Jonathan_Palmer my interest from the earlier post still stands— It’s not just SQL that make up Looker queries and there must be something that’s invalidating the render key.



Most of the time, there’s a difference in sort or column order or something that’s causing this to happen. If you can show that the queries are actually identical, or should be but the render key is changing, that would be super useful for us since this keeps coming up as something we’re never 100% sure is a bug or not.

Jonathan_Palmer
Userlevel 3

Thanks for the follow up @izzy. Happy to provide some more info about what I’m seeing on our side. What’s the best way to share this with someone on the Looker side?

IanT
Userlevel 6
Badge

What goes into making the render key? The usual way we have used this is by sticking let’s say 5 measures into an analysis and hiding from viz 4 measures to create a viz with just 1 measure showing, do this another 4 times rotating the measure to show. If this hide from viz option goes into a changing render key then I can’t see what the point in this functionality would be or is it more of a lucky feature if you happen to exploit it?

izzy
Userlevel 7
Badge +1

Best way is definitely to reach out to support— They know about this issue and will happily parse thru your info to figure it out.



That’s a really great question that I truly don’t know the answer to and I think is buried deeply in our code. Support will be able to route the info to the right engineer to verify.


Your described situation is the standard case for this so should work. where I see things failing is when people actually change the order of the fields when doing that. Regardless, getting the full query information i described above should let us pinpoint what’s causing it to change and then make a decision on whether it’s intended or not.

Jonathan_Palmer
Userlevel 3

Thanks @izzy. I’ll get in touch with support now.

Jonathan_Palmer
Userlevel 3

@izzy So from the conversation I’ve just had with Looker support, the ‘one query, many tiles’ option is null and void. Looker engineering seemed to confirm that if you have a dashboard with 3 tiles all driven by 1 identical piece of SQL, Looker will send 3 identical queries to your database. This is the behaviour I’m seeing Looker side and database side in our case. That really sucks, both from the inefficiency and cost perspective but also from the perspective that Looker advocates an approach that doesn’t work. 😞

moebe
Userlevel 4

This is really not good.

However, I hope that databases (like Snowflake) recognize that it is the identical query and get the results out of the cache quickly

izzy
Userlevel 7
Badge +1

Well shoot. I take responsibility for that one, as in this case “Looker” is mostly me, at least for the last ~year on this post. Sorry about that. I’ll add a note on the top of this post so others don’t go down the wrong path.

izzy
Userlevel 7
Badge +1

Actually, I take that back! Something prickled my spidey sense so I gave this a test and I do see this functionality working. See video:



That’s what we’re expecting to see, right?


Perhaps we still shouldn’t recommend this as it obviously doesn’t seem officially supported, and seems to break on a whim given some config change that I can’t quite pin down. But for simple cases at least, it seems to be working quite well for me. I’d be interested in what your tiles were that weren’t working.

izzy
Userlevel 7
Badge +1

Ah and I suppose I should have checked system__activity:


image


image


I’m clocking 1 query and all the rest from cache, with identical render keys despite different query slugs

Jonathan_Palmer
Userlevel 3

Thanks for the update @izzy. That’s really curious that it appears to work in certain circumstances and not others. Also curious that Looker support and engineering didn’t expect this to work of course.


In terms of what might be determining whether it works or not, in my use case I have a day-over-day delta in the single value viz. So my query is returning two rows (yesterday and yesterday -1), with the date dimension and three metrics . I then use a table calc to calculate the delta and then I use that for the comparison value.


I haven’t had a chance to check this myself but I wonder if even though the order by is the same, the fact it is multi row may have something to do with this?

izzy
Userlevel 7
Badge +1

I’ll take a look tomorrow and see if I can root out where this breaks down. For a super simple example like mine, did it work for you as expected?

Reply


Terms of Service