Optimizing a High‑Concurrency Interface: Reducing Response Time from 30 s to 0.8 s

In a high‑concurrency data‑processing scenario, an interface that queried over 2 million rows originally took about 30 seconds to respond; the author, a senior architect, shares the diagnosis and a series of optimizations that brought the latency down to 0.8 seconds.

Scenario

The interface calculates rule hit rates for a transaction system, requiring the aggregation of a large array column ( programhandleidlist ) from a PostgreSQL table.

Problem Diagnosis

Initial profiling showed the delay was not due to network or hardware but to the SQL execution. Direct execution of the SQL in the database took only ~800 ms, while the full request took 30 s, indicating overhead in the Java layer.

SQL Layer Analysis

Using EXPLAIN ANALYZE revealed that the query scanning 2 million rows was the main cost.

Code Layer Analysis

The MyBatis call returned a List<Map<String, Object>> , creating a map for each row, which became a major performance bottleneck.

List<Map<String, Object>> list = transhandleFlowMapper.selectDataTransHandleFlowAdd(selectSql);

To reduce object creation, the author transformed the query to return a single aggregated array using PostgreSQL array_agg and unnest functions.

Optimization Measures

1. SQL Optimization

Aggregate the 2 million rows into one array:

SELECT array_agg(elem) AS concatenated_array
FROM (
    SELECT unnest(programhandleidlist) AS elem
    FROM anti_transhandle
    WHERE create_time BETWEEN '2024-01-08 00:00:00.0' AND '2024-01-09 00:00:00.0'
) sub;

This reduces the result set to a single row, cutting the data transfer size.

2. Move Business Logic to the Database

Count occurrences of each element directly in SQL:

SELECT elem, COUNT(*) AS count
FROM (
    SELECT unnest(programhandleidlist) AS elem
    FROM anti_transhandle
    WHERE create_time BETWEEN '2024-01-08 00:00:00.0' AND '2024-01-09 00:00:00.0'
) sub
GROUP BY elem;

This query runs in ~0.7 seconds, still limited by the data volume, highlighting the need for column‑oriented storage for massive analytics.

3. Introduce Caching

Integrate Caffeine as a local cache to avoid repeated database hits for historical data.

<dependency>
    <groupId>com.github.ben-manes.caffeine</groupId>
    <artifactId>caffeine</artifactId>
    <version>3.1.8</version>
</dependency>

import com.github.benmanes.caffeine.cache.Caffeine;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.cache.caffeine.CaffeineCacheManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.concurrent.TimeUnit;

@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public CacheManager cacheManager() {
        CaffeineCacheManager cacheManager = new CaffeineCacheManager();
        cacheManager.setCaffeine(Caffeine.newBuilder()
            .maximumSize(500)
            .expireAfterWrite(60, TimeUnit.MINUTES));
        return cacheManager;
    }
}

Cache usage in the service method:

@Autowired
private CacheManager cacheManager;

private static final String YESTERDAY_HIT_RATE_CACHE = "hitRateCache";

@Override
public RuleHitRateResponse ruleHitRate(LocalDate currentDate) {
    double hitRate = cacheManager.getCache(YESTERDAY_HIT_RATE_CACHE)
        .get(currentDate.minusDays(1), () -> {
            // query database
            Map
hitRateList = dataTunnelClient.selectTransHandleFlowByTime(currentDate.minusDays(1));
            // compute and return result
            return computeHitRate(hitRateList);
        });
    // further processing
}

Conclusion

After applying SQL aggregation, moving part of the logic to the database, and adding Caffeine caching, the interface response time dropped from 30 seconds to under 0.8 seconds. The case also demonstrates the limitations of relational databases for massive analytics and suggests column‑store solutions like ClickHouse for truly millisecond‑level queries.