Database Optimisation: The Art of Peeling the Onion
- Lisa Roberts
- May 26
- 3 min read
A consultant's guide to untangling inherited database systems without tears. (Okay, maybe just a few tears.)
The Inherited Onion
As consultants, we've all been there. You arrive at a new client, bright-eyed and bushy-tailed, ready to make an impact. Then someone hands you access to their database system – an intricate, multi-layered creation that's been touched by dozens of developers over the years. Each with their own coding style, business understanding and deadline pressures.
Welcome to your onion.
From the outside, it looks manageable enough. A few tables here, some views there, a handful of stored procedures with quirky names. But start peeling back those layers, and suddenly your eyes begin to water.
The Tearful Layers You'll Encounter
Layer 1: The Performance Problems
"Why does this report take an hour to run?"
The outermost layer often reveals itself through slow performance. At the Dutch bank, our monthly mortgage pricing and valuation process for various EU banks was crawling. Data retrieval was taking ages because of how views and functions were querying massive datasets from multiple mortgage portfolios across different countries.
Quick Win: Review joins and create clustered indexes on join fields. This simple step can dramatically improve query performance without requiring major structural changes.
Layer 2: The Redundancy Trap
"Why is the same customer information stored in five different tables?"
As you dig deeper, you'll spot data duplication everywhere. The entity names appear in the Entity table (naturally), but also in the Loans table, the Property table, and scattered across various other tables where someone thought it would be "more efficient" to have it directly available.
Quick Win: Identify redundant data storage and begin planning how to normalize the database structure. This reduces storage costs and eliminates update anomalies.
Layer 3: The Missing Relationships
"How does this table connect to anything else?"
In our bank project, we discovered many tables lacked primary keys and foreign key constraints. Without these fundamental elements, the database diagram looked like a collection of isolated islands rather than an interconnected system.
Quick Win: Start documenting relationships and gradually implement proper constraints. This makes future development easier and prevents data integrity issues.
Layer 4: The Business Logic Labyrinth
"Why does this calculation happen in three different places with slightly different formulas?"
The deepest, most tear-inducing layer is often the scattered business logic. Critical calculations might exist in views, stored procedures, application code, and even Excel formulas used by the business team.
Quick Win: Document business rules as you discover them and look for inconsistencies. Create a centralized repository of business logic that can become the single source of truth.
Embracing the Onion Without Crying
The beauty of the onion approach is that each layer teaches you something valuable. Yes, there will be moments when you want to throw in the towel (or throw out the database and start from scratch). But resist that urge!
Instead:
Be curious, not judgmental: The previous developers weren't trying to create a mess. They were solving problems with the constraints they had at the time.
Document as you explore: Create your own map of the onion. Future you (and future developers) will thank you.
Look for patterns: Even in the most chaotic systems, patterns emerge that reveal the underlying business processes.
Make incremental improvements: Don't try to fix everything at once. Peel the onion layer by layer, improving as you go.
The Core Revelation
Eventually, after enough careful peeling, you'll reach the core – the essential business objectives that the database was built to serve. This is your eureka moment! With this understanding, you can begin designing a solution that meets those objectives more efficiently.
At our banking client, we eventually redesigned the database structure to support the core mortgage valuation process with proper relationships, optimized queries, and centralized business logic. Monthly processing time dropped from hours to minutes, and the maintenance burden decreased significantly.
The Cycle Continues
The funny thing about database optimization is that it's never truly finished. As business needs evolve, your beautifully streamlined database will inevitably grow new layers. That's okay! By understanding the onion peeling process, you've equippe
d yourself (and hopefully your client) with the knowledge to manage those layers more effectively.
And when the next consultant comes along years later, they'll at least find an onion with well-documented layers and clear relationships. They might still shed a tear, but it will be one of appreciation rather than frustration.
Remember: In database optimisation, as in life, understanding the layers of history helps build a better future – even if it makes you cry a little along the way.
Download the PDF | Contact us via email
Kommentare