Reusing SQL results

done
normal_priority
reply

(ima) #1

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


Shared Filters on Single Value Charts Performance Technique
(jesse.carah) #2

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


(ima) #3

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) #4

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.


Can we have multiple viz running the same query for a single time in the dashboard?
(ima) #5

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 Hamm) #6

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) #7

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 (Shared Filters on Single Value Charts Performance Technique). I ran some tests, and it seems to be doing what it ought to!

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!


Can we have multiple viz running the same query for a single time in the dashboard?
(ima) #8

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) #9

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!


#10

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


(Izzy) #11

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?


(ima) #12

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


(fabio) #13

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)?