On this page
A clinic’s reservation board needs appointments, patient details, waiting status, procedures, payments and clinical history to stay current. In many hospitals, the C# EMR and SQL Server database ran on the same main PC. The reservation-loading flow involved roughly 50 tables overall, rather than a single 50-table join. On underpowered machines, repeatedly querying that flow created CPU and memory pressure. Clinics complained about the slow board, and users relied on manual refresh rather than automatic updates.
Most of the API delay was in database queries and data fetching. Fetching only changed fields reduced the data sent to the client and Spring Boot memory usage for this reservation API by 90% each. Response time fell from 2 seconds to 0.2 seconds, while database-versioned caching kept the reservation board current.
These are historical results from my work on this API, not a newly reproduced benchmark. The original raw measurement set and separate cold-cache/warm-cache results are not retained. The 90% figures describe client data transfer and Spring Boot memory usage for this API; they do not describe all application traffic, process-wide heap usage or a measured CPU reduction.
Caching had to account for the way hospitals actually worked. Other programs could change the same SQL Server records, and appointments should not remain stale simply because a write bypassed the Spring API. I implemented invalidation around changes to relevant database columns, alongside reads limited to the data the reservation board needed.
The Kotlin examples below are shortened adaptations with neutral names. They preserve the relevant query, cache and execution behavior while omitting unrelated fields, imports and application wiring.
Build the clinic’s day as a read model
A request identifies an organization and a date range. On a cache miss, the application loads eleven related datasets:
| Data needed by the screen | Assembly strategy |
|---|---|
| Base reservations and patient details | QueryDSL constructor projections |
| Operations, waiting state and sub-reservations | Batch reads, indexed by organization, patient and reservation sequence |
| Survey answers, hashtags and earliest clinic date | Batch reads, indexed by organization and patient |
| Linked appointments, tickets and ticket usage | Separate projections and grouped results |
| Receipts and payment state | Grouped receipt DTOs, including an existence check for receipt details |
These are sequential batch reads, not eleven queries per reservation. There are additional configuration reads, so eleven is not the endpoint’s total SQL statement count.
For the main list, the query selects the fields the screen needs directly into DTOs. This reduced example shows the projection and date scope:
data class AppointmentSlice(
val organization: String,
val patient: String,
val sequence: Int,
val day: LocalDate,
)
val appointments = queries
.select(
Projections.constructor(
AppointmentSlice::class.java,
appointment.organization,
appointment.patient,
appointment.sequence,
appointment.day,
)
)
.from(appointment)
.where(
appointment.organization.eq(organization),
appointment.day.between(firstDay, lastDay),
)
.fetch()
The application then indexes each related dataset once. A composite key keeps records from different organizations, patients and reservations distinct. Receipts use grouping because one appointment can have multiple receipt rows:
data class AppointmentKey(
val organization: String,
val patient: String,
val sequence: Int,
)
val waitingByAppointment = waitingRows.associateBy {
AppointmentKey(it.organization, it.patient, it.sequence)
}
val receiptsByAppointment = receiptRows.groupBy {
AppointmentKey(it.organization, it.patient, it.sequence)
}
val screenRows = appointments.map { row ->
val key = AppointmentKey(row.organization, row.patient, row.sequence)
ScreenRow(
appointment = row,
waiting = waitingByAppointment[key],
receipts = receiptsByAppointment[key].orEmpty(),
)
}
This avoids repeatedly scanning every related list for every appointment. The full assembly also calculates duplicate-reservation counts once per patient and date before flattening main and sub-reservations into the final list. Maps add memory of their own, but they replace repeated matching work with direct lookups.
Reduce work before adding the cache
| Technique | Work it avoids or limits |
|---|---|
| Scalar/constructor DTO projections | Hydrating managed entity graphs and carrying those graphs through response assembly |
| Separate collection reads | Multiplying independent one-to-many relationships into one enormous joined result |
| Keyed maps and precomputed duplicate counts | Repeated searches across the entire day’s related records |
| Database aggregation for earliest clinic dates | Loading every historical visit just to determine the earliest date |
| Receipt-detail existence projection | Loading a detail collection when only its presence is needed |
| Cache the assembled reservation list | Repeating the batch reads and assembly on every matching request |
These choices target database transfer, object allocation and application CPU. The miss path still holds raw DTO lists, maps, intermediate rows and the final response model. Reducing unnecessary work is different from making a large response allocation-free.
Why I set aside CDC across the hospital fleet
CDC was an option for detecting writes made outside the API. In this environment, however, it meant creating and operating connectors for 1,000 separate hospital database hosts. Managing that fleet proved unmanageable, and the Kafka/Debezium connections were unstable. I set that approach aside and moved change detection into database triggers that advance a cache revision.
The cache needed to know that its read model had changed; it did not need a retained stream of every change from every hospital. A revision kept that decision close to the database where both the API and legacy programs wrote. It still required maintaining trigger coverage, but removed the per-host connector fleet from this cache design.
This was separate from the central reservation outbox, where Debezium captured one platform’s recorded integration work. The operational shape of those two CDC uses was different.
Put the database version in the cache key
I stored a change timestamp for the reservation cache family in the hospital database. Each cache lookup reads that revision and includes it alongside the organization and requested dates. The cache manager separately prefixes the cache namespace with the current hospital identity.
The important relationship is that the database revision participates in key generation, before Spring decides whether it can reuse the result:
@Component("dayKeys")
class DayKeys(private val revisions: RevisionReader) {
fun forWindow(
organization: String,
firstDay: LocalDate,
lastDay: LocalDate,
): String {
val revision: Instant = revisions.currentOrCreate()
return "$organization:$firstDay:$lastDay:$revision"
}
}
@Service
class DayReader(private val assembler: DayAssembler) {
@Cacheable(
cacheManager = "shortLived",
cacheNames = ["daily-view"],
key = "@dayKeys.forWindow(#organization, #firstDay, #lastDay)",
sync = true,
)
fun read(
organization: String,
firstDay: LocalDate,
lastDay: LocalDate,
): List<ScreenRow> = assembler.assemble(organization, firstDay, lastDay)
}
The revision reader retrieves the timestamp from the current hospital’s database and initializes an absent row through a separate transaction. The key is shared across users who request the same organization and dates; their visibility rules run afterward.
The cache stores the assembled DTO list as JSON with a five-minute TTL. This fragment shows the relevant defaults passed into the hospital-aware cache manager; the serializer includes the required Kotlin and date/time support:
val defaults = RedisCacheConfiguration.defaultCacheConfig()
.entryTtl(Duration.ofMinutes(5))
.serializeValuesWith(
RedisSerializationContext.SerializationPair.fromSerializer(
dtoJsonSerializer
)
)
I attached database triggers so changes to relevant columns used by the reservation read advanced the revision. That let writes from the legacy hospital system participate in invalidation too. This trigger behavior is part of my implementation account; the available application evidence does not establish the complete deployed trigger coverage.
Follow one read, one write, and the next read
| Step | Database/cache state | Request behavior |
|---|---|---|
| 1. Staff open a date range | Revision is v1; the matching key is absent | Load the projections, assemble the list and cache it under v1 |
| 2. Staff open the same range again | Revision remains v1; the key exists | Reuse the assembled list, then apply request-specific filtering |
| 3. A relevant clinical record changes | A database trigger advances the revision to v2 | The old v1 entry is now stale relative to the database |
4. The next request observes v2 | Its new key differs from the existing v1 key | Miss, rebuild from the database, then cache under v2 |
| 5. Older entries reach their TTL | The short-term cache uses a five-minute TTL | Expire old entries without finding and deleting each date-range key |
The trigger does not push a new response into Redis. It changes which key subsequent requests ask for. One revision covers the reservation cache family in that hospital database, so a write can invalidate more than the single date range that changed. That trades finer-grained reuse for a simpler invalidation contract.
A write after the revision lookup can still race with query execution and assembly. Neither key versioning nor a five-minute TTL establishes strict consistency; freshness depends on trigger coverage and the behavior of concurrent reads and writes.
Keep user filtering outside the shared cache
The request handler retrieves the shared list through a separate Spring bean, then applies the current staff member’s visibility rules. Automatic no-show and completion reconciliation receives the unfiltered list through another proxied bean:
val rows = sharedReader.read(organization, firstDay, lastDay)
val visibleRows = staffPolicy.apply(currentStaff, rows)
reconciliation.reconcile(organization, rows)
return visibleRows
A hit still reads the revision, deserializes Redis JSON, evaluates the applicable visibility rules and serializes the HTTP response. It reuses the expensive response model; it does not cache pre-encoded HTTP bytes or eliminate all database access.
Synchronized Spring cache loading is enabled, and Redis uses a locking cache writer. Those are load-coordination settings, not proof of unlimited per-key single-flight behavior across replicas and failures.
Reconciliation runs asynchronously. A one-minute Redis admission key suppresses repeated submissions that reach the worker for the same organization:
@Async("contextAwareExecutor")
@Transactional
fun reconcile(organization: String, rows: List<ScreenRow>) {
val admitted = redis.opsForValue().setIfAbsent(
"reconcile:$organization",
"1",
Duration.ofMinutes(1),
)
if (admitted != true) return
applyConfiguredStatusChanges(organization, rows)
}
The gate sits inside the worker, so it does not prevent task submission or bound the executor queue. It moves eligible status reconciliation off the request thread, but it is neither a durable job queue nor a fenced lock: sufficiently long tasks can outlive the key’s expiry.
Follow unexplained latency across hosting environments
The performance investigation also exposed a different problem. Most hospital databases ran on the clinic’s own main computer, but some hospitals used databases hosted on Naver Cloud Platform. In some of those hospitals, fetching query results took up to one second longer. This was a difference in query-fetch time, not a packet round-trip-time measurement.
I compared packet timings and network paths across the environments. The delay was concentrated in a particular NCP VPC environment. I assembled those comparisons and presented them in technical meetings with NCP engineers. NCP confirmed the cause and implemented a fix.
The provider confirmation and fix are part of my account of the work. I do not have a retained post-fix timing series to quantify the improvement. The exact historical command names are not preserved, so this account describes the packet timing and network-path comparisons without reconstructing a command transcript.
Make the tradeoffs visible
Some projection queries and the revision lookup use SQL Server’s non-locking read hint. This reduces certain lock waits at the cost of permitting dirty or inconsistent reads. It is not snapshot isolation and should not be used to imply strict consistency.
An explicit refresh path can also search Redis keys and delete matching entries. That operation can become costly as the keyspace grows. Ordinary revision changes avoid that search: they select a new key and let older entries expire.
The engineering result was a much faster reservation experience while acknowledging who else could change its data. The work crossed the query shape, JVM object model, application assembly, Redis cache and legacy database boundary. The JDBC parameter investigation covers a separate bottleneck; three resource-lifecycle decisions show how similar care appeared elsewhere in the product.