ecamp / ecamp/ecamp3

n+1 database queries when fetching /content_nodes and /camps endpoints

Open
#8,123 2 comments 0 reactions 0 assignees View on GitHub
Performance type: API
Dominant language
PHP
Stars
156
Forks
72
Avg merge
12h 43m
Merged PRs (30d)
203

Description

There is an n+1 problem in the API (this one has nothing to do with the frontend).
When fetching the /content_nodes endpoint, there is a separate SQL statement executed for each material node (loading the connected material items) and for each checklist node (loading the connected checklist items).
When fetching the /camps endpoint, there are 3 separate SQL statements executed for each camp, loading the periods, camp collaborations and material lists.

This can be reproduced locally by visiting http://localhost:3000/api/content_nodes or http://localhost:3000/api/camps and afterwards http://localhost:3000/api/_profiler/empty/search/results?limit=10, clicking on the topmost Token and then on the Doctrine section in the left navigation.

It originally became evident during #8004, when new content node fixtures were added and the query counts in the EndpointPerformanceTest (for [content nodes](https://github.com/ecamp/ecamp3/pull/8004#discussion_r2320882992) and for [camps](https://github.com/ecamp/ecamp3/pull/8004#discussion_r2320888044)) went up.

This could potentially be solved by using Doctrine's `fetch: EAGER` feature. According to the [documentation](https://www.doctrine-project.org/projects/doctrine-orm/en/3.5/reference/working-with-objects.html#by-eager-loading) and some rudimentary tests, for one-to-many relations, doctrine can query the top-level entity and then query the related entities in a second query (or in batches of 100 by default, if there are many). But eager fetching like this can potentially cause performance impacts in other endpoints.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.