Case study
Collapsing an N+1 into a single native query
A slow permission-list endpoint, fixed by reading the SQL before optimizing.
many → 1
Queries
−1 cursor path
Fetch layers
profile-first
Approach
Situation
A permission-list endpoint on our identity platform was slow. Turning on Hibernate SQL logging showed it was firing a cascade of queries — a classic N+1 — when fetching group permissions, plus a cursor-based repository path that added overhead.
Task
Cut the database round-trips and hit the exact response shape the API needed, without loading full entity graphs.
Action
First I confirmed the pattern by reading the generated SQL rather than guessing — the log made the repeated per-row queries obvious.
Then I replaced the multi-query fetch with a single native query that joins the relevant tables and projects exactly the columns the response needs, mapping them straight into the response structure — and removed the redundant cursor-based path entirely.
@Query(value = """
SELECT g.group_id AS groupId,
g.group_name AS groupName,
p.permission_id AS permissionId,
p.action AS action,
r.resource_key AS resourceKey
FROM auth_group g
JOIN group_permission gp ON gp.group_id = g.group_id
JOIN permission p ON p.permission_id = gp.permission_id
JOIN resource r ON r.resource_id = p.resource_id
WHERE g.tenant_id = :tenantId
""", nativeQuery = true)
List<GroupPermissionView> findPermissionsForTenant(@Param("tenantId") Long tenantId);The projection interface (GroupPermissionView) exposes only the fields the API returns, so nothing loads the full entity graph.
Result
- Collapsed many queries into one and removed an entire redundant fetch layer, materially reducing endpoint latency.
- Reinforced a habit of profiling with SQL logs before optimizing — measure, localize, collapse.
- The projection keeps the query aligned to the response contract, so the read path can't leak internal model changes.