Date:

Share:

Using jOOQ’s DiagnosticsConnection to detect N+1 Queries – Java, SQL and jOOQ.

Related Articles

N + 1 queries are a popular problem in many applications running SQL queries. The problem can be easily described as follows:

  • One query is retrieved that retrieves a parent value
  • N queries are triggered that retrieve all individual child values

This problem is not limited to SQL, it can happen with any poorly designed API that does not allow batch processing and / or in bulk (even stored procedures). But with SQL, it’s especially painful, because in many cases, running tons of logic in one query will be entirely possible, especially with MULTISET and JOOQ’s MULTISET and SQL / XML or SQL / JSON.

In the worst case, the N + 1 problem is caused by a third party ORM – or rather, its poor implementation / configuration, but some ORMs make it very easy to shoot themselves in the foot with N + 1 problems …

example

Let’s stay with JDBC for now to illustrate how N + 1 queries occur.

try (Statement stmt = conn.createStatement()) {

    // The parent query, fetching actors
    try (ResultSet r1 = stmt.executeQuery(
        """
        SELECT actor_id, first_name, last_name
        FROM actor
        LIMIT 5
        """
    )) {
        while (r1.next()) 
            System.out.println();
            System.out.println(
                "Actor: " + r1.getString(2) + " " + r1.getString(2));

            // The child query, fetching films per actor
            try (PreparedStatement pstmt = conn.prepareStatement(
                """
                SELECT count(*) FROM film_actor WHERE actor_id = ?
                """
            )) 
                pstmt.setInt(1, r1.getInt(1));

                try (ResultSet r2 = pstmt.executeQuery()) 
                    while (r2.next()) 
                        System.out.println("Films: " + r2.getInt(1));
                    
                
            
        
    }
}

When running against Sakila’s database, The prints above:

Actor: PENELOPE PENELOPE
Films: 19

Actor: NICK NICK
Films: 25

Actor: ED ED
Films: 22

Actor: JENNIFER JENNIFER
Films: 22

Actor: JOHNNY JOHNNY
Films: 29

Obviously this is true, but we could easily run it in one query:

SELECT
  a.first_name,
  a.last_name,
  count(fa.film_id)
FROM actor AS a
LEFT JOIN film_actor AS fa USING (actor_id)
GROUP BY a.actor_id

Given that we have 200 players in total, what do you prefer? Running 1 + 200 queries or just one query? If you control your SQL, the chances of this error occurring are much less, but what if you’re not in control (complete), since SQL is created based on enthusiastic / lazy load configurations and complex graph entity comments, then you’ll be glad you can just Connect jOOQ devices Diagnosis of repeated DiagnosticsConnection statements Into your integration test environment (Not necessarily in production, because there is some overhead to analyze and normalize all SQL strings).

Applied to the JDBC example above:

DSLContext ctx = DSL.using(connection);
ctx.configuration().set(new DefaultDiagnosticsListener() 
    @Override
    public void repeatedStatements(DiagnosticsContext c) 

        // Custom callback, may also throw exceptions, etc.
        System.out.println(
            "Repeated statement: " + c.normalisedStatement());
    
);

Connection conn = ctx.diagnosticsConnection();

You now get the following output:

Actor: PENELOPE PENELOPE
Films: 19

Actor: NICK NICK
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 25

Actor: ED ED
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 22

Actor: JENNIFER JENNIFER
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 22

Actor: JOHNNY JOHNNY
Repeated statement: select count(*) from film_actor where actor_id = ?;
Films: 29

As you can see, the diagnostic connection starts to register after the first repetition of the statement, the assumption is that within a transaction, it is usually unnecessary to repeat a statement more than once, because there is almost always a better way.

Use it with JPA / Hibernate

You probably do not write JDBC statements manually like that, but it does not matter who calls JDBC (you, jOOQ, JdbcTemplate, Hibernate, etc.). If you are serving your connection (or DataSource) With jOOQ’s DiagnosticsConnection or DiagnosticsDataSource, So you can intercept such events easily, regardless of the reason.

Future versions of jOOQ will add much more diagnostics using https://github.com/jOOQ/jOOQ/issues/7527.

To see what’s already available on jOOQ, Refer to the guide.

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Popular Articles