Java Database Performance: Profiling and Optimization
Database performance is often the single most critical factor determining the overall responsiveness and scalability of enterprise Java applications. While developers spend countless hours optimizing application code, the database layer frequently becomes a silent bottleneck that goes unnoticed until production traffic exposes its weaknesses. This tutorial provides a complete, practical guide to profiling and optimizing database interactions in Java applications—covering everything from identifying slow queries to tuning connection pools, batch processing, and fetch strategies.
Understanding Database Performance Profiling
🚀 Deploy your AI agent in 10 minutes
Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.
Try it free →What Is Database Profiling?
Database profiling is the systematic process of measuring, analyzing, and understanding how your Java application interacts with the database. It involves capturing metrics such as query execution time, connection acquisition latency, statement preparation overhead, result set fetching costs, and transaction commit durations. Profiling reveals the exact SQL statements being executed, their frequency, their execution plans, and the time spent at each stage of the database interaction lifecycle.
In the Java ecosystem, profiling can be performed at multiple levels:
- JDBC level – Intercepting calls to the JDBC API to measure statement execution, connection management, and result set processing
- ORM level – Monitoring Hibernate, JPA, or other ORM frameworks to understand entity loading, dirty checking, and flush operations
- Database server level – Using database-native tools to examine query plans, lock contention, and resource utilization
- Network level – Analyzing the data transfer between application and database, including serialization overhead
Why It Matters
Without profiling, optimization is guesswork. Developers may spend time refactoring code that is not actually a bottleneck while the real performance killers remain hidden. Profiling provides the empirical evidence needed to:
- Identify the slowest queries that dominate response times
- Detect N+1 query problems where ORMs generate hundreds of unnecessary queries
- Uncover connection pool exhaustion that causes thread blocking
- Find missing indexes that force full table scans
- Measure the impact of fetch size on memory and latency
- Validate optimization efforts with before-and-after comparisons
Common Performance Bottlenecks in Java Database Applications
Before diving into profiling techniques, it is essential to understand the typical bottlenecks that plague Java database applications. Recognizing these patterns accelerates diagnosis:
- Connection pool contention – When all connections are in use, threads block waiting for a connection, causing cascading latency
- Unoptimized SQL queries – Missing WHERE clauses, missing JOIN conditions, or queries that fetch unnecessary columns
- Missing or stale database indexes – The query planner resorts to sequential scans when appropriate indexes do not exist
- Excessive round trips – Executing many small queries instead of batching or using JOINs
- Large result sets without pagination – Fetching millions of rows into memory when only a page is needed
- Improper transaction boundaries – Holding transactions open too long, acquiring locks unnecessarily
- ORM-induced inefficiencies – Lazy loading triggering N+1 queries, dirty checking scanning thousands of entities
- Statement recompilation overhead – Not using PreparedStatement caching, forcing the database to re-parse SQL repeatedly
Profiling JDBC Operations: Techniques and Tools
1. Manual Timing with System.nanoTime()
The simplest profiling approach requires no external libraries. By wrapping JDBC calls with high-resolution timing, you can log the duration of every database operation. This technique is ideal for development environments and lightweight production diagnostics when configured with a sampling rate.
import java.sql.*;
import java.util.concurrent.TimeUnit;
public class TimedJdbcProfiler {
private static final long SLOW_QUERY_THRESHOLD_MS = 100;
public static List<User> findUsersByStatus(Connection conn, String status)
throws SQLException {
long start = System.nanoTime();
String sql = "SELECT id, username, email, created_at FROM users WHERE status = ?";
PreparedStatement stmt = null;
ResultSet rs = null;
List<User> users = new ArrayList<>();
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, status);
long queryStart = System.nanoTime();
rs = stmt.executeQuery();
long queryEnd = System.nanoTime();
while (rs.next()) {
User user = new User();
user.setId(rs.getLong("id"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
user.setCreatedAt(rs.getTimestamp("created_at"));
users.add(user);
}
long end = System.nanoTime();
long totalMs = TimeUnit.NANOSECONDS.toMillis(end - start);
long queryMs = TimeUnit.NANOSECONDS.toMillis(queryEnd - queryStart);
long fetchMs = TimeUnit.NANOSECONDS.toMillis(end - queryEnd);
if (totalMs > SLOW_QUERY_THRESHOLD_MS) {
System.err.printf("[SLOW QUERY] SQL: %s | Params: [%s] | " +
"Query: %dms | Fetch: %dms | Total: %dms | Rows: %d%n",
sql, status, queryMs, fetchMs, totalMs, users.size());
}
return users;
} finally {
closeQuietly(rs);
closeQuietly(stmt);
}
}
private static void closeQuietly(AutoCloseable resource) {
if (resource != null) {
try { resource.close(); } catch (Exception e) { /* log */ }
}
}
}This manual approach works well for targeted profiling but becomes unwieldy when applied across an entire codebase. For comprehensive coverage, consider the following techniques.
2. Using JDBC Wrappers for Profiling
A more systematic method involves creating a proxy driver or wrapper that intercepts all JDBC calls. The wrapper delegates to the real driver while recording metrics. This approach is non-invasive—your application code remains unchanged, and profiling can be enabled or disabled via configuration.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class ProfilingConnection implements Connection {
private final Connection delegate;
private final ProfilingListener listener;
public ProfilingConnection(Connection delegate, ProfilingListener listener) {
this.delegate = delegate;
this.listener = listener;
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return new ProfilingPreparedStatement(
delegate.prepareStatement(sql), sql, listener);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException {
return new ProfilingPreparedStatement(
delegate.prepareStatement(sql, autoGeneratedKeys), sql, listener);
}
// Delegate all other Connection methods to 'delegate'...
@Override
public void commit() throws SQLException {
long start = System.nanoTime();
delegate.commit();
listener.onCommit(System.nanoTime() - start);
}
@Override
public Statement createStatement() throws SQLException {
Statement stmt = delegate.createStatement();
return new ProfilingStatement(stmt, listener);
}
@Override
public void close() throws SQLException { delegate.close(); }
// ... remaining Connection methods omitted for brevity
}
class ProfilingPreparedStatement implements PreparedStatement {
private final PreparedStatement delegate;
private final String sql;
private final ProfilingListener listener;
private final List<Object> parameters = new ArrayList<>();
ProfilingPreparedStatement(PreparedStatement delegate, String sql,
ProfilingListener listener) {
this.delegate = delegate;
this.sql = sql;
this.listener = listener;
}
@Override
public ResultSet executeQuery() throws SQLException {
long start = System.nanoTime();
ResultSet rs = delegate.executeQuery();
long elapsed = System.nanoTime() - start;
listener.onQuery(sql, parameters, elapsed);
return new ProfilingResultSet(rs, listener);
}
@Override
public int executeUpdate() throws SQLException {
long start = System.nanoTime();
int rows = delegate.executeUpdate();
listener.onUpdate(sql, parameters, System.nanoTime() - start, rows);
return rows;
}
@Override
public void setString(int parameterIndex, String x) throws SQLException {
delegate.setString(parameterIndex, x);
parameters.add("param[" + parameterIndex + "]=" + x);
}
@Override
public void setInt(int parameterIndex, int x) throws SQLException {
delegate.setInt(parameterIndex, x);
parameters.add("param[" + parameterIndex + "]=" + x);
}
@Override
public void setLong(int parameterIndex, long x) throws SQLException {
delegate.setLong(parameterIndex, x);
parameters.add("param[" + parameterIndex + "]=" + x);
}
// Delegate remaining PreparedStatement methods...
@Override
public void close() throws SQLException { delegate.close(); }
}
interface ProfilingListener {
void onQuery(String sql, List<Object> params, long elapsedNanos);
void onUpdate(String sql, List<Object> params, long elapsedNanos, int rowsAffected);
void onCommit(long elapsedNanos);
}This wrapper pattern is the foundation of many production-grade profiling libraries. The listener interface can be implemented to log metrics, send them to a monitoring system, or aggregate them into a dashboard.
3. Using a Dedicated Profiling Library: P6Spy Integration
Rather than building your own wrapper, you can leverage P6Spy—a mature, open-source JDBC proxy driver designed specifically for profiling. P6Spy intercepts every JDBC call and logs the exact SQL with bind parameters, execution time, and row counts. It requires no code changes; you simply configure your datasource to use the P6Spy driver.
// Step 1: Add P6Spy dependency to pom.xml
// <dependency>
// <groupId>p6spy</groupId>
// <artifactId>p6spy</artifactId>
// <version>3.9.1</version>
// </dependency>
// Step 2: Configure spy.properties file
// driverlist=org.postgresql.Driver
// logfile=/var/log/p6spy.log
// dateformat=yyyy-MM-dd HH:mm:ss.SSS
// logMessageFormat=p6spy.commonslogging.P6SpyLogger
// append=true
// logSQL=true
// logOnlyOnError=false
// slowLogThreshold=50
// slowLogThresholdMsec=50
// Step 3: Modify your datasource configuration
// Instead of: jdbc:postgresql://localhost:5432/mydb
// Use: jdbc:p6spy:postgresql://localhost:5432/mydb
// Example HikariCP configuration with P6Spy:
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:p6spy:postgresql://localhost:5432/mydb");
config.setDriverClassName("com.p6spy.engine.P6SpyDriver");
config.setUsername("app_user");
config.setPassword("secure_password");
config.setMaximumPoolSize(20);
HikariDataSource dataSource = new HikariDataSource(config);
// Your application code remains completely unchanged:
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM orders WHERE customer_id = ? AND status = ?")) {
stmt.setLong(1, 10042L);
stmt.setString(2, "SHIPPED");
ResultSet rs = stmt.executeQuery();
// Process results normally...
} catch (SQLException e) {
// Handle exception
}
// P6Spy automatically logs to the configured file:
// 2024-01-15 14:32:18.245 | SELECT * FROM orders WHERE customer_id = ? AND status = ?
// 2024-01-15 14:32:18.245 | bind: [10042, SHIPPED]
// 2024-01-15 14:32:18.312 | execution time: 67ms | rows: 12P6Spy excels in development and staging environments. For production, consider sampling-based profilers or APM tools that impose lower overhead. The library also supports filtering to exclude trivial queries and a configurable slow query threshold that triggers additional logging.
4. Connection Pool Metrics and Monitoring
Connection pool behavior directly impacts application throughput. Pools like HikariCP expose rich metrics that reveal contention, leak suspects, and connection lifecycle timing. Monitoring these metrics is essential for capacity planning and detecting pool exhaustion.
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.metrics.MetricsTrackerFactory;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
public class ConnectionPoolMonitor {
public static void main(String[] args) {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("app_user");
config.setPassword("secure_password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000); // 30 seconds
config.setIdleTimeout(600000); // 10 minutes
config.setLeakDetectionThreshold(10000); // 10 seconds
HikariDataSource ds = new HikariDataSource(config);
// Schedule periodic metric reporting
ScheduledExecutorService scheduler =
Executors.newSingleThreadScheduledExecutor();
scheduler.scheduleAtFixedRate(() -> {
var poolMetrics = ds.getHikariPoolMXBean();
if (poolMetrics != null) {
System.out.printf("[POOL METRICS] Active: %d | Idle: %d | " +
"Waiting: %d | Total: %d | Pending: %d%n",
poolMetrics.getActiveConnections(),
poolMetrics.getIdleConnections(),
poolMetrics.getThreadsAwaitingConnection(),
poolMetrics.getTotalConnections(),
poolMetrics.getThreadsAwaitingConnection()
);
// Alert on high contention
int waiting = poolMetrics.getThreadsAwaitingConnection();
if (waiting > 5) {
System.err.printf("[WARNING] Connection pool contention: " +
"%d threads waiting for a connection!%n", waiting);
}
}
}, 0, 15, TimeUnit.SECONDS);
// Use the datasource for application queries...
// scheduler.shutdown() on application shutdown
}
}Key metrics to track include active connections, idle connections, threads awaiting connections, connection creation rate, and connection timeout occurrences. When threads awaiting connections consistently exceeds zero, either the pool size is insufficient or queries are holding connections too long.
Optimization Strategies
Connection Pool Optimization
A properly tuned connection pool is the foundation of database performance. The goal is to minimize connection creation overhead while preventing resource exhaustion. Here are the critical configuration parameters and their rationale:
HikariConfig config = new HikariConfig();
// Pool sizing: The formula is (core_count * 2) + effective_spindle_count
// For a server with 8 cores and a database with SSD (effective_spindle_count ≈ 1):
// pool size ≈ (8 * 2) + 1 = 17
// However, always benchmark—start with 10 and increase gradually
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
// Connection timeout: How long a thread waits before throwing an exception
// Too short: spurious failures under load spikes
// Too long: threads hang indefinitely during outages
// Recommended: 30 seconds for most applications
config.setConnectionTimeout(30000);
// Idle timeout: How long an idle connection survives before being closed
// Must be LESS than the database's connection idle timeout
// PostgreSQL default: 10 minutes; set pool idle timeout to 8-9 minutes
config.setIdleTimeout(540000); // 9 minutes
// Max lifetime: Hard limit on connection lifespan
// Must be LESS than database infrastructure timeouts (load balancers, proxies)
// Typical: 30 minutes
config.setMaxLifetime(1800000);
// Leak detection: Logs a warning if a connection is held longer than threshold
// Helps identify code that forgets to close connections
// Set to slightly above your longest expected query
config.setLeakDetectionThreshold(15000); // 15 seconds
// Validation: Ensures connections are alive before handing them out
// Use a lightweight query like "SELECT 1" or set validation timeout
config.setConnectionTestQuery("SELECT 1");
config.setValidationTimeout(3000); // 3 seconds
HikariDataSource optimizedDataSource = new HikariDataSource(config);Pool sizing formula in detail: The widely cited formula connections = (core_count * 2) + effective_spindle_count originates from the PostgreSQL community and accounts for the fact that each CPU core can handle roughly two concurrent database connections (one active, one waiting for I/O). The effective_spindle_count accounts for disk parallelism—1 for SSDs, higher for spinning disks in RAID configurations. However, this formula provides a starting point only. Always conduct load testing to determine the optimal size for your specific workload. A pool that is too large wastes database resources and increases context switching; a pool that is too small causes thread blocking and reduced throughput.
PreparedStatement and Query Caching
Every time the database receives a SQL string, it must parse, optimize, and plan execution. PreparedStatements allow the database to cache the execution plan, dramatically reducing overhead for frequently executed queries. In Java, you must reuse PreparedStatement instances (or enable driver-level caching) to benefit from this optimization.
import java.sql.*;
import java.util.concurrent.ConcurrentHashMap;
public class PreparedStatementCache {
// Simple LRU-like cache for PreparedStatements
private static final ConcurrentHashMap<String, PreparedStatement>
statementCache = new ConcurrentHashMap<>();
public static PreparedStatement getCachedStatement(Connection conn,
String sql) throws SQLException {
PreparedStatement cached = statementCache.get(sql);
if (cached != null && !cached.isClosed()) {
// Clear parameters from previous use
cached.clearParameters();
return cached;
}
// Prepare new statement and cache it
PreparedStatement newStmt = conn.prepareStatement(sql);
statementCache.put(sql, newStmt);
return newStmt;
}
// Usage example with batch processing
public void insertOrders(Connection conn, List<Order> orders)
throws SQLException {
String sql = "INSERT INTO orders (customer_id, total, status) " +
"VALUES (?, ?, ?)";
PreparedStatement stmt = getCachedStatement(conn, sql);
for (Order order : orders) {
stmt.setLong(1, order.getCustomerId());
stmt.setBigDecimal(2, order.getTotal());
stmt.setString(3, order.getStatus());
stmt.addBatch();
}
int[] results = stmt.executeBatch();
System.out.printf("Inserted %d orders in batch%n", results.length);
// Do NOT close the cached statement—it stays in the cache
}
// Periodic cleanup of closed statements
public static void evictClosedStatements() {
statementCache.entrySet().removeIf(entry -> {
try {
return entry.getValue().isClosed();
} catch (SQLException e) {
return true; // Evict on error
}
});
}
}Many JDBC drivers support statement caching natively. For PostgreSQL, the pg_prepared_statements driver parameter controls this. For MySQL, the cachePrepStmts property enables client-side caching. Always consult your driver documentation for the recommended caching configuration.
Batch Processing Optimization
Executing multiple INSERT, UPDATE, or DELETE statements individually incurs a network round trip for each execution. Batching groups these operations into a single round trip, dramatically reducing latency for bulk operations. The performance difference is often 10x to 50x.
import java.sql.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
public class BatchProcessingExample {
// BAD: Individual inserts — one round trip per row
public void insertOrdersIndividually(Connection conn, List<Order> orders)
throws SQLException {
String sql = "INSERT INTO orders (customer_id, total, status, created_at) " +
"VALUES (?, ?, ?, ?)";
long start = System.nanoTime();
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
for (Order order : orders) {
stmt.setLong(1, order.getCustomerId());
stmt.setBigDecimal(2, order.getTotal());
stmt.setString(3, order.getStatus());
stmt.setTimestamp(4, Timestamp.valueOf(order.getCreatedAt()));
stmt.executeUpdate(); // Separate round trip per iteration
}
}
long elapsed = System.nanoTime() - start;
System.out.printf("Individual inserts: %d rows in %d ms%n",
orders.size(), elapsed / 1_000_000);
}
// GOOD: Batch insert — single round trip
public void insertOrdersBatched(Connection conn, List<Order> orders)
throws SQLException {
String sql = "INSERT INTO orders (customer_id, total, status, created_at) " +
"VALUES (?, ?, ?, ?)";
long start = System.nanoTime();
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
int batchSize = 1000; // Optimal batch size varies by database
int count = 0;
for (Order order : orders) {
stmt.setLong(1, order.getCustomerId());
stmt.setBigDecimal(2, order.getTotal());
stmt.setString(3, order.getStatus());
stmt.setTimestamp(4, Timestamp.valueOf(order.getCreatedAt()));
stmt.addBatch();
count++;
// Execute batch when buffer reaches threshold
if (count % batchSize == 0) {
int[] results = stmt.executeBatch();
stmt.clearBatch();
System.out.printf("Flushed batch of %d rows%n", results.length);
}
}
// Execute remaining rows
if (count % batchSize != 0) {
int[] results = stmt.executeBatch();
System.out.printf("Flushed final batch of %d rows%n", results.length);
}
}
long elapsed = System.nanoTime() - start;
System.out.printf("Batch inserts: %d rows in %d ms%n",
orders.size(), elapsed / 1_000_000);
}
// BEST: Batch insert with transaction control and generated keys
public void insertOrdersTransactional(Connection conn, List<Order> orders)
throws SQLException {
String sql = "INSERT INTO orders (customer_id, total, status, created_at) " +
"VALUES (?, ?, ?, ?)";
boolean originalAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
long start = System.nanoTime();
try (PreparedStatement stmt = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
int batchSize = 500;
int count = 0;
for (Order order : orders) {
stmt.setLong(1, order.getCustomerId());
stmt.setBigDecimal(2, order.getTotal());
stmt.setString(3, order.getStatus());
stmt.setTimestamp(4, Timestamp.valueOf(order.getCreatedAt()));
stmt.addBatch();
count++;
if (count % batchSize == 0) {
stmt.executeBatch();
// Retrieve generated keys if needed
ResultSet keys = stmt.getGeneratedKeys();
int index = 0;
while (keys.next()) {
orders.get((count - batchSize) + index)
.setId(keys.getLong(1));
index++;
}
keys.close();
stmt.clearBatch();
}
}
if (count % batchSize != 0) {
stmt.executeBatch();
ResultSet keys = stmt.getGeneratedKeys();
int index = 0;
int baseIndex = (count / batchSize) * batchSize;
while (keys.next()) {
orders.get(baseIndex + index).setId(keys.getLong(1));
index++;
}
keys.close();
}
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(originalAutoCommit);
}
long elapsed = System.nanoTime() - start;
System.out.printf("Transactional batch: %d rows in %d ms%n",
orders.size(), elapsed / 1_000_000);
}
}Batch size tuning: The optimal batch size depends on the database, network latency, and row size. Start with 500-1000 rows per batch and adjust based on profiling results. Too small a batch size wastes the potential of batching; too large a batch size can cause memory pressure, long-held locks, and transaction log bloat. Many databases also impose limits—MySQL's max_allowed_packet and PostgreSQL's work_mem influence the maximum practical batch size.
Fetch Size Tuning
By default, many JDBC drivers fetch the entire result set into memory before returning control to the application. For large result sets, this causes excessive memory consumption and garbage collection pressure. Configuring the fetch size enables incremental retrieval, where the driver fetches rows in chunks.
import java.sql.*;
public class FetchSizeOptimization {
// BAD: Default fetch size — entire result set loaded at once
public void processAllOrdersDefaultFetch(Connection conn) throws SQLException {
String sql = "SELECT id, customer_id, total, status, items " +
"FROM orders WHERE created_at > ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setTimestamp(1, Timestamp.valueOf("2024-01-01 00:00:00"));
// Default fetch size — driver dependent, often 0 (all rows)
ResultSet rs = stmt.executeQuery();
// All 500,000 rows loaded into driver memory at once
while (rs.next()) {
processOrder(rs);
}
}
// Memory spike: 500,000 rows × row size ≈ hundreds of MB
}
// GOOD: Controlled fetch size with setFetchSize()
public void processAllOrdersOptimized(Connection conn) throws SQLException {
String sql = "SELECT id, customer_id, total, status, items " +
"FROM orders WHERE created_at > ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setTimestamp(1, Timestamp.valueOf("2024-01-01 00:00:00"));
// Fetch 200 rows at a time — balance between round trips and memory
stmt.setFetchSize(200);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
processOrder(rs);
}
}
// Memory: ~200 rows in driver buffer at any time
// Trade-off: more network round trips but lower memory pressure
}
// BEST: Streaming with fetch size and server-side cursor (PostgreSQL)
public void streamLargeResultSet(Connection conn) throws SQLException {
// PostgreSQL requires auto-commit OFF for server-side cursors
boolean originalAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
String sql = "SELECT id, customer_id, total, status, items " +
"FROM orders WHERE created_at > ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setTimestamp(1, Timestamp.valueOf("2024-01-01 00:00:00"));
// For PostgreSQL: setFetchSize > 0 AND auto-commit false
// enables a server-side cursor that streams results
stmt.setFetchSize(100);
ResultSet rs = stmt.executeQuery();
int processedCount = 0;
while (rs.next()) {
processOrder(rs);
processedCount++;
// Commit periodically to release server-side resources
if (processedCount % 10000 == 0) {
conn.commit();
System.out.printf("Checkpoint: processed %d rows%n",
processedCount);
}
}
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(originalAutoCommit);
}
}
private void processOrder(ResultSet rs) throws SQLException {
// Business logic processing of a single order
long id = rs.getLong("id");
BigDecimal total = rs.getBigDecimal("total");
// ... processing logic
}
}Fetch size considerations by database: PostgreSQL requires auto-commit to be disabled for server-side cursors to be used with setFetchSize. MySQL honors fetchSize only when useCursorFetch=true is specified in the connection URL. Oracle JDBC driver defaults to fetch size 10, which is often too small for analytical workloads. Always profile memory usage and query latency when tuning fetch size—a smaller fetch size increases round trips but reduces memory, while a larger fetch size does the opposite. The sweet spot typically lies between 100 and 500 rows for transactional applications and 1000-5000 rows for analytical queries.
SQL Query Optimization Techniques
Even with perfect Java-side optimizations, a poorly written SQL query will dominate performance. Profiling reveals the problematic queries; the next step is optimizing them. Here are techniques you can apply directly from your Java application:
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class QueryOptimizationExamples {
// BEFORE: N+1 query problem — one query to fetch orders,
// then one query per order to fetch line items
// This is the single most common ORM performance killer
public List<Order> getOrdersWithItemsNPlusOne(Connection conn,
Long customerId) throws SQLException {
List<Order> orders = new ArrayList<>();
// Query 1: Fetch orders
String orderSql = "SELECT id, total, status FROM orders " +
"WHERE customer_id = ?";
try (PreparedStatement stmt = conn.prepareStatement(orderSql)) {
stmt.setLong(1, customerId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Order order = new Order();
order.setId(rs.getLong("id"));
order.setTotal(rs.getBigDecimal("total"));
order.setStatus(rs.getString("status"));
// Query 2..N+1: Fetch items for EACH order — TERRIBLE!
String itemSql = "SELECT product_id, quantity, price " +
"FROM order_items WHERE order_id = ?";
try (PreparedStatement itemStmt = conn.p