Rescuing a legacy prescription viewer from 20-second load times to reliable performance
Services Provided
Web Development, Code Review, Application Modernization, Performance Optimization, Project Recovery
Project Technologies
PHP | Laravel | SQL Server | Docker | Azure App Service
Industry Served
Healthcare
Team Composition
1 Full-Stack Developer
Project Duration
Ongoing since 2022
TLDR
Del Mar Medical's Reorder Viewer application had become nearly unusable, with 20-second load times that caused crashes and left customer pharmacies unable to access patient prescription information. The application was a legacy PHP script built years earlier by a database administrator, containing complex SQL Server queries that had grown slower as the database expanded. Twin Sun reverse-engineered the relevant database structure, built a test environment with fabricated data, and discovered that breaking the single massive query into several targeted calls achieved a 10x performance improvement without any database modifications. Within two weeks, the team delivered a complete replacement on a modern, Dockerized platform hosted on Azure, with automated testing, reproducible deployments, and new capabilities including granular permissions, audit logging, printable reports, multi-factor authentication, and intelligent PDF handling that extracts only relevant pages from batch documents.
The Challenge
Del Mar Medical’s Reorder Viewer application had become nearly unusable. This web-based tool enables pharmacists at customer facilities to see what prescriptions have been filled for a particular patient. The application had a typical origin story: a database administrator built something that worked well enough years ago, someone saw it, and it quickly became part of Del Mar’s offering to customer pharmacies.
Over time, the database grew. The queries got slower. Eventually, a single patient lookup took 20 seconds or more. The application would crash or become inaccessible under normal load. Customer pharmacies could not see patient reorder information when they needed it.
The core problem lived in a legacy PHP script, mostly stored in a couple of very large files containing complex SQL Server queries. The main database query was dozens of lines of code with multiple unions, subselects, and distinct operations. It was incomprehensible at first glance and took considerable effort to understand.
Our Solution
We spent the first few days understanding what we inherited. The queries were hairy and the code was difficult to follow, but we needed to understand the system before making any changes.
We faced a significant constraint: this was an electronic medical record system. We could not modify the database or put undue load on it. FrameworkECM is closed source, we did not understand its internals, and we did not want to be the reason future upgrades might break.
To test our changes safely, we reverse-engineered the relevant parts of the FrameworkECM database structure through examining the SQL queries piece by piece. We built a Docker container with a replica database seeded with fabricated test data. This gave us an environment where we could compare apples to apples: does the new system match the results of the old one?
The application itself was just a web page with input fields feeding values into that massive database query. All the business logic lived in the queries, not the application code. We decided the best course was a full rewrite in our standard tech stack. The application code would take a day or two to rebuild, and that would free us to focus on what actually mattered: optimizing those database queries.
The breakthrough came when we realized that breaking apart the single massive query into several calls was actually faster than the original approach. This allowed us to eliminate irrelevant records early in the process, filter by facility permissions upfront, and avoid the expensive operations that made the original query so slow. We achieved a 10x performance improvement without making any database changes, solely through changing our approach to querying data.
The Results
Within two weeks, we delivered a replacement that performed significantly better than the old system: response times dropped from 20 seconds to 2 seconds.
The new application runs on a Dockerized environment hosted on Azure App Service, with reproducible deployments, a replicable server environment, and a suite of automated tests running in continuous integration before every deploy — deploys always work. When heavy concurrent usage during normal business hours revealed remaining bottlenecks, we introduced a caching layer for result sets and pagination for large data sets, further reducing load and improving response times.
Beyond performance, we delivered capabilities the original application never had: granular user roles and permissions managed by administrators rather than requiring a DBA, audit logging accessible without external developer help, printable reports for patients and reorder numbers by facility, multi-factor authentication with multiple delivery options, and intelligent PDF handling that extracts only the relevant pages from batch documents containing multiple patients — work that required a deeper understanding of the client’s database than the original script ever had.
