We’re evaluating optimization strategies for our sustain engineering queries in Agile 9.3.6, specifically queries that analyze field failure patterns, warranty claims, and quality issues across product lines. These queries are currently taking 30-60 seconds for complex analysis spanning multiple years of data.
We’re debating between two approaches: investing in comprehensive database indexing improvements versus implementing an in-memory caching layer (considering Redis). Both have been suggested by different teams, and I’m looking for real-world experiences with each approach in the context of sustain management analytics.
The queries involve joining multiple large tables (QUALITY_ISSUES, WARRANTY_CLAIMS, FIELD_FAILURES, PARTS) with date range filters and aggregations. Would love to hear pros and cons of each strategy and whether anyone has successfully used a hybrid approach.
You need to consider the query characteristics to make the right choice. Sustain engineering queries typically fall into two categories: standardized reports (same query patterns, different date ranges) and exploratory analysis (ad-hoc queries with varying filters).
For standardized reports, caching is highly effective. For exploratory analysis, database optimization is more important since cache hit rates will be low.
Another factor is data volume and growth rate. If your WARRANTY_CLAIMS and FIELD_FAILURES tables are growing rapidly, index maintenance overhead becomes significant. Indexes need to be rebuilt or reorganized periodically, which can impact performance during maintenance windows.
Database indexing should be your first line of defense. For sustain engineering queries, you’re dealing with analytical workloads that scan large datasets. Proper indexing can reduce query time by 80-90% without adding infrastructure complexity. Focus on composite indexes covering your common query patterns - date ranges, product families, failure types. The key is analyzing your actual query patterns and building indexes that match those access patterns rather than generic single-column indexes.
This is where a hybrid strategy makes sense. Use database indexing for the baseline performance improvement - this benefits all queries regardless of caching. Then layer in selective caching for your most frequent query patterns. We cache pre-aggregated results for standard reporting periods (monthly, quarterly, yearly summaries) and let ad-hoc queries hit the optimized database directly. This gives you the reliability of database optimization with the speed boost of caching for common use cases.