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.
1 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.
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.
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 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.
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.
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.
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