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 screenAssembly strategy
Base reservations and patient detailsQueryDSL constructor projections
Operations, waiting state and sub-reservationsBatch reads, indexed by organization, patient and reservation sequence
Survey answers, hashtags and earliest clinic dateBatch reads, indexed by organization and patient
Linked appointments, tickets and ticket usageSeparate projections and grouped results
Receipts and payment stateGrouped 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

TechniqueWork it avoids or limits
Scalar/constructor DTO projectionsHydrating managed entity graphs and carrying those graphs through response assembly
Separate collection readsMultiplying independent one-to-many relationships into one enormous joined result
Keyed maps and precomputed duplicate countsRepeated searches across the entire day’s related records
Database aggregation for earliest clinic datesLoading every historical visit just to determine the earliest date
Receipt-detail existence projectionLoading a detail collection when only its presence is needed
Cache the assembled reservation listRepeating 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

StepDatabase/cache stateRequest behavior
1. Staff open a date rangeRevision is v1; the matching key is absentLoad the projections, assemble the list and cache it under v1
2. Staff open the same range againRevision remains v1; the key existsReuse the assembled list, then apply request-specific filtering
3. A relevant clinical record changesA database trigger advances the revision to v2The old v1 entry is now stale relative to the database
4. The next request observes v2Its new key differs from the existing v1 keyMiss, rebuild from the database, then cache under v2
5. Older entries reach their TTLThe short-term cache uses a five-minute TTLExpire 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.