Back to all case studies

When 4-Hour Reports Block Your Entire HQ: Rebuilding Trust Through Database Discipline

Snapshot

Delivery model: Principal-led engagement (Stefan, Founder & Principal Consultant) Client: Nordlicht Retail (German fashion retailer)

A Major German fashion retailer with 80+ outlets was paralysed by a legacy POS reporting system. Daily distribution planning required reports that took 4 hours to run, locked the entire headquarters IT infrastructure, and crashed regularly. The engagement restored operational stability in 3 weeks and rebuilt the relationship between IT and Sales leadership.

The Challenge

Every morning, the Head of Sales needed distribution reports to allocate stock across 80 retail outlets. The process was a daily crisis:

• 4-hour runtime for key reports, with frequent crashes before completion
• Exclusive database locks froze all HQ systems during execution
• Dual-database chaos: MySQL and MSSQL with no coherent schema design
• Legacy codebase: Hand-written reporting software in CA-Visual Objects, a niche language with limited maintainability
• Constant complaints: IT had lost credibility with the business. Sales viewed the technical team as incompetent

The system had become a bottleneck for the entire retail operation, with no one in IT able to communicate effectively with Sales about what was technically feasible.

Constraints

• No budget for replacement: The POS system itself could not be rewritten
• Dual-database architecture: Had to work within the existing MySQL/MSSQL split
• Operational pressure: Reports were mission-critical. Downtime during fixes was unacceptable
• Trust deficit: Any solution had to deliver immediate, visible improvement to regain business confidence

Approach

1. Query Plan Forensics Analysed execution plans for every statement in the reporting software. Identified missing indexes, Cartesian joins, and full table scans on transactional tables with millions of rows.

2. Strategic Indexing Added indexes on frequently joined columns and filter predicates. Prioritised the queries causing the longest locks.

3. View-Based Abstraction Encapsulated common join patterns, for example sales transactions plus stock movements plus outlet metadata, into database views. This eliminated redundant query logic and ensured consistency.

4. Stored Procedures for Calculated Fields Moved complex calculations (inventory deltas, regional aggregations) into stored procedures feeding the views, reducing the computational burden on the reporting layer.

5. Replaced Lock-Heavy Tooling Retired the CA-Visual Objects hand-written software. Implemented List & Label, a commercial reporting tool, to query the new views without exclusive locks.

6. Transactional Calculation Architecture Stopped relying on snapshot data, which required locks to ensure consistency. Instead, summed all sales, returns, and stock movements transactionally, then correlated with annual physical inventory counts. This approach:

• Eliminated the need for long-running locks
• Made manual corrections, for example inventory adjustments, immediately visible across the system
• Drastically reduced faulty records from stale snapshots

7. User Education on "Data Correctness" Worked with Sales to reframe expectations: A report showing 5 items in stock vs. 4 or 6 is operationally equivalent if a sale could occur moments after generation. The goal is decision-grade accuracy, not real-time perfection. This shift defused complaints about minor discrepancies and focused attention on actionable insights.

What Was Delivered

• Indexed database schema with views consolidating 80% of reporting queries
• List & Label-based reporting suite replacing legacy CA-Visual Objects code
• Transactional calculation engine (stored procedures) for inventory reconciliation
• Documentation on the new data model and query patterns for future maintainers
• Training session for Sales on interpreting report data and understanding timing constraints

Results

• Runtime reduction: 4 hours -> 30 minutes for critical distribution reports
• Zero system locks: HQ IT infrastructure no longer frozen during report generation
• Eliminated daily crashes: Reports ran reliably every morning
• Immediate data propagation: Manual corrections, for example inventory adjustments, reflected instantly, not after overnight batch jobs
• Restored credibility: Became the only IT staff member trusted by the Head of Sales, ending the perception of technical incompetence

Why It Worked

Technical pragmatism over perfectionism Rather than demanding a full system rewrite, the solution worked within existing constraints in a dual-database legacy POS environment. Strategic indexing and view consolidation delivered 87.5% improvement without touching the core application.

Reframing "correctness" as a business concept The educational component was as critical as the technical fix. By aligning Sales expectations with database realities (eventual consistency, timing windows), complaints shifted from "the data is wrong" to "this helps me make decisions."

Transaction-based truth Moving from snapshot locking to transactional summation solved both performance and accuracy problems simultaneously. This architectural choice eliminated the root cause (exclusive locks) rather than optimising around it.

Human-centric delivery Success was measured not just by query speed, but by whether IT could have a productive conversation with Sales. The trust rebuild was the ultimate outcome.

How Vionix Worked

The engagement was structured as a 3-week intensive intervention:

• Week 1: Query plan analysis, index implementation, and view design
• Week 2: Stored procedure development, List & Label integration, and testing
• Week 3: User training, monitoring, and handover to internal IT

Daily stand-ups with the Head of Sales ensured alignment on priorities and immediate feedback on improvements. The work was performed alongside the existing system (no downtime), with the new reporting suite running in parallel until validation was complete.

Post-delivery, internal IT retained ownership of the database schema and reporting tool, with documentation enabling future modifications without external dependency.