How can i resolve the N+1 Selects problem ?

Question

Hello everyone,

I have trouble understanding how to avoid the n+1 select in jpa or hibernate.

From what i read, there's the 'left join fetch', but i'm not sure if it still works with more than one list (oneToMany)..

Could someone explain it to me, or give me a link with a clear complete explanation please ?

I'm sorry if this is a noob question, but i can't find a real clear article or doc on this issue.

Thanks

Answer 1

The NHProf website gives a good explanation.

HTH,
Kent

Answer 2

Apart from the join, you can also use subselect(s). This results in 2 queries being executed (or in general m + 1, if you have m lists), but it scales well for a large number of lists too, unlike join fetching.

With join fetching, if you fetch 2 tables (or lists) with your entity, you get a cartesian product, i.e. all combinations of pairs of rows from the two tables. If the tables are large, the result can be huge, e.g. if both tables have 1000 rows, the cartesian product contains 1 million rows!

A better alternative for such cases is to use subselects. In this case, you would issue 2 selects - one for each table - on top of the main select (which loads the parent entity), so altogether you load 1 + 100 + 100 rows with 3 queries.

For the record, the same with lazy loading would result in 201 separate selects, each loading a single row.

Update: here are some examples:

• a tutorial: Tuning Lazy Fetching, with a section on subselects towards the end (btw it also explains the n+1 selects problem and all strategies to deal with it),
• examples of HQL subqueries from the Hibernate reference,
• just in case, the chapter on fetching strategies from the Hibernate reference - similar content as the first one, but much more thorough