Custom reports in our automation-mgmt module consistently timeout when querying datasets over 50K test execution records. The SQL timeout occurs after exactly 60 seconds, even though we’ve increased the database connection timeout to 300 seconds.
SELECT te.id, te.status, te.duration
FROM test_executions te
WHERE te.project_id = 'AUTO_PROJECT'
AND te.created > '2024-01-01'
Query optimization attempts haven’t helped - we’ve tried adding WHERE filters, but the automation-mgmt tables lack proper composite indexes. Async reports might be an option, but we need real-time data for our performance dashboards.
Dataset limits in pol-2304 documentation mention 100K record thresholds, but we’re hitting timeouts at half that. Has anyone successfully optimized large automation reporting queries?
For real-time dashboards, you might need to rethink the approach. Instead of querying raw test_executions on every dashboard refresh, set up a materialized view or summary table that updates incrementally. Query the summary for dashboards and only hit the main table for detailed drill-downs. This is how we handle dataset limits in our automation reporting.
Async reports are actually the recommended approach for datasets over 25K records in pol-2304. Configure your report to run asynchronously and cache results. Users get near-real-time data with a 5-minute refresh cycle instead of hitting SQL timeouts. Check the automation-mgmt module documentation on async report configuration.
Composite indexes will add minimal write overhead compared to the read performance gain. But consider partitioning your test_executions table by month or quarter. That way, your queries only scan relevant partitions. We did this and reduced query time from 55 seconds to under 10 seconds on 200K records.