Boost Database Query Performance with Spring AOP: Parallel IN Splitting Technique

This feature was originally built at a previous company to address severe performance degradation when PostgreSQL IN queries contain hundreds or thousands of parameters, which slows down the interface response. The solution uses multithreading via a custom Spring AOP annotation to split the large IN list, execute sub‑queries in parallel, and merge the results. SELECT * FROM device WHERE id IN (1, 2, 3, 4) The query is split into two smaller queries:

SELECT * FROM device WHERE id IN (1, 2)
SELECT * FROM device WHERE id IN (3, 4)

These sub‑queries are executed concurrently, and their results are combined.

Because this pattern is frequently needed, a Spring AOP annotation was created so that developers only need to add the annotation to enable the optimization.

Define AOP Annotation

The annotation includes the following parameters:

setThreadPool: the thread pool to use (avoid using a shared pool).

handlerReturnClass: the class that defines how to process and merge the returned values.

splitLimit: the minimum number of items that triggers splitting.

splitGroupNum: the number of items per split group.

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SplitWorkAnnotation {
    /** Set the thread pool */
    ThreadPoolEnum setThreadPool();
    /** Return value handling class */
    Class<? extends HandleReturn> handlerReturnClass() default MergeFunction.class;
    /** Split when size exceeds this limit */
    int splitLimit() default 1000;
    /** Number of items per split group */
    int splitGroupNum() default 100;
}

A parameter that needs to be split is marked with a separate annotation, which supports only a single parameter per method.

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.PARAMETER)
public @interface NeedSplitParam {}

Use AOP to Implement Parallel Split and Merge Logic

@Aspect
@Component
@Slf4j
public class SplitWorkAspect {
    @Pointcut("@annotation(com.demo.SplitWorkAnnotation)")
    public void needSplit() {}

    @Around("needSplit()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        Method targetMethod = ((MethodSignature) pjp.getSignature()).getMethod();
        SplitWorkAnnotation splitAnno = targetMethod.getAnnotation(SplitWorkAnnotation.class);
        Object[] args = pjp.getArgs();
        int splitLimit = splitAnno.splitLimit();
        int splitGroupNum = splitAnno.splitGroupNum();
        if (args == null || args.length == 0 || splitLimit <= splitGroupNum) {
            return pjp.proceed();
        }
        int splitParamIndex = -1;
        for (int i = 0; i < targetMethod.getParameters().length; i++) {
            if (targetMethod.getParameters()[i].isAnnotationPresent(NeedSplitParam.class)) {
                splitParamIndex = i;
                break;
            }
        }
        if (splitParamIndex == -1) {
            return pjp.proceed();
        }
        Object splitParam = args[splitParamIndex];
        if (!(splitParam instanceof Object[]) && !(splitParam instanceof List) && !(splitParam instanceof Set)) {
            return pjp.proceed();
        }
        boolean notMeetSplitLen = (splitParam instanceof Object[] && ((Object[]) splitParam).length <= splitLimit)
                || (splitParam instanceof List && ((List<?>) splitParam).size() <= splitLimit)
                || (splitParam instanceof Set && ((Set<?>) splitParam).size() <= splitLimit);
        if (notMeetSplitLen) {
            return pjp.proceed();
        }
        // Optional deduplication for List
        if (splitParam instanceof List) {
            List<?> list = (List<?>) splitParam;
            if (list.size() > 1) {
                splitParam = new ArrayList<>(new HashSet<>(list));
            }
        }
        int batchNum = getBatchNum(splitParam, splitGroupNum);
        if (batchNum == 1) {
            return pjp.proceed();
        }
        CompletableFuture<?>[] futures = new CompletableFuture[batchNum];
        ThreadPoolEnum threadPool = splitAnno.setThreadPool();
        if (threadPool == null) {
            return pjp.proceed();
        }
        try {
            for (int batch = 0; batch < batchNum; batch++) {
                final int currentBatch = batch;
                futures[batch] = CompletableFuture.supplyAsync(() -> {
                    Object[] newArgs = Arrays.copyOf(args, args.length);
                    newArgs[splitParamIndex] = getPartParam(splitParam, splitGroupNum, currentBatch);
                    try {
                        return pjp.proceed(newArgs);
                    } catch (Throwable e) {
                        throw new RuntimeException(e);
                    }
                }, threadPool.getThreadPoolExecutor());
            }
            CompletableFuture.allOf(futures).get();
            Class<? extends HandleReturn> handleCls = splitAnno.handlerReturnClass();
            List<Object> results = new ArrayList<>(futures.length);
            for (CompletableFuture<?> f : futures) {
                results.add(f.get());
            }
            return handleCls.getDeclaredMethod("handleReturn", List.class)
                    .invoke(handleCls.getDeclaredConstructor().newInstance(), results);
        } catch (InterruptedException | ExecutionException e) {
            throw new RuntimeException(e);
        }
    }

    public Integer getBatchNum(Object param, Integer splitGroupNum) {
        if (param instanceof Object[]) {
            int len = ((Object[]) param).length;
            return (len % splitGroupNum == 0) ? len / splitGroupNum : len / splitGroupNum + 1;
        } else if (param instanceof Collection) {
            int len = ((Collection<?>) param).size();
            return (len % splitGroupNum == 0) ? len / splitGroupNum : len / splitGroupNum + 1;
        }
        return 1;
    }

    public Object getPartParam(Object param, Integer splitGroupNum, Integer batch) throws Exception {
        if (param instanceof Object[]) {
            Object[] arr = (Object[]) param;
            int end = Math.min((batch + 1) * splitGroupNum, arr.length);
            return Arrays.copyOfRange(arr, batch * splitGroupNum, end);
        } else if (param instanceof List) {
            List<?> list = (List<?>) param;
            int end = Math.min((batch + 1) * splitGroupNum, list.size());
            return list.subList(batch * splitGroupNum, end);
        } else if (param instanceof Set) {
            List<?> list = new ArrayList<>((Set<?>) param);
            int end = Math.min((batch + 1) * splitGroupNum, list.size());
            Set<Object> set = param.getClass().getDeclaredConstructor().newInstance();
            set.addAll(list.subList(batch * splitGroupNum, end));
            return set;
        }
        return null;
    }
}

Define Interface for Handling Return Values

public interface HandleReturn {
    /** Process the list of sub‑query results and return the merged value */
    Object handleReturn(List t);
}

A simple merge implementation that concatenates list results:

public class MergeFunction implements HandleReturn {
    @Override
    public Object handleReturn(List results) {
        if (results == null) return null;
        if (results.size() <= 1) return results.get(0);
        List first = (List) results.get(0);
        for (int i = 1; i < results.size(); i++) {
            first.addAll((List) results.get(i));
        }
        return first;
    }
}

The accompanying diagram illustrates the overall flow from annotation detection to parallel execution and result merging.