Interview question

What is batch processing in JDBC and how does it improve performance? JDBC में batch processing क्या है और यह performance कैसे बेहतर बनाता है?

Answer

Batch processing groups multiple SQL statements together and sends them to the database in a SINGLE round trip, dramatically reducing network overhead compared to executing statements one at a time.

// WITHOUT batching - one round trip PER insert, very slow for bulk data
PreparedStatement pstmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');
for (User user : userList) {
    pstmt.setString(1, user.getName());
    pstmt.setString(2, user.getEmail());
    pstmt.executeUpdate();  // network round trip for EVERY single row
}

// WITH batching - all statements sent together in one round trip
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);  // batches typically combined with transactions

PreparedStatement batchStmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');

for (User user : userList) {
    batchStmt.setString(1, user.getName());
    batchStmt.setString(2, user.getEmail());
    batchStmt.addBatch();  // queue this set of parameters, don't execute yet
}

int[] results = batchStmt.executeBatch();  // executes ALL queued statements together
conn.commit();

System.out.println('Inserted ' + results.length + ' rows');

// For very large batches, execute in chunks to avoid excessive memory use
int batchSize = 1000;
int count = 0;
for (User user : userList) {
    batchStmt.setString(1, user.getName());
    batchStmt.setString(2, user.getEmail());
    batchStmt.addBatch();
    if (++count % batchSize == 0) {
        batchStmt.executeBatch();  // flush periodically
    }
}
batchStmt.executeBatch();  // flush any remaining statements
conn.commit();

// Performance impact: batching 10,000 inserts can be 10-50x faster
// than executing them individually, due to reduced network round trips

Batch processing कई SQL statements को साथ group करके एक ही round trip में database को भेजता है, network overhead काफ़ी कम करता है।

// Batching के बिना - हर insert पर round trip, बहुत धीमा
PreparedStatement pstmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');
for (User user : userList) {
    pstmt.setString(1, user.getName());
    pstmt.setString(2, user.getEmail());
    pstmt.executeUpdate();  // हर row पर network round trip
}

// Batching के साथ - सारे statements एक साथ
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);

PreparedStatement batchStmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');

for (User user : userList) {
    batchStmt.setString(1, user.getName());
    batchStmt.setString(2, user.getEmail());
    batchStmt.addBatch();
}

int[] results = batchStmt.executeBatch();
conn.commit();

System.out.println('Inserted ' + results.length + ' rows');

// बड़े batches के लिए chunks में execute करें
int batchSize = 1000;
int count = 0;
for (User user : userList) {
    batchStmt.setString(1, user.getName());
    batchStmt.setString(2, user.getEmail());
    batchStmt.addBatch();
    if (++count % batchSize == 0) {
        batchStmt.executeBatch();
    }
}
batchStmt.executeBatch();
conn.commit();

Was this answer clear?

Subjects

Related questions