Slow database queries can feel like traffic jams in your application. Everything comes to a halt waiting for data to arrive. I’ve spent a lot of time tuning this layer, and the right techniques can transform a sluggish app into a responsive one. It often comes down to how you ask for data and manage the conversation with your database.

Let’s start with something fundamental: connection pooling. Think of a database connection as a phone line. Picking up the phone, dialing, and waiting for someone to answer takes time. If your app does this for every single request, it wastes enormous effort. A connection pool is like having a switchboard with lines already open and waiting.

You tell the switchboard you need a line, it hands you an open one, you make your call, and when you hang up, the line goes back to the pool for the next person. HikariCP is my go-to switchboard operator because it’s fast and efficient. Setting it up is straightforward.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/my_app");
config.setUsername("app_user");
config.setPassword("aSecurePassword123");
config.setMaximumPoolSize(15);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000); // 30 seconds
config.setIdleTimeout(600000);      // 10 minutes

HikariDataSource dataSource = new HikariDataSource(config);

The key is configuring the pool size. If you have too few connections, requests will queue up waiting for a free line. Too many, and you can overwhelm your database. I usually start with a pool size slightly larger than the number of application threads. The connectionTimeout is a safety net—if a connection isn’t available in time, it throws an error fast instead of leaving your user hanging.

Once you have an efficient way to get a connection, the next step is using it wisely. This brings us to prepared statements. Imagine you need to look up user profiles by email, a common task. A naive way is to build a new query string every time.

// This is inefficient and unsafe
String query = "SELECT * FROM users WHERE email = '" + userInput + "'";

Not only is this slow because the database must re-analyze the query structure each time, but it’s also dangerously vulnerable to SQL injection. A prepared statement fixes both issues. You create a template with placeholders.

String sql = "SELECT id, name, email FROM users WHERE email = ? AND account_active = true";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
    stmt.setString(1, userEmailAddress);
    ResultSet rs = stmt.executeQuery();
    // process results
}

The first time you run this, the database plans how to execute it. Every time after that, with a different email, it just reuses the plan. It’s like giving the database a fill-in-the-blank form. It’s faster and completely safe, as the user input is treated strictly as data, not executable code.

For those using JPA and Hibernate, a different set of challenges appears. The convenience of object mapping can hide inefficient queries. A classic issue is the “N+1 selects” problem. Let’s say you fetch a list of BlogPost entities, each with a collection of Comments.

If the mapping for Comments is lazy-loaded, your single query to get posts might trigger a separate query for each post’s comments as you iterate through the list. One initial query plus N follow-up queries. You can see this in your logs as a flurry of small selects.

The solution is to fetch the data you need in a controlled way. Use a JOIN FETCH in your JPQL to tell the persistence provider to get everything in one go.

@Query("SELECT p FROM BlogPost p JOIN FETCH p.comments WHERE p.publishDate > :date")
List<BlogPost> findRecentPostsWithComments(@Param("date") LocalDate date);

Another JPA tactic is using projections. Often, you don’t need the entire entity with all its relationships. You might just need two fields for a dropdown list. Fetching the full object graph is wasteful.

public interface UserNameOnly {
    String getFirstName();
    String getLastName();

    // This can be used in a Spring Data JPA query
    @Query("SELECT u.firstName as firstName, u.lastName as lastName FROM User u")
    List<UserNameOnly> findAllNames();
}

This query only pulls the two columns from the database. It’s faster and uses less memory. I make a habit of asking, “Do I really need the whole object?” More often than not, the answer is no.

When your application needs to insert or update a large batch of records, doing it one-by-one is painfully slow. Each statement involves a network round-trip. JDBC batching groups these operations.

Imagine logging system events. Without batching, every log entry is its own INSERT statement. With batching, you can send hundreds in one network packet.

// Disable auto-commit to control the transaction
connection.setAutoCommit(false);
String sql = "INSERT INTO audit_log (event_type, user_id, details, created_at) VALUES (?, ?, ?, ?)";

try (PreparedStatement stmt = connection.prepareStatement(sql)) {
    for (AuditEvent event : events) {
        stmt.setString(1, event.getType());
        stmt.setLong(2, event.getUserId());
        stmt.setString(3, event.getDetails());
        stmt.setTimestamp(4, Timestamp.from(event.getTimestamp()));
        stmt.addBatch(); // Add to the batch

        // Execute in chunks to avoid huge memory footprints
        if (++batchCount % 500 == 0) {
            stmt.executeBatch();
        }
    }
    // Execute any remaining records in the batch
    stmt.executeBatch();
    connection.commit(); // Commit all inserts at once
}

The optimal batch size isn’t a magic number. I’ve found values between 50 and 1000 work well, but it depends on your data and database. You need to test. Start with 100 and measure the improvement.

All these operations usually happen inside transactions. Transactions are essential for consistency, but they are like someone putting a “Do Not Disturb” sign on a piece of data. The longer that sign is up, the longer other parts of your app have to wait.

Keep your transactions short and focused. Do your non-database work first—validation, calculations—then open the transaction, do the database work, and close it immediately.

@Service
public class InventoryService {
    @Transactional
    public void adjustInventory(Long itemId, int delta) {
        // 1. READ: Fetch current state
        Item item = itemRepository.findByIdForUpdate(itemId); // Pessimistic lock
        // 2. CHECK & CALCULATE: Business logic
        if (item.getStock() + delta < 0) {
            throw new InsufficientStockException();
        }
        // 3. WRITE: Update database
        item.setStock(item.getStock() + delta);
        itemRepository.save(item);
        // Transaction ends here. The lock is released.
    }
}

Notice I didn’t send an email notification inside the @Transactional method. That kind of external call should happen after, so the database lock isn’t held while an email server responds. For read-only operations, always mark them as such: @Transactional(readOnly = true). This gives the database a hint it can avoid certain locking overhead.

Even the best-written query can be slow if the database has to check every single row to find what you need. This is called a full table scan. An index is like a book’s index. Instead of reading every page to find mentions of “connection pooling,” you look it up in the index which directs you to the right pages.

Creating an index is simple in SQL.

-- An index on a column frequently used in WHERE clauses
CREATE INDEX idx_customer_email ON customers(email);

-- A composite index for queries filtering on multiple columns
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

The order in a composite index matters. An index on (status, order_date) is great for finding orders by status, or by status and date. It’s not as helpful for finding orders by date alone. Indexes aren’t free. They speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index itself must be updated. I start by indexing the obvious foreign keys and fields in high-traffic query WHERE clauses, then let database monitoring guide me to the next candidates.

One of the most powerful tools is avoiding the database altogether for suitable data. If you have information that is read often but changes rarely, cache it. The first level is simple in-memory caches like Caffeine.

// A simple cache loader
LoadingCache<Long, Product> productCache = Caffeine.newBuilder()
    .maximumSize(10_000)
    .expireAfterWrite(30, TimeUnit.MINUTES)
    .refreshAfterWrite(10, TimeUnit.MINUTES)
    .build(key -> productRepository.findById(key).orElse(null));

// Usage
Product p = productCache.get(123L);

Spring’s cache abstraction makes this even cleaner.

@Service
public class ProductService {
    @Cacheable(value = "products", key = "#id")
    public Product getProductById(Long id) {
        // This method is only called if the cache misses
        return repository.findById(id).orElseThrow();
    }

    @CacheEvict(value = "products", key = "#product.id")
    public Product updateProduct(Product product) {
        // Evict old data from cache on update
        return repository.save(product);
    }
}

Caching introduces complexity around data freshness. You must decide when to evict or update cached entries. For data shared across multiple application instances, you need a distributed cache like Redis. The rule I follow is: cache at the highest possible level for the shortest acceptable time.

The techniques so far assume a blocking model. Your thread makes a request and waits for the database to respond. Reactive programming offers a different model using non-blocking drivers like R2DBC. Your thread makes a request and is freed up to do other work while the database processes it. When the response is ready, it’s handled.

This is excellent for applications with many concurrent, slow queries. It’s like ordering food at a busy counter. Instead of standing at the counter staring at the kitchen door (blocking), you take a pager and go sit down. You free up space at the counter for others.

// A reactive repository with Spring Data R2DBC
public interface UserRepository extends ReactiveCrudRepository<User, Long> {
    @Query("SELECT * FROM users WHERE region = :region")
    Flux<User> findByRegion(String region);
}

// Using it in a service
public Mono<UserDashboard> getDashboard(Long userId) {
    Mono<User> userMono = userRepository.findById(userId);
    Flux<Order> ordersFlux = orderRepository.findByUserId(userId);

    return Mono.zip(userMono, ordersFlux.collectList())
               .map(tuple -> new UserDashboard(tuple.getT1(), tuple.getT2()));
}

The Mono and Flux are promises of future data. This style requires thinking in terms of data streams and can significantly improve resource utilization in I/O-bound applications. It’s a paradigm shift, not just a library swap.

You can’t improve what you can’t measure. Monitoring is critical. I always integrate tools that let me see what SQL is actually being generated and how long it takes. For Hibernate, enabling SQL logging (spring.jpa.show-sql=true) is a start, but tools like datasource-proxy give more detail.

# Example logging configuration to see slow queries
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE
    com.zaxxer.hikari.HikariConfig: INFO
    com.zaxxer.hikari: TRACE

I also make heavy use of my database’s built-in tools. PostgreSQL has pg_stat_statements. MySQL has the slow query log and EXPLAIN command. Running EXPLAIN before a complex query shows the database’s plan—whether it’s using an index, doing a merge join, or a painful full scan. This insight is invaluable.

Finally, all these optimizations rest on a solid schema design. No amount of tuning will fix a fundamentally poor structure. Use the right data types. Store dates as DATE, numbers as NUMERIC or INT, not as VARCHAR. This allows proper comparisons and calculations.

Normalize your data to reduce duplication, but don’t be afraid to denormalize for performance. If you have a full_name column that is constantly derived from first_name and last_name, it might be worth storing it pre-computed. Partition massive tables, like event logs, by date range so queries only scan relevant chunks.

Most importantly, paginate. Never SELECT * from a table with millions of rows. Use limit-offset or, better yet, keyset pagination.

// Keyset pagination is more efficient than OFFSET for large datasets
@Query("SELECT o FROM Order o WHERE o.id > :lastSeenId ORDER BY o.id ASC")
List<Order> findNextPage(@Param("lastSeenId") Long lastSeenId, Pageable pageable);

Instead of asking for the 10,000th record (which requires counting 9,999 records), you ask for records where the ID is greater than the last one you saw. It’s consistently fast.

Improving database performance is an iterative process. You apply one technique, measure the impact, identify the new bottleneck, and address it. It requires patience and observation. Start with connection pooling and prepared statements—they are almost always beneficial. Then, use monitoring to guide your efforts towards the biggest time consumers in your specific application. The goal is to make your data layer a silent, efficient foundation, not the source of the traffic jam.