Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
Repeated statements
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SPI method handling this event is repeatedStatements()
Sometimes, there is no other option than to repeat an identical (or similar, see duplicate statements) statement many times in a row, but often, it is a sign that your queries can be rewritten and your repeated statements should really be joined to a larger query.
Why is it bad?
This problem is usually referred to as the N+1 problem. A parent entity is loaded (often by an ORM), and its child entities are loaded lazily. Unfortunately, there were several parent instances, so for each parent instance, we're now loading a set of child instances, resulting in many many queries. This diagnostic detects if on the same connection, there is repeated execution of the same statement, even if it is not exactly identical.
An example is given here:
// A custom DiagnosticsListener SPI implementation
class RepeatedStatement extends DefaultDiagnosticsListener {
@Override
public void repeatedStatements(DiagnosticsContext ctx) {
// The statement that is being executed and which has duplicates
System.out.println("Actual statement: " + ctx.actualStatement());
// A normalised version of the actual statement, which is shared by all duplicates
// This statement has "normal" whitespace, bind variables, IN-lists, etc.
System.out.println("Normalised statement: " + ctx.normalisedStatement());
// All the duplicate actual statements that have produced the same normalised
// statement in the recent past.
System.out.println("Repeated statements: " + ctx.repeatedStatements());
}
}
And then:
// Configuration is configured with the target DataSource, SQLDialect, etc. for instance Oracle.
try (Connection c = DSL.using(configuration.derive(new RepeatedStatement()))
.diagnosticsConnection();
Statement s1 = c.createStatement();
ResultSet a = s1.executeQuery("SELECT id FROM author WHERE first_name LIKE 'A%'")) {
while (a.next()) {
int id = a.getInt(1);
// This query is run once for every author, when we could have joined the author table
try (PreparedStatement s2 = c.prepareStatement("SELECT title FROM book WHERE author_id = ?")) {
s2.setInt(1, id);
try (ResultSet b = s2.executeQuery()) {
while (b.next())
System.out.println("ID: " + id + ", title: " + b.getString(1));
}
}
}
}
Unlike when detecting duplicate statements, repeated statement statistics are performed locally only, for a single JDBC Connection, or, if possible, for a transaction. Repeated statements in different transactions are usually not an indication of a problem.
Feedback
Do you have any feedback about this page? We'd love to hear it!