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.
Names withheld to preserve confidentiality.
Names withheld to preserve confidentiality.