Why this matters
Executing individual SQL statements in a loop is inefficient. Using batch processing improves performance by reducing database communication overhead.
Ensure that batch processing is used instead of executing individual SQL statements inside loops for better database efficiency.
Executing individual SQL statements in a loop is inefficient. Using batch processing improves performance by reducing database communication overhead.
Side-by-side examples engineers can pattern-match during review.
public void execute(Connection connection) {
try {
Statement statement = connection.createStatement();
for (int i = 0; i < 10; i++) {
statement.execute("INSERT INTO myTable (column1, column2) VALUES (" + i + ", 'value" + i + "')"); // Noncompliant
}
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}public void execute(Connection connection) {
try {
Statement statement = connection.createStatement();
for (int i = 0; i < 10; i++) {
statement.addBatch("INSERT INTO myTable (column1, column2) VALUES (" + i + ", 'value" + i + "')"); // Compliant
}
statement.executeBatch();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}public void execute(Connection connection) {
try {
Statement statement = connection.createStatement();
for (int i = 0; i < 10; i++) {
statement.execute("INSERT INTO myTable (column1, column2) VALUES (" + i + ", 'value" + i + "')"); // Noncompliant
}
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}public void execute(Connection connection) {
try {
Statement statement = connection.createStatement();
for (int i = 0; i < 10; i++) {
statement.addBatch("INSERT INTO myTable (column1, column2) VALUES (" + i + ", 'value" + i + "')"); // Compliant
}
statement.executeBatch();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}From the same buckets as this rule.
Review SQL/database migrations for operations that can lock large tables or cause downtime. Examples: creating indexes without CONCURRENTLY (Postgres), ALTER COLUMN TYPE on big tables, adding NOT NULL without backfill, long-running updates without batching. Require an online migration strategy (CONCURRENTLY, backfill in batches, dual-write/expand-contract) and a rollback plan.