Optimizing a High-Concurrency Interface: Reducing Response Time from 30 s to 0.8 s with SQL Refactoring and Caffeine Caching

In a high‑concurrency data‑processing scenario, the response time of an interface handling over 2 million records was reduced from 30 seconds to under 0.8 seconds through a series of optimizations.

Scenario

The interface calculates rule interception rates for each transaction in a trading system. Initial latency was about 30 seconds.

Problem Diagnosis

Network and server hardware were ruled out; profiling showed the bottleneck was SQL execution. The MyBatis query returned a list of maps, each map representing a row, causing massive object creation overhead.

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

Original SQL:

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 the raw SQL in PostgreSQL took about 800 ms, far less than the 30 s observed in the application.

SQL Layer Analysis

Using EXPLAIN ANALYZE confirmed the query itself was fast; the slowdown originated from Java processing.

Code Layer Analysis

Each of the 2 million rows was turned into a Map , leading to heavy memory allocation and copying.

Optimization Measures

1. SQL Optimization

Transform the 2 million rows into a single row using PostgreSQL array 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 aggregates the array elements, reducing the amount of data transferred to the application.

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 about 0.7 seconds, still limited by the data volume, highlighting the unsuitability of relational databases for massive analytical workloads.

3. Introduce Caching with Caffeine

Local caching was added to avoid repeated database hits for historical dates. The Caffeine dependency and configuration are:

<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;
    }
}

The service method was updated to check the cache before querying the database:

@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
hitRateList = dataTunnelClient.selectTransHandleFlowByTime(currentDate.minusDays(1));
            // ... compute hitRate ...
            return hitRate;
        });
    // ... other logic ...
}

Summary

After applying SQL refactoring, moving aggregation to PostgreSQL, and adding Caffeine caching, the interface latency dropped from 30 seconds to under 0.8 seconds. The case demonstrates the limitations of relational databases for massive data analytics and suggests column‑oriented stores (e.g., ClickHouse, Hive) for truly millisecond‑level queries.

Additionally, the article includes promotional material for ChatGPT services, a developer community, and various paid offerings.