This article outlines the process of improving the performance of a commercial medical service provider's patient management system without resorting to expensive server upgrades. The solution involves separating employee and public portals, distributing data across multiple servers, implementing archiving and web services, and utilizing Windows Service scheduled timer jobs for report generation. The implementation is based on ASP.NET, MS SQL, and AWS infrastructure.
Note: information shared in this article does not reveal any confidential information or workflow.
The patient management system of a commercial medical service provider with over 500K clients in Singapore faces performance issues, including slow report generation and timeouts during new registrations. The system, built with ASP.NET and MS SQL and hosted on AWS, handles more than 5 million records in key tables. AWS recommends expensive dedicated host and high-performance database server upgrades, which the client rejects. This article presents a cost-effective solution to optimize the system's performance without resorting to server upgrades.
2. Solution Design
The proposed solution involves the following steps:
- Separating employee and public patient portals
- Distributing data across multiple servers
- Implementing an Archive Automation Service
- Creating web service APIs for data access
- Generating reports using Windows Service scheduled timer jobs
Note: the diagram above is for showcase only. Not identical to the deployed design.
3.1 Separating Portals
The employee-accessed portal and public patient-accessed portal are separated and hosted on different servers. This ensures that each portal has dedicated resources, improving overall system performance and reducing the likelihood of timeouts.
3.2 Data Distribution
Three additional servers with regular specifications are set up to host specific data:
- Server 1: Log data older than one week
- Server 2: Patient visit data older than six months
- Server 3: Health reports older than six months
By distributing older data across these servers, the core production database retains only around 500K records in key tables, reducing the load on the primary server.
3.3 Archive Automation Service
An Archive Automation Service is implemented to transfer data from the primary server to the additional servers based on specific settings. It also removes physical reports older than 30 days. The service periodically scans the primary server's tables, transferring data to the appropriate secondary server and deleting it from the primary server.
3.4 Web Service APIs
Web service APIs are created on the additional servers, and firewall controls are set up to allow production servers to call these APIs. The APIs return data in real-time upon request, ensuring that the primary server can access older data when needed.
3.5 Windows Service Scheduled Timer Jobs
A Windows Service scheduled timer job is implemented to generate patient reports on the secondary servers. The timer job periodically generates reports, offloading the report generation process from the primary server and improving its performance.
The cost-effective scalability solution presented in this article optimizes the performance of a large-scale medical service provider's patient management system without requiring expensive server upgrades. By separating portals, distributing data across multiple servers, implementing an Archive Automation Service, and utilizing web service APIs and Windows Service scheduled timer jobs, the system can handle high volumes of data while maintaining fast response times and smooth operation.
5. Future Improvements
The solution can be further enhanced with the following improvements:
- Implementing caching mechanisms to reduce database load
- Optimizing database queries and indexes for faster data retrieval
- Monitoring system performance and adjusting server resources as needed
- Exploring the use of AWS services like Amazon RDS or Amazon Aurora for improved database performance
By continually refining the system's performance, the medical service provider can ensure reliable and efficient patient management while keeping infrastructure costs under control.