We have a critical Prism Analytics report combining employee data with external compensation benchmarks that’s taking 45+ minutes to refresh during business hours. The report joins employee demographics from Core HR with external market data across 12,000+ employees in 47 countries.
The query appears to be doing full table scans and the materialized views we created aren’t being utilized efficiently. We’ve noticed the external data source batch loads are happening during peak usage times which compounds the problem.
SELECT e.worker_id, e.location, ext.market_rate
FROM employee_data e
JOIN external_compensation ext ON e.job_code = ext.job_code
WHERE e.active = 1 AND ext.effective_date = CURRENT_DATE
Executive leadership needs this data for quarterly planning meetings and the current refresh time is unacceptable. Has anyone optimized Prism Analytics queries with similar complexity and external data dependencies?
For materialized view design with external sources, create separate views for each data source with appropriate filters already applied. Your view should include the WHERE clause conditions as part of the view definition so the optimizer recognizes it can satisfy filtered queries. Also verify your view includes job_code with proper indexing since that’s your join key.
The external data source batch timing is critical here. You mentioned loads happening during peak usage which is definitely contributing to contention. We moved our external compensation data loads to off-peak hours (2-4 AM) and implemented incremental loading rather than full refreshes. This alone cut our report times by 60%. Also consider partitioning your external data by effective date to avoid scanning historical records unnecessarily.
Thanks for the insights. We checked and our materialized views are refreshing every 6 hours but the query optimizer isn’t using them. The execution plan shows full table scans on both employee_data and external_compensation. We’re going to try the CTE approach and look into the caching settings. Any specific recommendations on materialized view design for multi-source reports?