Mike Heeren
Bij het ontwikkelen van applicaties die grote datasets moeten exposeren is het gebruikelijk om paginering toe te passen. Om te voorkomen dat de applicatie (maar ook de afnemer en database) belast wordt met onnodig grote datasets, worden deze opgeknipt in meerdere (los op te halen) pagina’s. Met de Pageable-interface biedt Spring Data hier een standaardoplossing voor aan. Echter, wanneer je bijvoorbeeld niet enkel wilt kunnen filteren op waarden uit de hoofdentiteit, maar ook op velden uit gerelateerde entiteiten, brengt dit extra complexiteit met zich mee. In deze blog duiken we in deze uitdaging.
Scenario
Het scenario waarmee we deze uitdaging willen demonstreren, zijn twee database tabellen die met een (lazy opgehaalde) OneToMany-relatie aan elkaar zijn gekoppeld:
Voor het testen gebruiken we de volgende dataset:
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
We willen een gepagineerde lijst van alle Parent-instanties ophalen die minimaal één volwassen (18 jaar of ouder) Child hebben. Op basis van de dataset zijn dit dus de Parent-instanties 1 t/m 6:
- Parent 1 heeft meerdere volwassen Child-
- Parent 2 t/m 6 hebben één volwassen Child-relatie.
- Parent 7 heeft wel een Child, maar dat is niet volwassen.
- Parent 8 heeft helemaal geen Child-relaties.
Query met FETCH JOIN
De eerste poging was om een JPA JOIN FETCH query te gebruiken. In tegenstelling tot een reguliere JOIN, haalt de JOIN FETCH de Child-instanties direct eager op. Het voordeel hiervan is dat als we deze objecten later in de code nodig hebben, deze niet nog een keer lazy opgehaald hoeven worden.
interface ParentEntityRepository : Repository<ParentEntity, Long> {
@Query("SELECT p FROM ParentEntity p JOIN FETCH p.children c WHERE c.age >= 18")
fun joinFetch(pageable: Pageable): Page<ParentEntity>
}
Als we deze methode aanroepen, lijkt deze in eerste instantie het gewenste gedrag te vertonen. Echter, wanneer we de log bekijken, zien we daar de volgende waarschuwing gelogd worden:
HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory
Als we vervolgens kijken naar de query die daadwerkelijk op de database is uitgevoerd, zien we inderdaad dat de Pageable parameters helemaal niet toegepast zijn:
select pe1_0.id,c1_0.parent_id,c1_0.id,c1_0.age,pe1_0.name from parent pe1_0 join child c1_0 on pe1_0.id=c1_0.parent_id where c1_0.age>=18
Effectief betekent dit dat niet enkel de objecten die daadwerkelijk worden teruggegeven door de methode in het geheugen zijn geladen, maar alle Parent-instanties met volwassen kinderen. Dit verbruikt natuurlijk onnodig veel rekenkracht. Niet alleen van onze eigen applicatie, maar ook van de onderliggende database.
Met de volgende instelling kunnen we Spring configureren dat dit niet alleen tot een waarschuwing leidt (die makkelijk over het hoofd is te zien), maar tot een fout in de applicatie:
spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true
Query met reguliere JOIN
De volgende poging was om een regulier JPA JOIN query te gebruiken:
@Query("SELECT p FROM ParentEntity p JOIN p.children c WHERE c.age >= 18")
fun joinWithoutDistinct(pageable: Pageable): Page<ParentEntity>
Wanneer we nu een pagina van 5 waarden opvragen, krijgen we echter maar 4 waarden terug en geeft het Page-object ook aan dat er totaal 7 waarden zijn (terwijl we er maar 6 verwachten).
Dit is te verklaren door de query die daadwerkelijk op de database is uitgevoerd:
select pe1_0.id,pe1_0.name from parent pe1_0 join child c1_0 on pe1_0.id=c1_0.parent_id where c1_0.age>=18 fetch first ? rows only
We zien nu dus dat de Pageable-parameter wordt toegepast. Echter, bij een JOIN-clausule op de database wordt het cartesische product teruggegeven. Effectief betekent dit dat Parent 1 nu twee keer (voor iedere volwassen Child-relatie) terugkomt in het resultaat uit de database. In combinatie met de fetch first-clausule valt hierdoor echter ook ten onrechte een Parent-instantie weg uit het resultaat en wordt Parent 1 ten onrechte dubbel geteld bij het totaal aantal waarden.
Query met JOIN en DISTINCT
Het bovenstaande probleem kan eenvoudig worden opgelost door een DISTINCT-clausule op te nemen. Hierdoor worden de gedupliceerde Parent-instanties namelijk ontdubbeld in de database voordat de fetch first-clausule wordt toegepast:
@Query("SELECT DISTINCT p FROM ParentEntity p JOIN p.children c WHERE c.age >= 18")
fun joinWithDistinct(pageable: Pageable): Page<ParentEntity>
Wat uiteindelijk de volgende query daadwerkelijk op de database uit zal voeren:
select distinct pe1_0.id,pe1_0.name from parent pe1_0 join child c1_0 on pe1_0.id=c1_0.parent_id where c1_0.age>=18 fetch first ? rows only
Hiermee hebben we het gewenste gedrag van de applicatie gerealiseerd.
Query met EXISTS-subquery
Hoewel de bovenstaande oplossing werkt, is het gevoelsmatig toch niet erg efficiënt om de JOIN-clausule meer rijen terug te laten geven dan noodzakelijk om deze vervolgens met de DISTINCT-clausule weer te ontdubbelen.
De zoektocht naar een alternatief leverde het volgende op:
@Query(
"""
SELECT p FROM ParentEntity p WHERE EXISTS (
SELECT 1 FROM ParentEntity p2 JOIN p2.children c WHERE p2.id = p.id AND c.age >= 18
)
"""
)
fun existsSubquery(pageable: Pageable): Page<ParentEntity
In plaats van de JOIN-clausule direct op hoofdniveau toe te passen, doen we dit in een subquery om te bepalen of er volwassen Child-relaties zijn. Op de database ziet de query er als volgt uit:
select pe1_0.id,pe1_0.name from parent pe1_0 where exists(select 1 from parent pe2_0 join child c1_0 on pe2_0.id=c1_0.parent_id where pe2_0.id=pe1_0.id and c1_0.age>=18) fetch first ? rows only
Ook deze query geeft ons het verwachte resultaat. Het voordeel van deze variant is dat de EXISTS-clausule direct na het vinden van de eerste relatie kan stoppen met zoeken, in tegenstelling tot de JOIN-clausule op hoofdniveau. Bij deze laatste moeten namelijk alsnog alle Child-relaties doorlopen worden.
Let op: Welke van de queries daadwerkelijk efficiënter verwerkt kan worden door de database, kan per database type verschillend zijn. Als je aan een vergelijkbare uitdaging werkt, gebruik vooral eens een profiler voor beide queries om te kijken wat de performance-implicaties zijn!
JPA Criteria API
Als alternatief voor de JPA-queries is het mogelijk om de ParentEntityRepository de JpaSpecificationExecutor interface te laten implementeren. Op deze manier is het mogelijk om gebruik te maken van de JPA Criteria API.
Het onderstaande verzoek resulteert bijvoorbeeld in dezelfde database-query als de eerdere joinWithDistinct-methode:
parentEntityRepository.findAll(
{ root, query, builder ->
query?.distinct(true)
val child = root.join<Any, Any>("children")
builder.greaterThanOrEqualTo(child.get("age"), 18)
},
pageable
)
Het equivalent van de existsSubquery-methode zou er als volgt uitzien:
parentEntityRepository.findAll(
{ root, query, builder ->
builder.exists(query!!.subquery(ParentEntity::class.java).also { subquery ->
val subqueryRoot = subquery.from(root.model)
val child = subqueryRoot.join<Any, Any>("children")
subquery.where(
builder.equal(subqueryRoot, root),
builder.greaterThanOrEqualTo(child.get("age"), 18)
)
})
},
pageable
)
Op deze manier kun je queries dynamischer opzetten dan via de “traditionele” JPA-queries. Het is echter net wat complexer qua implementatie en onderhoudbaarheid van de code.
Conclusie
We hebben een duik genomen in het pagineren van data die we tevens willen kunnen filteren op basis van waarden uit gerelateerde entiteiten.
Hoewel Spring Data hier een standaardoplossing voor biedt, zijn er toch queries op te stellen die vervolgens alsnog alle rijen ophalen uit de database, waarbij de paginering pas in het geheugen wordt toegepast. Dit lijkt in geen enkel scenario wenselijk en de waarschuwing die gelogd wordt is makkelijk om over het hoofd te zien. Het advies is dus om altijd de fail_on_pagination_over_collection_fetch configuratie in te stellen.
We hebben daarnaast twee queries opgesteld die het gewenste gedrag realiseren:
- JOIN in combinatie met DISTINCT
- EXISTS-subquery
De laatste lijkt net wat efficiënter te zijn. Echter, hoe efficiënt deze beide daadwerkelijk zijn kan per database type verschillen. Test beide scenario’s dus vooral met een profiler om te kijken wat de performance-implicaties echt zijn!
Alle code in dit artikel is beschikbaar op GitHub:
https://github.com/thecodebuilders-nl/pageable-with-joins
