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

In a high‑concurrency data‑processing scenario, the response time of an interface handling over 2 million records was initially around 30 seconds. The author, a senior architect, walks through the diagnosis and a series of optimizations that brought the latency down to under 0.8 seconds.

Problem Diagnosis

The initial investigation ruled out network and hardware issues and revealed that the bottleneck lay in the SQL execution, which took about 30 seconds to fetch 2 million rows.

Key MyBatis query:

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

SQL used to fetch the programhandleidlist column:

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

SQL‑Level Analysis

Using EXPLAIN ANALYZE showed that the query itself executed in about 800 ms, indicating that the heavy cost was introduced by the Java layer processing the result set.

Code‑Level Analysis

Each row was materialized as a Map object, creating millions of objects and causing severe GC pressure.

Optimization Measures

1. SQL Optimization

The goal was to collapse the 2 million rows into a single result 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 returns one aggregated array instead of millions of rows.

2. Moving Business Logic to the Database

To 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 reduces data transferred to the application and cuts processing time.

3. Introducing Caching (Caffeine)

A local Caffeine cache was added to store historical statistics, avoiding repeated heavy queries.

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

The method ruleHitRate was updated to check the cache before querying the database.

Result

After applying the SQL aggregation, moving counting logic to PostgreSQL, and adding Caffeine caching, the interface response time dropped from 30 seconds to under 0.8 seconds, demonstrating the importance of profiling, proper data modeling, and leveraging both database and in‑memory caches for high‑performance back‑ends.

Takeaways

Relational databases excel at transactional workloads but may struggle with massive analytical queries; column‑oriented stores (e.g., ClickHouse) can be more suitable for such cases.

Avoid creating large numbers of Java objects for massive result sets; prefer set‑based operations in the database.

Local caches like Caffeine can dramatically reduce repeated heavy queries when data freshness requirements allow.