Reducing page load times from 30 seconds to 3 seconds through strategic caching

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

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

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.