Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why doesn't the n+1 problem occur in the constructor passing entity fields?

I checked the behavior of SQL when we want to have values mapped immediately from an Entity to a DTO object, for example. If I gave an example in the constructor:

SELECT new Test(T) FROM Test T

I saw 2 queries in the logs.
On the other hand, if I did:

SELECT new TEST(T.name, T.city) FROM TEST T

I saw only one query in the logs.

Why?

Tags:

2

Answers


  1. 0 
    SELECT new Test(T) FROM Test T

    The first results in one query to fetch references and N queries to fetch properties for each entity due to lazy loading (N+1).

    SELECT new TEST(T.name, T.city) FROM TEST T

    The second retrieves all data in a single query due to eager loading. By specifying the exact fields, you allow JPA to optimize the query.

    Login or Signup to reply.
  2. 0

    This is defined by the JPA specification. Anytime you use ‘.’ over a relationship in JPA, you are performing an inner join (unless using it to define an outer join in a From clause). Since you are selecting t.city, you are forcing JPA to join to the City entity and include it in the results – so there is no need for it to issue yet another query to fetch that same data.

    When you select just T, you have not specified how to fetch city at all, leaving the provider to do its thing and build you a complete Test entity (which you wrap in a constructor). This ends up with N+1 queries – but doesn’t have to, as there are many other ways to fetch relationships like batching or even eager fetching that might be more appropriate.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search