Hibernate — проблема декартова произведения при запросах с пагинацией

а можно просто использовать cte и inner select, получая списки сразу как массив с нужной паджинацией как основной таблицы, так и вложенных списков.

Что-то аж загрустил...

На дворе 23 год, а я такую же проблему решал еще в далеком 2008. И толком решения нормального так и нет ни у кого.

Наоборот, стало только хуже. Теперь по канонам у вас должны быть отдельные микросервисы для клиентов и для счетов. У каждого микросервиса своя база и SELECT N+1 превращается в HTTP GET+SELECT N+1.

Действительно, как отметили в комментариях - нативный запрос в статье упомянут, но не сказано, почему это решение не подошло. Но в целом очень круто и интересно, спасибо!

Как показала практика отношения на уровне сущностей это хорошо для понимания модели данных, но какой-то изврат для выборки данных из таблиц. В своих проектах во избежание n+1 и загрузки ненужных данных используем native запросы с json_agg вложенных сущностей. Также динамические запросы собираем и выполняем через entity-manager обертку. Может быть такой подход покажется "грубым", но он хорош по производительности и более понятен разработчикам.

Мы на проекте, чтобы избежать всего этого, включили sping-hibernate-query-utils в режиме exception, чтобы никаких N+1 не прошло. Сейчас надежда на тесты и QA, чтобы исключение не дошло до боя.

В хибернейте есть проперти, которая будет ещё другой исключение кидать при попытке пагинации в памяти, вот её тоже хотим включить скоро, и отловить эти вредные места.

Пока наше решение это одновременно и включённые батчи в пропертях глобально в режиме PADDING, и 2х запросы.

В указанном примере с Entity graph проблема декартова произведения не ушла, а только переместилась в запрос getAllByIdIn. Из-за этого и OutOfMemoryError.

Есть более эффективное решение с Entity graph/Join fetch:

1. Запрос с пагинацией должен возвращать сущность, с непроинициализированными дочерними коллекциями.

2. Для каждой дочерней коллекции выполняется отдельный запрос.

@Repository
interface ClientRepository : JpaRepository<ClientEntity, Long> {

    fun getAll(pageable: Pageable): Page<ClientEntity>

    @EntityGraph(attributePaths = ["accounts"])
    fun getWithAccountsByIdIn(clientIds: List<Long>): List<ClientEntity>

    @EntityGraph(attributePaths = ["deposits"])
    fun getWithDepositsByIdIn(clientIds: List<Long>): List<ClientEntity>
}

В результате всегда будет вызываться ровно 4 запроса, вне зависимости от числа извлекаемых записей:

1. select * from client c limit 50 offset 50
2. select count(c.id) from client c
3. select * from client c left outer join account a on c.id=a.client_id where c.id in (51, 52, ...)
4. select * from client c left outer join deposit d on c.id=d.client_id where c.id in (51, 52, ...)

В большинстве случаев этот подход эффективнее, чем использование @BatchSize и нагляднее, чем использование Native SQL, особенно когда пейджинг выполняется с критериями.

Подход тоже не идеален:

• Запросы приходится вызывать явно.

• Лишний раз извлекаются родительские сущности вместе с дочками.

• Код не очевидный. Результаты 3-го и 4-го запросов используются неявно. На самом деле, в родительских сущностях наполняются Lazy-коллекции.

Извлекать только дочки без родительких сущностей можно, но тогда не будут наполняться Lazy-коллекции, код будет значительно сложнее.