Reducing page load times from 30 seconds to 3 seconds through strategic caching
How targeted performance investigation delivered dramatic improvements while identifying deeper architectural issues
Key results at a glance
The challenge
The Problem
A French company providing inventory management and cost calculation for airline operations had a critical performance problem. Their main interface was taking 12 to 30 seconds to load - clearly unacceptable for operational use.
The Investigation Brief
- Identify hotpaths: Find where time was being spent
- Implement quick wins: Deliver immediate improvements
- Document roadmap: Provide a path for improvements beyond the engagement
What We Found
The root cause was a classic pattern in legacy systems: inefficient database interactions reminiscent of early Entity Framework days. Developers hadn't appreciated that each lazy-loaded property access triggered a database call.
The database issues ran deeper:
- Exponentially growing tables: Tens of millions of rows over just four years
- Custom versioning system: Start and end dates stored for every record change (similar to temporal tables but baked into main tables)
- Complex joins: Versioning data spread across 3-5 primary tables, requiring constant joining
The results
Key results
- Page load reduced from 12-30 seconds to 2-3 seconds (6-15x improvement)
- Caching abstraction layer supporting future Redis/hybrid cache extension
- Root cause identified: lazy loading patterns and custom versioning tables
- SQL partitioning investigated with ripple effect analysis
- Improvement roadmap delivered for team continuation
Outcomes
Performance Improvement
- Load times reduced from 12-30 seconds to 2-3 seconds
- 6-15x improvement making the system usable for operations
- Immediate relief while deeper issues are evaluated
Root Cause Documentation
- Lazy loading anti-patterns identified and explained
- Custom versioning system issues documented
- Partitioning constraints and ripple effects mapped
Long-term Roadmap
- Caching patterns documented for team to extend
- Partitioning approach documented with feasibility notes
- Temporal table migration recommendation for leadership discussion
Honest Assessment
The caching solution was a deliberate "band-aid" - it made the system usable while the team evaluates the larger architectural changes needed for a permanent fix. Sometimes the right answer is quick relief plus a roadmap, not a complete rebuild.
The solution
Our Approach
We focused on deliverable improvements within a one-month engagement while documenting longer-term recommendations.
Caching Abstraction Layer
Implemented a memory cache with an interface designed for future extension:
- Immediate performance improvement through in-memory caching
- Architecture supporting future Redis integration
- Compatible with .NET's hybrid cache when they upgrade from .NET 6
Database Investigation
Investigated SQL partitioning on the problematic tables:
- Dramatic improvements in isolation: Partitioning worked when tested independently
- Ripple effects prevented adoption: Partitioning would need to cascade across related tables that didn't support the same date-range patterns
Also recommended migrating to proper SQL temporal tables, though this too would require significant restructuring.
Roadmap Delivery
Left the team with documented next steps:
- Rolling out caching pattern to other areas
- Evaluating database partitioning feasibility
- Considering temporal table migration
Ready to achieve similar results?
Let's discuss how we can help your organisation achieve these results.
Book a strategy call