Hello, I’m looking for some help on speeding up a dashboard load time. I’ve reviewed the help article and don’t see how the listed steps help too much (already working with transformed data, can’t cache this data as frequently applying new filters)
These queries take about 30s to run when run individually:
- Provider Bookings Change YOY
- Edit: Cannot link the other query, as I’m restricted to only two links, but it’s in the dashboard below and the same exact SQL as the above query, just graph vs table view. Code block below…
-- Provider Bookings Change YOY
with date_spine as (
select
month_of_year_int as month,
year_int as year
from utils.dates where year_int in (2020, 2019)
)
select
date_spine.month,
coalesce(count (distinct case when
extract(year from bookings.created_at_et) = '2019' and is_online is true
then bookings.booking_id else null end), 0) as total_online_bookings_2019,
coalesce(count (distinct case when
extract(year from bookings.created_at_et) = '2020' and is_online is true
then bookings.booking_id else null end), 0) as total_online_bookings_2020,
coalesce(count (distinct case when
extract(year from bookings.created_at_et) = '2019' and is_online is false
then bookings.booking_id else null end), 0) as total_in_person_bookings_2019,
coalesce(count (distinct case when
extract(year from bookings.created_at_et) = '2020' and is_online is false
then bookings.booking_id else null end), 0) as total_in_person_bookings_2020
from date_spine
left join analytics.bookings on date_spine.year = extract(year from bookings.created_at_et)
and date_spine.month = extract(month from bookings.created_at_et)
and bookings.created_at_et > '2019-01-01'
left join analytics.order_items on bookings.booking_id = order_items.booking_id
left join analytics.providers on bookings.provider_id = providers.provider_id
and providers.tools_live_date < (current_timestamp - interval '1 year')
and providers.provider_status = 'live'
and providers.provider_id = {{provider_id}}
group by 1
order by 1 asc
;
When I add these queries to a dashboard, they take 5-6 minutes to run. See the dashboard here.
- Looking at the (/admin/troubleshooting/logs) (cmd+f “dashboard/185”) after running, Metabase consistently fails to load the two queries on the righthand side of the dashboard. Redshift seems to be handling the queries normally, though. As noted, the data loads eventually on Metabase after about 5-6 mins of waiting.
Is there something I’m missing here on how to optimize this query?
**Using Chrome, and Metabase v0.37.4.
Please let me know if I can provide any additional information, this is my first time posting here