Summary

The encountered massive scalability issues of the application were investigated and causes identified and resolution paths offered.

Database interactions where blocking operations up to critical level leading to massively underperforming scalabilitly and even challenging operation with a tenth of the planned capacity of operators.

The customer's own development team were at a deadend and management requested a second opinion.

The Project

The insurance company had built a custom application to run their day-to-day operations with their workforce of about 100 operators.

When scalability tests were run with a relatively small test group it turned out that the system significantly and critically underperformed regarding response times and actual practicability to work with.

It was found out that database response times (MS SQL) were at times taking in the order of minutes and longer not allowing for realistic usage of the application while running reports in parallel as planned.

During the investigation it was found that besides database server and query optimizations weren't resolving the issues and finally the transactional model for normal operations was identified as the issue. Table- and row-based locking of slow running processes even for read-only processes lead to a significant slow down of the database query throughput and finally the application performance.

Suggestions for optimzations included changing the transactional model used originally and also distinguishing between read and write operations.

Benefits

Already initial suggestions (Server Parameter Improvments, General Transactional Model, ...) significantly improved the performance allowing to move onward to the allowed scale of operators.

Also recommendations for longer-term code-based changes provide a path to increase performance even more.