Consecutive aggregation
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SPI method handling this event is consecutiveAggregation()
Multiple consecutive aggregate queries that are similar (same FROM clause), but use different aggregate functions each, and different WHERE clauses might be unified into a single query aggregating everything in one go.
Why is it bad?
Instead of running N queries, it is possible to run just 1 query.
An example is given here:
// A custom DiagnosticsListener SPI implementation
class ConsecutiveAggregation implements DiagnosticsListener {
@Override
public void consecutiveAggregation(DiagnosticsContext ctx) {
// The statement that is being executed and which has similar aggregate queries.
System.out.println("Actual statement: " + ctx.actualStatement());
// A normalised version of the actual statement, which is shared by all duplicates
// This statement has its SELECT and WHERE clause removed.
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 ConsecutiveAggregation()))
.diagnosticsConnection();
Statement s = c.createStatement()
) {
try (ResultSet a = s.executeQuery("SELECT count(*) FROM author")) {
while (a.next())
println(a.getInt(1));
}
// This query could be merged into the previous one e.g.:
// SELECT count(*), count(*) FILTER (WHERE last_name LIKE 'A%') FROM author
try (ResultSet a = s.executeQuery("SELECT count(*) FROM author WHERE last_name LIKE 'A%'")) {
while (a.next())
println(a.getInt(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!