Why do large IN clauses hurt performance?

Q: Why do large IN clauses hurt performance?

The generated SQL for findByIdIn(List<Long ids) with 10,000 IDs looks like this: SELECT FROM trades WHERE trade_id IN (?, ?, ?, ?, ... / 10,000 question marks / ?) This causes five specific problems. First, the SQL string itself is enormous – potentially hundreds of kilobytes. Parsing this takes mea

Q: How does a temp table approach work?

For cases where you must pass a large list and serverside filtering is not possible, a temp table lets the database JOIN against the IDs efficiently. package com.bank.repository; import com.bank.model.Trade; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.dat

Q: What should a senior engineer know?

Hibernate IN clause padding. Hibernate 5.2+ pads the IN clause to the next power of two (128, 256, 512). This reduces plan cache pollution because the same prepared statement is reused for 257 IDs and 512 IDs. Enable with hibernate.query.in_clause_parameter_padding=true. Query plan cache size. Hiber

Q: What are common mistakes with large IN clauses?

The most common mistake is passing a usercontrolled list directly to findByIdIn() without size validation. The second most common mistake is chunking without considering the total result size in memory. package com.bank.service; import com.bank.model.Trade; import com.bank.repository.TradeRepository

Q: When should you use each approach?

Choose the approach based on your situation: Serverside filter: always the best choice when possible. Replace WHERE id IN (...) with WHERE status = 'PENDING' AND date = '20240115'. Chunking: use when you must pass IDs and the list comes from another service or API. Chunk size 500 works across all da

Definition

A large IN clause with thousands of IDs causes huge SQL, plan cache pollution, parameter limits, and poor optimizer decisions – fix by chunking into batches of 500, using a temp table, or redesigning to a server-side filter.

Huge SQL string – 10,000 parameters generate a massive query text that must be parsed, planned, and transmitted over the network.
Plan cache pollution – each different-length IN list is a different prepared statement. The database's plan cache fills with thousands of entries that are never reused.
Parameter limits – Oracle limits bind parameters to 1,000 per IN clause. PostgreSQL has no hard limit but degrades significantly above a few thousand.
Poor execution plans – the optimizer may choose a full table scan over 10,000 index probes.
Chunking – partition the ID list into batches of 500 and execute multiple queries. Each query is fast, the plan is cached and reused.
Server-side filter – replace the IN clause with a WHERE condition the database evaluates (WHERE status = 'PENDING' instead of WHERE id IN (10000 IDs)).

Asking the National Bank of India clerk to find accounts numbered 1 through 10,000 by reading all 10,000 numbers aloud takes forever. Better approaches: give the clerk a printed list (temp table) and say "find all accounts on this list." Or say "find all PENDING accounts" (server-side filter) – the clerk knows the criteria without needing 10,000 numbers. Or break into batches: "find accounts 1 through 500, then 501 through 1000."

Understanding the Concepts

The generated SQL for findByIdIn(List ids) with 10,000 IDs looks like this:

SQL

SELECT * FROM trades WHERE trade_id IN (?, ?, ?, ?, ... /* 10,000 question marks */ ?)

This causes five specific problems. First, the SQL string itself is enormous – potentially hundreds of kilobytes. Parsing this takes measurable time. Second, every different count of parameters is a different prepared statement. Third, Oracle has a hard limit of 1,000 bind parameters. Fourth, the query optimizer may not use the index efficiently. Fifth, transmitting 10,000 IDs over the network adds latency.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, Long> {
    List<Trade> findByIdIn(List<Long> ids);
    // With 10,000 IDs:
    // Generates: SELECT * FROM trades WHERE trade_id IN (?, ?, ... x 10000)
    // Oracle: ORA-01795: maximum number of expressions in a list is 1000
    // PostgreSQL: works but plan cache destroyed, slow execution
}

How does chunking fix the problem?

Chunking partitions the ID list into batches of 500 and executes multiple queries. Each query is fast, and the plan cache reuses the same prepared statement (always 500 parameters).

JAVA

package com.bank.service;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.List;

@Service
public class TradeService {

    private static final int CHUNK_SIZE = 500;
    private final TradeRepository tradeRepo;

    public TradeService(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    @Transactional(readOnly = true)
    public List<Trade> findByIds(List<Long> ids) {
        if (ids.size() <= CHUNK_SIZE) {
            return tradeRepo.findAllById(ids);  // small list: single query
        }
        // Large list: chunk it
        List<Trade> result = new ArrayList<>();
        for (int i = 0; i < ids.size(); i += CHUNK_SIZE) {
            List<Long> chunk = ids.subList(i, Math.min(i + CHUNK_SIZE, ids.size()));
            result.addAll(tradeRepo.findAllById(chunk));
        }
        return result;
        // 10,000 IDs / 500 = 20 queries
        // Each: SELECT * FROM trades WHERE trade_id IN (?, ?... x 500)
        // Plan cached and reused for every chunk!
    }
}

TEXT

Before chunking (10,000 IDs):
  1 query with 10,000 parameters
  Parse time: 150ms, execution: 12 seconds
  Oracle: ORA-01795 crash

After chunking (20 x 500):
  20 queries with 500 parameters each
  Parse time: 2ms each, execution: 600ms total
  Plan cached and reused for every batch

How does a server-side filter help?

The best fix is often redesigning the query entirely. Instead of shipping 10,000 IDs from the application, let the database filter using a WHERE condition it can evaluate with an index.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.time.LocalDate;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, Long> {

    // WRONG: shipping 10,000 IDs from the app
    List<Trade> findByIdIn(List<Long> ids);
    // SELECT * FROM trades WHERE trade_id IN (10000 IDs)

    // CORRECT: let the database filter server-side
    @Query("SELECT t FROM Trade t WHERE t.status = :status AND t.settlementDate = :date")
    List<Trade> findPendingForDate(@Param("status") String status,
                                   @Param("date") LocalDate date);
    // SELECT * FROM trades WHERE status = 'PENDING' AND settlement_date = '2024-01-15'
    // 2 parameters, index-friendly, 50ms
}

How does a temp table approach work?

For cases where you must pass a large list and server-side filtering is not possible, a temp table lets the database JOIN against the IDs efficiently.

JAVA

package com.bank.repository;

import com.bank.model.Trade;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

public interface TradeRepository extends JpaRepository<Trade, Long> {

    // Native query with temp table pattern
    @Query(value = """
        WITH id_list AS (
            SELECT unnest(CAST(:ids AS bigint[])) AS id
        )
        SELECT t.* FROM trades t
        JOIN id_list ON t.trade_id = id_list.id
        """, nativeQuery = true)
    List<Trade> findByIdsUsingArray(Long[] ids);
    // PostgreSQL-specific: passes IDs as a single array parameter
    // Database JOINs against the array efficiently
}

What chunk size should you use?

The recommended chunk size is 500. This is well under Oracle's 1,000 limit, efficient for PostgreSQL and MySQL, and large enough that you do not fire too many queries.

| Chunk Size | Queries for 10K IDs | Notes |
|-----------|--------------------|---------|
| 100 | 100 queries | Too many round-trips |
| 500 | 20 queries | Recommended: fast and safe |
| 1000 | 10 queries | Hits Oracle's limit |
| 5000 | 2 queries | Plan cache issues |

JAVA

package com.bank.util;

import java.util.ArrayList;
import java.util.List;

public class ListPartitioner {

    public static <T> List<List<T>> partition(List<T> list, int chunkSize) {
        List<List<T>> chunks = new ArrayList<>();
        for (int i = 0; i < list.size(); i += chunkSize) {
            chunks.add(list.subList(i, Math.min(i + chunkSize, list.size())));
        }
        return chunks;
    }
}
// Usage: ListPartitioner.partition(ids, 500)
// Returns: [[id1..id500], [id501..id1000], ...]

What should a senior engineer know?

Hibernate IN clause padding. Hibernate 5.2+ pads the IN clause to the next power of two (128, 256, 512). This reduces plan cache pollution because the same prepared statement is reused for 257 IDs and 512 IDs. Enable with hibernate.query.in_clause_parameter_padding=true.
Query plan cache size. Hibernate has a query plan cache (hibernate.query.plan_cache_max_size, default 2048). Each unique IN clause length creates a new entry. Without padding or chunking, 1,000 different lengths fill the cache quickly.
Batch fetching as an alternative. For entity relationships, use @BatchSize(size = 50) instead of collecting IDs and running findByIdIn(). Hibernate batches lazy-load queries automatically.
Spring Data JPA 3.0+ findAllById(). The default implementation already uses chunking internally for some databases. Check the behavior for your specific Spring Data version before adding manual chunking.

YAML

# application.yml: enable IN clause padding
spring:
  jpa:
    properties:
      hibernate:
        query:
          in_clause_parameter_padding: true
          plan_cache_max_size: 4096

What are common mistakes with large IN clauses?

The most common mistake is passing a user-controlled list directly to findByIdIn() without size validation. The second most common mistake is chunking without considering the total result size in memory.

JAVA

package com.bank.service;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
public class TradeMistakeDemo {

    private final TradeRepository tradeRepo;

    public TradeMistakeDemo(TradeRepository tradeRepo) {
        this.tradeRepo = tradeRepo;
    }

    // WRONG: no size validation, no chunking
    @Transactional(readOnly = true)
    public List<Trade> wrongApproach(List<Long> ids) {
        return tradeRepo.findByIdIn(ids);  // could be 100,000 IDs!
    }

    // CORRECT: validate size, chunk, and consider pagination
    @Transactional(readOnly = true)
    public List<Trade> correctApproach(List<Long> ids) {
        if (ids.isEmpty()) return List.of();
        if (ids.size() > 50_000) {
            throw new IllegalArgumentException(
                "ID list too large: " + ids.size() + ". Max: 50000");
        }
        return new TradeService(tradeRepo).findByIds(ids);
    }
}

When should you use each approach?

Choose the approach based on your situation:

Server-side filter: always the best choice when possible. Replace WHERE id IN (...) with WHERE status = 'PENDING' AND date = '2024-01-15'.
Chunking: use when you must pass IDs and the list comes from another service or API. Chunk size 500 works across all databases.
Temp table / array: use for very large lists (50K+) when chunking would fire too many queries. Database-specific.

JAVA

package com.bank.service;

import com.bank.model.Trade;
import com.bank.repository.TradeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDate;
import java.util.List;

@Service
public class TradeReconciliationService {

    private final TradeRepository tradeRepo;
    private final TradeService tradeService;

    public TradeReconciliationService(TradeRepository tradeRepo,
                                      TradeService tradeService) {
        this.tradeRepo = tradeRepo;
        this.tradeService = tradeService;
    }

    // Best: server-side filter
    @Transactional(readOnly = true)
    public List<Trade> findPendingTrades(LocalDate date) {
        return tradeRepo.findPendingForDate("PENDING", date);
        // 2 parameters, index-friendly, fast
    }

    // Good: chunked when IDs come from external system
    @Transactional(readOnly = true)
    public List<Trade> findFromExternalIds(List<Long> externalIds) {
        return tradeService.findByIds(externalIds);
        // Chunked into batches of 500
    }
}

Visual Overview

DIAGRAM

  Large IN clause problem:
  ┌─────────────────┐
  │  Application     │
  │  10,000 IDs      │
  └────────┬────────┘
           │  WHERE id IN (?,?,...x10000)
           ▼
  ┌─────────────────────────────────┐
  │  Database                         │
  │  1. Parse huge SQL       (slow)   │
  │  2. New plan (not cached) (slow)  │
  │  3. 10K index probes     (slow)   │
  │  4. Oracle: ORA-01795   (crash)   │
  └─────────────────────────────────┘

  Fix: Chunking (500 per batch):
  ┌─────────────────┐
  │  Application     │
  │  10,000 IDs      │
  └────────┬────────┘
           │  Split into 20 chunks of 500
           ▼
  ┌─────────────────────────────────┐
  │  Database                         │
  │  Chunk 1: IN (?,?...x500) (fast)  │
  │  Chunk 2: IN (?,?...x500) (fast)  │
  │  ...                              │
  │  Chunk 20: IN (?,?...x500)(fast)  │
  │  Plan cached and REUSED!          │
  └─────────────────────────────────┘

Key Takeaway

Large IN clauses with thousands of IDs cause five problems: huge SQL strings that are slow to parse, plan cache pollution because each different parameter count creates a different prepared statement, Oracle's hard limit of 1,000 bind parameters, poor optimizer decisions for very large IN lists, and unnecessary network overhead shipping thousands of IDs to the database.

The immediate fix is chunking: partition the ID list into batches of 500 and execute multiple queries. Each query is fast, the plan is cached and reused across batches, and the approach stays under all database limits. At National Bank of India, Rajesh's trade reconciliation service chunks 15,000 external IDs into 30 batches of 500, completing in 600ms compared to 12 seconds for the single query.

The best fix is redesigning the query entirely. Instead of "fetch these 15,000 specific IDs," Rajesh asks "fetch all PENDING trades for today's settlement date" – two parameters, index-friendly, 50ms. If you can express the filter as a WHERE condition the database evaluates, that always beats shipping an ID list from the application.

Remember one sentence: chunk large IN lists into batches of 500, but always prefer a server-side WHERE condition over shipping IDs from the application.

---