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

Scenario

In a high‑concurrency data‑processing environment, the response time of a rule‑hit‑rate statistics API (over 2 million rows) was around 30 seconds, which was unacceptable for the transaction system.

Problem Diagnosis

The latency was traced to the SQL execution layer. Direct execution of the original query took about 800 ms, but the overall request still lasted 30 seconds, indicating additional overhead in the Java layer.

SQL Layer Analysis

The original MyBatis query:

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

SQL used to fetch programhandleidlist:

SELECT programhandleidlist FROM anti_transhandle WHERE create_time BETWEEN '2024-01-08 00:00:00.0' AND '2024-01-09 00:00:00.0';

Running EXPLAIN ANALYZE showed that the database work itself was fast, so the bottleneck lay elsewhere.

Code Layer Analysis

Each row was materialised as a Map in Java, creating over 2 million map objects, which dramatically slowed processing.

Optimization Measures

1. SQL Optimization

Transform the 2 million rows into a single aggregated result using PostgreSQL's array_agg and unnest functions:

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 amount of data transferred to the application layer.

2. Move Business Logic to the Database

Count occurrences of each ID 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 sheer data volume, highlighting the unsuitability of relational databases for massive analytical workloads.

3. Introduce Caching (Caffeine)

Added a local Caffeine cache to store historical statistics, reducing repeated database hits. Dependency:

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

Cache configuration:

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), () -> {
            Map<String, String> hitRateList = dataTunnelClient.selectTransHandleFlowByTime(currentDate.minusDays(1));
            // compute hitRate
            return hitRate;
        });
    // further processing
}

Result

After applying the SQL rewrite, moving aggregation to the database, and caching historical results, the API response time dropped from 30 seconds to under 0.8 seconds.

Conclusion

The case demonstrates the limits of relational databases for massive analytical queries and shows how careful SQL tuning, pushing computation to the DB, and strategic caching can dramatically improve backend performance.