Case Study
A UK financial services firm's internal portal had a critical client-position query that took 4.2 seconds against a 50M-row transaction database, blocking adviser workflow during client meetings. Softomate's targeted database performance work cut the query to 80ms (98% faster) through schema redesign, targeted indexing, and a partitioned aggregate table strategy.
14 min read By Deen Dayal Yadav, Founder & AI Automation Director
UK financial services firm, 50-million-row transaction database, 6 advisers, internal portal
A UK financial services firm's internal portal had a critical client-position query that took 4.2 seconds against a 50M-row transaction database, blocking adviser workflow during client meetings. Softomate's targeted database performance work cut the query to 80ms (98% faster) through schema redesign, targeted indexing, and a partitioned aggregate table strategy.
A UK-based financial services firm providing investment management to private clients operated an internal portal used by its 6 advisers during client meetings to display real-time portfolio positions, transaction history, and performance attribution. The portal was built on a Laravel back end with a MySQL database that, over twelve years of operation, had accumulated approximately 50 million transaction records across roughly 1,800 active client accounts and another 4,200 historical accounts retained for regulatory record-keeping.
The portal worked well for most workflows. The single critical bottleneck was the client-positions query: the screen that loaded when an adviser opened a client record, showing the client's current portfolio composition, year-to-date performance, and the last 12 months of transaction history. This screen was loaded multiple times during every client meeting, and average page-load time was 4.2 seconds. In a client meeting context, 4.2 seconds of dead air while the portal loaded was both professionally awkward and operationally damaging. Advisers had begun pre-loading client records before meetings, which kicked the problem down the road but did not solve it.
The query itself was relatively conventional. It joined the transactions table (50M rows) with the holdings table (8.4M rows), the accounts table (6,000 rows), the securities reference table (12,000 rows), and the pricing history table (24M rows), with WHERE clauses on client ID and date ranges. The query plan involved multiple full-table scans on the transactions table because the available indexes did not match the actual query patterns the application was using.
The IT director had attempted a fix the previous year by adding three additional indexes to the transactions table. The result was marginal improvement on the target query (from 4.8 to 4.2 seconds) and significant degradation on the firm's daily settlement batch process, which had relied on a particular index ordering that the new indexes had inadvertently disrupted. The change had been rolled back partially after the settlement issue surfaced, leaving the firm in a worse position than before: the slow query had not been fixed and the settlement batch was now using a less-than-optimal plan. The IT director's external consultant had recommended sharding the transactions table, which would have been a 6-figure project with high risk; the firm had declined to proceed.
The business impact of the slow query was visible in client perception. The firm's annual client satisfaction survey, which used a structured Net Promoter Score format, had shown a steady decline in “efficiency of meetings” scores over the previous three years. In post-meeting feedback collected by the firm's adviser support team, several clients had specifically commented on the perception that meetings ran slowly, with one high-value client describing the portal performance as “noticeably worse than my bank.” The chief executive had personally flagged the client experience issue as a strategic priority but the IT director had been unable to find a fix that did not introduce equivalent or larger problems elsewhere.
The regulatory dimension added pressure. As an FCA-regulated investment manager, the firm carried specific record-keeping obligations under the FCA's COBS rules, which required the firm to retain detailed transaction-level records for at least five years after the relevant transaction. The 50M row table was a direct artefact of those obligations, and shrinking the table through archival was not commercially straightforward; the regulatory records had to remain queryable for FCA review and for client subject access requests under GDPR. Any performance solution needed to preserve full query access to historical records, not just recent transactions.
The operational dimension added a fourth concern. The transactions table was the foundation of the firm's daily settlement reconciliation, the monthly client statement production, the quarterly performance attribution calculation, and the annual regulatory reporting cycle. Any structural change to the table risked downstream effects on each of these workloads, and the IT director's previous attempted fix had demonstrated exactly that risk in practice. Any new performance work needed to be designed and tested with full visibility on the complete set of workloads using the table.
The brief to Softomate was tightly scoped. Cut the client-positions query response time to under 200ms (a target derived from the firm's analysis of perception threshold for “instant” UI response). Do not degrade any of the existing dependent workloads (settlement, statement production, performance attribution, regulatory reporting). Do not require any change to the application's existing query code, because rewriting application queries would extend the scope into a separate engagement. Complete the work within an eight-week engagement window.
Softomate's database engineer started with a structured analysis of the actual query workload against the database. The first week was production-trace gathering, capturing every query hitting the database over a representative 5-day window, with timing, query plan, and result-set size for each. The trace revealed that the client-positions query was one of seven hot queries that, between them, accounted for roughly 84% of total database CPU time. Optimising all seven was the right scope, not just the visible one.
The schema analysis identified three structural issues. First, the transactions table had no partitioning despite being the largest table by an order of magnitude; the entire table was being scanned for every position query. Second, the available indexes had been added incrementally over years and did not reflect the actual query patterns the application had evolved to use; several indexes were unused and a few highly-useful indexes were missing. Third, the holdings table was being recomputed from transactions on every query rather than maintained as a materialised view, which forced the expensive joins on every read.
The remediation was delivered in three sequential phases over five weeks, each with explicit rollback plans. Phase one introduced range partitioning on the transactions table by transaction_date, with partitions of one calendar quarter each. The partitioning was added as a non-blocking online operation using MySQL's native partitioning support, with the application's existing queries unchanged. The result was that position queries scoped to recent date ranges (which was the case for the vast majority of adviser use) now scanned a small fraction of the table.
Phase two rebuilt the index strategy from scratch based on the actual query patterns observed in the production trace. Six unused indexes were dropped, four new composite indexes were added with column ordering optimised for the specific WHERE-and-JOIN patterns the hot queries used, and the settlement batch's required ordering was explicitly preserved through a dedicated index that the settlement query was hinted to use. The settlement batch process, which had been the casualty of the previous attempted fix, ran faster after this phase than it had before any of the changes started.
Phase three introduced a materialised holdings_current table maintained by triggers on the transactions table. The materialised table held the current position state per account per security, updated incrementally on every transaction insert rather than computed on every position query. The application's client-positions query was rewritten to read from the materialised table instead of computing from transactions on the fly, eliminating the most expensive part of the original query plan. The materialised table was reconciled against the transactions table daily by a scheduled job to catch any drift, with alerting if drift was detected.
The testing approach reflected the production-critical nature of the work. Each phase was first deployed to a staging environment that held a full copy of production data, with the application's complete test suite run against it. Phase changes were then deployed to production during a planned maintenance window with full backup and tested rollback procedures ready. Each phase was deployed sequentially with at least a week of production observation between phases to confirm no unexpected side effects had surfaced before the next phase began.
The observability layer was a parallel workstream that proved disproportionately valuable. Softomate installed enhanced query-level monitoring (using MySQL's performance_schema with a custom collector exporting to the firm's existing Grafana setup) that gave the IT director real-time visibility on query timings, lock contention, and index usage. The observability was the mechanism by which the firm would detect any future performance regression at the moment it surfaced rather than after-the-fact through user complaints. The IT director described this capability as the longest-lasting value of the engagement, separate from the headline query speedup.
The dependent-workload preservation was explicitly engineered rather than assumed. Before any production change, Softomate ran each phase's proposed changes against the staging environment with the complete set of dependent workloads (settlement reconciliation, statement production, performance attribution, regulatory reporting) executed, with timings compared against pre-change baselines. Any workload that showed degradation was flagged for investigation before the phase proceeded to production. This approach added approximately two weeks to the project timeline but eliminated the risk profile of the previous attempted fix.
The target client-positions query fell from 4.2 seconds to 80 milliseconds, a 98.1% reduction. The portal page load time on the equivalent screen fell from 4.6 seconds (which included some application-level overhead beyond the database query) to 240 milliseconds. The advisers' practice of pre-loading client records before meetings was abandoned within the first week of go-live; client records loaded fast enough that pre-loading offered no benefit.
The six other hot queries identified in the production trace all improved measurably, with reductions ranging from 64% to 91% depending on the specific query pattern. The firm's overall database CPU utilisation fell by approximately 42% in the first month post-deployment, freeing capacity for the analytics workload the IT director had previously been planning to move to a separate read replica.
The settlement batch process, which had been the casualty of the previous attempted fix, ran 34% faster after the new index strategy than it had before any changes started. The IT director described this as the outcome he had been most concerned about going in, and the most operationally important one after the visible query speedup.
The infrastructure cost saving was a secondary but meaningful benefit. The firm had been on the cusp of upgrading its database server tier to handle continuing growth in transaction volume; the freed CPU capacity meant that upgrade was deferred by an estimated 18 months, saving approximately £14,400 in deferred infrastructure cost. The firm has since committed to a structural review of its analytics workload now that headroom is available, with Softomate retained to advise on the read-replica strategy.
The materialised holdings table proved valuable beyond its immediate purpose. The firm's compliance reporting workflow had previously required nightly batch computation of client positions for regulatory reporting; the materialised table made that batch unnecessary and the relevant reports now ran on-demand. The compliance officer reported recovering approximately 4 hours per week from this change alone.
The client perception change was the outcome the chief executive valued most. The next annual client satisfaction survey, conducted approximately six months after deployment, showed the “efficiency of meetings” scores recovering to their highest recorded level. The high-value client who had specifically commented on portal performance the previous year mentioned the improvement positively in an unprompted comment to her adviser during her annual review meeting. The chief executive identified this single client-feedback data point as the clearest evidence that the performance work had translated into the client-perception outcome the firm had been targeting.
The observability layer Softomate installed proved its value within the first three months post-deployment. A specific query pattern began showing increasing lock contention as the firm onboarded a new institutional client whose query workload differed from the established pattern; the IT director identified the issue within hours of it emerging through the Grafana dashboard, rather than the days or weeks it would have taken to surface through user complaints under the previous setup. A targeted index addition resolved the contention before any user impact materialised.
Total Softomate engagement cost was recovered within 4 months of go-live, calculated against the avoided infrastructure upgrade plus the operational time recovered across the adviser and compliance functions. The IT director has subsequently retained Softomate on a quarterly review basis to monitor the production query workload and proactively flag emerging hot queries before they become visible bottlenecks.
Three things, with the benefit of hindsight, would have made the work smoother. First, the production-trace gathering in week one captured only the application-driven query workload; ad-hoc queries run by the firm's analytics team for management reporting were missed initially, and required a second trace pass in week two to capture properly. Future engagements should explicitly include analytics workloads in the initial trace scope. Second, the materialised table reconciliation job's alerting thresholds were initially set too tight, producing several false-positive alerts in the first weeks of production; refinement of the thresholds based on observed reconciliation patterns resolved the noise. Third, the documentation handover at engagement end was scoped narrowly to the technical configuration; the firm's IT team requested expanded documentation covering the rationale and trade-offs of each decision so that future similar work could draw on the reasoning, which Softomate provided as a follow-up deliverable.
The strategic outcome that surprised the IT director most was the change in his own posture toward database performance. Before the engagement, he had treated database performance as a periodic firefighting activity that he addressed when complaints reached him. After the engagement, with the observability layer in place and a structured understanding of the workload profile, he treated database performance as a continuous monitoring activity with proactive intervention. The two patterns produced very different outcomes; the proactive pattern surfaced and addressed issues before users experienced them, while the firefighting pattern had been consistently behind the curve.
The next phase of work under discussion focuses on the firm's analytics workload, which is increasingly being run by the management team rather than just the operations team and which is putting pressure on the production database that the read-replica strategy will need to address. Softomate is currently scoping a read-replica architecture that will isolate analytical workloads from the operational portal queries while preserving real-time data freshness for adviser-facing reporting.
The twelve-month retrospective produced several additional findings worth recording. The materialised holdings table's reconciliation drift, which the team had been monitoring as a potential data-integrity risk, had occurred exactly twice across the year, both times caught by the daily reconciliation job within hours and resolved through targeted re-computation. The reconciliation reliability gave the firm sufficient confidence in the architecture to extend the materialised-table approach to two further derived datasets (performance attribution and cost-basis tracking), both of which had previously been computed on-demand from transactions and both of which produced equivalent query-time improvements when materialised. The cumulative effect was that the firm's portal performance compressed further over the year as additional derived datasets moved from on-demand computation to maintained materialisation.
The advisers' working day had changed substantially. The pre-meeting record loading time that had previously been a known annoyance had become essentially imperceptible, and the advisers reported in a structured interview at the nine-month mark that they noticed the change most acutely when working with clients who had multiple portfolios; switching between portfolio views had previously been a moment of dead time during meetings, and was now seamless enough that the conversation flow no longer broke at those transitions. Three advisers specifically commented that the experience of using the portal had changed from one of tolerance to one of active enjoyment, a shift that was difficult to quantify but that translated into the advisers spending more time in the portal exploring client positions and producing better-informed conversation outcomes.
The strategic outcome that mattered most to the chief executive was the alignment with the firm's broader client experience strategy. The firm had positioned itself in the private-client investment management market as delivering a personal, attentive service that justified its fee structure relative to lower-cost alternatives. The portal performance had been a quiet contradiction to that positioning; clients who experienced sluggish loading had a small but real reason to question whether the firm's technology investment matched its fee positioning. With the performance issue resolved, the contradiction was eliminated, and the firm's positioning became consistent across the relationship dimensions clients experienced. The chief executive described this in the annual strategy review as having resolved an asymmetry the firm had been quietly carrying for years.
The firm's compliance officer also reported a quietly important outcome from the materialised holdings table. The firm's regulatory reporting cycle, which had previously required overnight batch computation followed by a manual review the next morning, became substantially smoother because the source data was already in the form the reports needed. The compliance officer's working day during the firm's monthly regulatory submission window changed materially: from approximately twelve hours of reactive work managing batch outputs and resolving discrepancies, to approximately three hours of structured review of a system that was already consistent. The change recovered roughly nine hours of compliance officer time per month, which was redeployed into proactive regulatory horizon-scanning work that the firm had wanted to do for years but had never had the capacity for.
The IT director's posture change deserves further elaboration because it represented one of the most strategically durable outcomes. Before the engagement, database performance had been a reactive concern: the IT director addressed it when complaints reached him, and complaints reached him when users were already affected. After the engagement, performance became a proactive concern: the observability layer surfaced emerging issues days or weeks before they became user-visible, and the IT director's working day included a daily review of the performance dashboard that took perhaps ten minutes but provided early warning of any pattern shift. The IT director described this in a one-year retrospective as the most consequential change in his own working day across the entire engagement, separate from any specific technical outcome. The shift was, in the IT director's own framing, the difference between operating a database that the firm hoped would behave correctly and operating a database that the firm understood thoroughly enough to be confident in. The confidence dividend manifested in faster decision-making on architecture changes, less defensive sizing of infrastructure capacity, and a willingness to take on workload types the firm had previously avoided because of performance uncertainty. The compounding effect across the year following deployment was substantial in ways that did not show up in any single metric.
Related service: Software Development Service London and Performance Test Engineering London. Further reading: Database Performance Tuning Guide.
Anonymised client engagement. Identifying details modified for confidentiality. Outcome ranges reflect typical results from similar projects.
Names withheld to preserve confidentiality.
Work with us
Every project we take on has a measurable outcome. Talk to our London team and we will show you exactly how we would approach your challenge.
Softomate Solutions Limited
Deen Dayal Yadav
Online