How to fetch multiple to-many relationships with jOOQ MULTISET
Last modified:
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how we can fetch multiple to-many relationships with jOOQ MULTISET so that we avoid bumping into an unintentional Cartesian Product.
The approach taken by jOOQ is truly revolutionary since it offers a solution that allows you to fetch multiple to-many relationships using a single type-safe query.
Domain Model
Let’s assume that we have the following database tables:
The POST table has a one-to-many relationship with the POST_COMMENT table and a many-to-many relationship with the TAG table.
The POST_COMMENT table has a one-to-many relationship with the USER_VOTE table.
Fetching multiple to-many relationships with multiple JOINs
Let’s assume we have a business use case that requires us to fetch the POST table records with the ID column value between 1 and 50, along with all their associated POST_COMMENT, USER_VOTE, and TAG records.
However, we don’t want to get a flat result set but one that’s hierarchical. Therefore, we want to get the results mapped to the following PostRecord hierarchy:
Faced with such a requirement, it’s not uncommon to see it implemented by joining the required database tables and aggregating the flat result set to the hierarchical PostRecord structure, as illustrated by the following example:
List<PostRecord> posts = sql
.select(
POST.ID,
POST.TITLE,
POST_COMMENT.ID,
POST_COMMENT.REVIEW,
TAG.ID,
TAG.NAME,
USER_VOTE.ID,
USER_VOTE.VOTE_TYPE,
concat(
USER.FIRST_NAME,
inline(" "),
USER.LAST_NAME
)
)
.from(POST)
.leftOuterJoin(POST_COMMENT).on(POST_COMMENT.POST_ID.eq(POST.ID))
.leftOuterJoin(POST_TAG).on(POST_TAG.POST_ID.eq(POST.ID))
.leftOuterJoin(TAG).on(TAG.ID.eq(POST_TAG.TAG_ID))
.leftOuterJoin(USER_VOTE).on(USER_VOTE.COMMENT_ID.eq(POST_COMMENT.ID))
.leftOuterJoin(USER).on(USER.ID.eq(USER_VOTE.USER_ID))
.where(POST.ID.between(minPostId, maxPostId))
.orderBy(POST.ID.asc())
.fetchInto(FlatPostRecord.class)
.stream()
.collect(
Collectors.collectingAndThen(
Collectors.toMap(
FlatPostRecord::postId,
record -> {
PostRecord post = new PostRecord(
record.postId(),
record.postTitle(),
new ArrayList<>(),
new ArrayList<>()
);
Long commentId = record.commentId();
if (commentId != null) {
CommentRecord commentRecord = new CommentRecord(
commentId,
record.commentReview(),
new ArrayList<>()
);
Long voteId = record.voteId();
if (voteId != null) {
commentRecord.votes().add(
new UserVoteRecord(
voteId,
record.userName(),
record.voteType()
)
);
}
post.comments().add(
commentRecord
);
}
Long tagId = record.tagId();
if (tagId != null) {
post.tags().add(
new TagRecord(
tagId,
record.tagName()
)
);
}
return post;
},
(PostRecord existing, PostRecord replacement) -> {
if(replacement.comments().size() == 1) {
CommentRecord newCommentRecord =
replacement.comments().get(0);
CommentRecord existingCommentRecord = existing
.comments()
.stream()
.filter(
commentRecord -> commentRecord
.id().equals(newCommentRecord.id())
)
.findAny()
.orElse(null);
if(existingCommentRecord == null) {
existing.comments().add(
newCommentRecord
);
} else {
if(newCommentRecord.votes().size() == 1) {
UserVoteRecord newUserVoteRecord =
newCommentRecord.votes().get(0);
if(!existingCommentRecord.votes()
.contains(newUserVoteRecord)) {
existingCommentRecord.votes().add(
newUserVoteRecord
);
}
}
}
}
if(replacement.tags().size() == 1) {
TagRecord newTagRecord = replacement
.tags().get(0);
if(!existing.tags().contains(newTagRecord)) {
existing.tags().add(
newTagRecord
);
}
}
return existing;
},
LinkedHashMap::new
),
(Function<Map<Long, PostRecord>, List<PostRecord>>)
map -> new ArrayList<>(map.values())
)
);
However, this solution suffers from the implicit Cartesian Product performance problem, which is only obvious when counting the number of records returned by the flat result set:
List<FlatPostRecord> posts = sql
.select(
POST.ID,
POST.TITLE,
POST_COMMENT.ID,
POST_COMMENT.REVIEW,
TAG.ID,
TAG.NAME,
USER_VOTE.ID,
USER_VOTE.VOTE_TYPE,
concat(
USER.FIRST_NAME,
inline(" "),
USER.LAST_NAME
)
)
.from(POST)
.leftOuterJoin(POST_COMMENT).on(POST_COMMENT.POST_ID.eq(POST.ID))
.leftOuterJoin(POST_TAG).on(POST_TAG.POST_ID.eq(POST.ID))
.leftOuterJoin(TAG).on(TAG.ID.eq(POST_TAG.TAG_ID))
.leftOuterJoin(USER_VOTE).on(USER_VOTE.COMMENT_ID.eq(POST_COMMENT.ID))
.leftOuterJoin(USER).on(USER.ID.eq(USER_VOTE.USER_ID))
.where(POST.ID.between(minPostId, maxPostId))
.orderBy(POST.ID.asc())
.fetchInto(FlatPostRecord.class);
assertEquals(
POST_COUNT * POST_COMMENT_COUNT * TAG_COUNT * VOTE_COUNT,
posts.size()
);
Not nice!
The flat result set size is the product of the filtered POST records (50) with all the matching POST_COMMENT, USER_VOTE and POST_TAG records.
When checking the execution plan, we can see that this SQL query generated a result set containing 50,000 records and required 4.58 MB to sort the result set:
-------------------------------------------------------------------------------------------------------- | Id | Operation | Name | A-Rows | Used-Mem | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50000 | | | 1 | SORT ORDER BY | | 50000 | 4582K (0)| |* 2 | FILTER | | 50000 | | |* 3 | HASH JOIN RIGHT OUTER | | 50000 | 591K (0)| | 4 | TABLE ACCESS FULL | USER | 2 | | | 5 | NESTED LOOPS OUTER | | 50000 | | | 6 | NESTED LOOPS OUTER | | 10000 | | |* 7 | HASH JOIN RIGHT OUTER | | 500 | 921K (0)| | 8 | TABLE ACCESS FULL | TAG | 10 | | | 9 | NESTED LOOPS OUTER | | 500 | | |* 10 | TABLE ACCESS FULL | POST | 50 | | | 11 | TABLE ACCESS BY INDEX ROWID BATCHED| POST_TAG | 500 | | |* 12 | INDEX RANGE SCAN | IDX_POST_TAG_POST_ID | 500 | | | 13 | TABLE ACCESS BY INDEX ROWID BATCHED | POST_COMMENT | 10000 | | |* 14 | INDEX RANGE SCAN | IDX_POST_COMMENT_POST_ID | 10000 | | | 15 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_VOTE | 50000 | | |* 16 | INDEX RANGE SCAN | IDX_USER_VOTE_POST_COMMENT_ID | 50000 | | --------------------------------------------------------------------------------------------------------
The reason why the database resorts to an implicit Cartesian Product, in this case, is because there’s no other way to join the following two subsets of data:
POSTwithPOST_COMMENTandUSER_VOTEPOSTwithPOST_TAG
Since the USER_VOTE and POST_TAG are unrelated, the only way the relational database can combine two unrelated datasets is to create a Cartesian Product, which is going to send a lot of records from the database server to the client.
Sending tens or hundreds of thousands of records due to an implicit Cartesian Product is going to have a performance impact on our transaction response time, so we are interested in avoiding such an inconvenient outcome.
Fetching multiple to-many relationships with jOOQ MULTISET
To avoid the Cartesian Product, we need to aggregate the associated to-many records in the SQL query itself, and that’s exactly how the jOOQ MULTISET operator works.
Not only that we will get rid of the Cartesian Product, but we no longer have to manually transform the flat result set to the hierarchical PostRecord structure, as illustrated by the following example:
List<PostRecord> posts = sql
.select(
POST.ID.as("id"),
POST.TITLE,
multiset(
select(
POST_COMMENT.ID,
POST_COMMENT.REVIEW,
multiset(
select(
USER_VOTE.ID,
concat(
USER.FIRST_NAME,
inline(" "),
USER.LAST_NAME
),
USER_VOTE.VOTE_TYPE
)
.from(USER_VOTE)
.innerJoin(USER).on(USER.ID.eq(USER_VOTE.USER_ID))
.where(USER_VOTE.COMMENT_ID.eq(POST_COMMENT.ID))
).as("votes").convertFrom(
r -> r.map(Records.mapping(UserVoteRecord::new))
)
)
.from(POST_COMMENT)
.where(POST_COMMENT.POST_ID.eq(POST.ID))
).as("comments").convertFrom(
r -> r.map(Records.mapping(CommentRecord::new))
),
multiset(
select(
POST_TAG.tag().ID,
POST_TAG.tag().NAME
)
.from(POST_TAG)
.where(POST_TAG.POST_ID.eq(POST.ID))
).as("tags").convertFrom(
r -> r.map(Records.mapping(TagRecord::new))
)
)
.from(POST)
.where(POST.ID.between(minPostId, maxPostId))
.orderBy(POST.ID.asc())
.fetch(Records.mapping(PostRecord::new));
assertEquals(POST_COUNT, posts.size());
PostRecord post = posts.get(0);
assertEquals(POST_COMMENT_COUNT, post.comments().size());
assertEquals(TAG_COUNT, post.tags().size());
CommentRecord comment = post.comments().get(0);
assertEquals(VOTE_COUNT, comment.votes().size());
When running the above MULTISET query, jOOQ executes the following SQL query:
select
"POST"."ID" "id",
"POST"."TITLE",
(
select
coalesce(
json_arrayagg(
json_array(
"v0",
"v1",
"v2" format json null on null returning clob
) format json returning clob
),
json_array(returning clob)
)
from (
select
"POST_COMMENT"."ID" "v0",
"POST_COMMENT"."REVIEW" "v1",
(
select
coalesce(
json_arrayagg(
json_array(
"v0",
"v1",
"v2" null on null returning clob
) format json returning clob
),
json_array(returning clob)
)
from (
select
"USER_VOTE"."ID" "v0",
(
("USER"."FIRST_NAME" || ' ') ||
"USER"."LAST_NAME"
) "v1",
"USER_VOTE"."VOTE_TYPE" "v2"
from
"USER_VOTE"
join
"USER" on "USER"."ID" = "USER_VOTE"."USER_ID"
where
"USER_VOTE"."COMMENT_ID" = "POST_COMMENT"."ID"
) "t"
) "v2"
from
"POST_COMMENT"
where
"POST_COMMENT"."POST_ID" = "POST"."ID"
) "t"
) "comments",
(
select
coalesce(
json_arrayagg(
json_array(
"v0",
"v1" null on null returning clob
) format json returning clob
),
json_array(returning clob)
)
from (
select
"alias_111264759"."ID" "v0",
"alias_111264759"."NAME" "v1"
from (
"POST_TAG"
join
"TAG" "alias_111264759"
on "POST_TAG"."TAG_ID" = "alias_111264759"."ID"
)
where
"POST_TAG"."POST_ID" = "POST"."ID"
) "t"
) "tags"
from
"POST"
where
"POST"."ID" between 1 and 50
order by
"POST"."ID" asc
When executing the SQL query above, we can see that, indeed, only 50 records are returned by the databse:
| id | title | comments | tags | |----|-------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 | Post nr. 1 | [[1,"Comment nr. 1",[[1,"Alice Smith",3],[3,"Alice Smith",2],[2,"Bob Johnson",0],[4,"Bob Johnson",0],[5,"Bob Johnson",2]]],[2,"Comment nr. 2",[[6,"Alice Smith",0],[9,"Alice Smith",3],[7,"Bob Johnson",0],[8,"Bob Johnson",1],[10,"Bob Johnson",1]]],[3,"Comment nr. 3",[[11,"Alice Smith",2],[13,"Alice Smith",2],[12,"Bob Johnson",3],[14,"Bob Johnson",1],[15,"Bob Johnson",4]]],[4,"Comment nr. 4",[[16,"Alice Smith",4],[17,"Alice Smith",4],[19,"Alice Smith",4],[18,"Bob Johnson",2],[20,"Bob Johnson",4]]],[5,"Comment nr. 5",[[23,"Alice Smith",0],[24,"Alice Smith",3],[21,"Bob Johnson",0],[22,"Bob Johnson",3],[25,"Bob Johnson",1]]],[6,"Comment nr. 6",[[27,"Alice Smith",3],[28,"Alice Smith",0],[26,"Bob Johnson",3],[29,"Bob Johnson",0],[30,"Bob Johnson",1]]],[7,"Comment nr. 7",[[31,"Alice Smith",3],[35,"Alice Smith",3],[32,"Bob Johnson",4],[33,"Bob Johnson",3],[34,"Bob Johnson",1]]],[8,"Comment nr. 8",[[37,"Alice Smith",4],[38,"Alice Smith",3],[39,"Alice Smith",1],[40,"Alice Smith",2],[36,"Bob Johnson",4]]],[9,"Comment nr. 9",[[41,"Bob Johnson",1],[42,"Bob Johnson",1],[43,"Bob Johnson",1],[44,"Bob Johnson",1],[45,"Bob Johnson",2]]],[10,"Comment nr. 10",[[46,"Alice Smith",4],[47,"Alice Smith",4],[48,"Alice Smith",4],[49,"Alice Smith",3],[50,"Bob Johnson",4]]],[11,"Comment nr. 11",[[51,"Alice Smith",0],[53,"Alice Smith",3],[52,"Bob Johnson",4],[54,"Bob Johnson",0],[55,"Bob Johnson",4]]],[12,"Comment nr. 12",[[56,"Alice Smith",4],[59,"Alice Smith",2],[57,"Bob Johnson",2],[58,"Bob Johnson",1],[60,"Bob Johnson",4]]],[13,"Comment nr. 13",[[61,"Bob Johnson",3],[62,"Bob Johnson",0],[63,"Bob Johnson",4],[64,"Bob Johnson",3],[65,"Bob Johnson",1]]],[14,"Comment nr. 14",[[70,"Alice Smith",0],[66,"Bob Johnson",3],[67,"Bob Johnson",2],[68,"Bob Johnson",0],[69,"Bob Johnson",2]]],[15,"Comment nr. 15",[[74,"Alice Smith",2],[71,"Bob Johnson",3],[72,"Bob Johnson",1],[73,"Bob Johnson",2],[75,"Bob Johnson",0]]],[16,"Comment nr. 16",[[79,"Alice Smith",0],[76,"Bob Johnson",0],[77,"Bob Johnson",1],[78,"Bob Johnson",2],[80,"Bob Johnson",2]]],[17,"Comment nr. 17",[[81,"Alice Smith",2],[82,"Bob Johnson",1],[83,"Bob Johnson",0],[84,"Bob Johnson",0],[85,"Bob Johnson",0]]],[18,"Comment nr. 18",[[86,"Alice Smith",0],[87,"Alice Smith",1],[89,"Alice Smith",3],[90,"Alice Smith",0],[88,"Bob Johnson",3]]],[19,"Comment nr. 19",[[93,"Alice Smith",4],[94,"Alice Smith",2],[95,"Alice Smith",0],[91,"Bob Johnson",1],[92,"Bob Johnson",0]]],[20,"Comment nr. 20",[[96,"Alice Smith",4],[98,"Alice Smith",0],[100,"Alice Smith",1],[97,"Bob Johnson",3],[99,"Bob Johnson",0]]]] | [[1,"Tag nr. 1"],[2,"Tag nr. 2"],[3,"Tag nr. 3"],[4,"Tag nr. 4"],[5,"Tag nr. 5"],[6,"Tag nr. 6"],[7,"Tag nr. 7"],[8,"Tag nr. 8"],[9,"Tag nr. 9"],[10,"Tag nr. 10"]] | | 2 | Post nr. 2 | [[21,"Comment nr. 21",[[103,"Alice Smith",4],[101,"Bob Johnson",1],[102,"Bob Johnson",3],[104,"Bob Johnson",3],[105,"Bob Johnson",0]]],[22,"Comment nr. 22",[[110,"Alice Smith",0],[106,"Bob Johnson",3],[107,"Bob Johnson",3],[108,"Bob Johnson",2],[109,"Bob Johnson",2]]],[23,"Comment nr. 23",[[113,"Alice Smith",4],[111,"Bob Johnson",1],[112,"Bob Johnson",1],[114,"Bob Johnson",2],[115,"Bob Johnson",0]]],[24,"Comment nr. 24",[[116,"Alice Smith",0],[117,"Alice Smith",4],[118,"Alice Smith",0],[120,"Alice Smith",1],[119,"Bob Johnson",1]]],[25,"Comment nr. 25",[[125,"Alice Smith",3],[121,"Bob Johnson",2],[122,"Bob Johnson",4],[123,"Bob Johnson",4],[124,"Bob Johnson",4]]],[26,"Comment nr. 26",[[129,"Alice Smith",1],[126,"Bob Johnson",4],[127,"Bob Johnson",1],[128,"Bob Johnson",0],[130,"Bob Johnson",0]]],[27,"Comment nr. 27",[[131,"Alice Smith",3],[134,"Alice Smith",4],[132,"Bob Johnson",0],[133,"Bob Johnson",4],[135,"Bob Johnson",4]]],[28,"Comment nr. 28",[[140,"Alice Smith",4],[136,"Bob Johnson",0],[137,"Bob Johnson",2],[138,"Bob Johnson",3],[139,"Bob Johnson",4]]],[29,"Comment nr. 29",[[144,"Alice Smith",1],[141,"Bob Johnson",2],[142,"Bob Johnson",4],[143,"Bob Johnson",1],[145,"Bob Johnson",0]]],[30,"Comment nr. 30",[[147,"Alice Smith",2],[148,"Alice Smith",0],[146,"Bob Johnson",3],[149,"Bob Johnson",3],[150,"Bob Johnson",1]]],[31,"Comment nr. 31",[[151,"Alice Smith",1],[152,"Alice Smith",1],[153,"Alice Smith",0],[154,"Alice Smith",0],[155,"Bob Johnson",0]]],[32,"Comment nr. 32",[[158,"Alice Smith",4],[160,"Alice Smith",3],[156,"Bob Johnson",4],[157,"Bob Johnson",4],[159,"Bob Johnson",0]]],[33,"Comment nr. 33",[[161,"Alice Smith",2],[162,"Alice Smith",0],[163,"Bob Johnson",3],[164,"Bob Johnson",0],[165,"Bob Johnson",0]]],[34,"Comment nr. 34",[[166,"Alice Smith",4],[167,"Alice Smith",4],[168,"Alice Smith",3],[169,"Alice Smith",1],[170,"Bob Johnson",1]]],[35,"Comment nr. 35",[[172,"Alice Smith",3],[171,"Bob Johnson",0],[173,"Bob Johnson",4],[174,"Bob Johnson",4],[175,"Bob Johnson",4]]],[36,"Comment nr. 36",[[176,"Alice Smith",2],[177,"Alice Smith",4],[180,"Alice Smith",4],[178,"Bob Johnson",3],[179,"Bob Johnson",2]]],[37,"Comment nr. 37",[[183,"Alice Smith",4],[185,"Alice Smith",1],[181,"Bob Johnson",0],[182,"Bob Johnson",0],[184,"Bob Johnson",2]]],[38,"Comment nr. 38",[[186,"Alice Smith",2],[188,"Alice Smith",2],[189,"Alice Smith",1],[190,"Alice Smith",4],[187,"Bob Johnson",1]]],[39,"Comment nr. 39",[[192,"Alice Smith",0],[193,"Alice Smith",0],[191,"Bob Johnson",0],[194,"Bob Johnson",0],[195,"Bob Johnson",3]]],[40,"Comment nr. 40",[[200,"Alice Smith",0],[196,"Bob Johnson",3],[197,"Bob Johnson",3],[198,"Bob Johnson",2],[199,"Bob Johnson",4]]]] | [[1,"Tag nr. 1"],[2,"Tag nr. 2"],[3,"Tag nr. 3"],[4,"Tag nr. 4"],[5,"Tag nr. 5"],[6,"Tag nr. 6"],[7,"Tag nr. 7"],[8,"Tag nr. 8"],[9,"Tag nr. 9"],[10,"Tag nr. 10"]] | | 3 | Post nr. 3 | [[41,"Comment nr. 41",[[201,"Alice Smith",2],[203,"Alice Smith",4],[204,"Alice Smith",0],[205,"Alice Smith",1],[202,"Bob Johnson",1]]],[42,"Comment nr. 42",[[206,"Alice Smith",2],[207,"Alice Smith",1],[208,"Alice Smith",3],[209,"Alice Smith",4],[210,"Bob Johnson",4]]],[43,"Comment nr. 43",[[211,"Alice Smith",2],[213,"Alice Smith",3],[215,"Alice Smith",2],[212,"Bob Johnson",3],[214,"Bob Johnson",2]]],[44,"Comment nr. 44",[[217,"Alice Smith",4],[218,"Alice Smith",4],[220,"Alice Smith",0],[216,"Bob Johnson",1],[219,"Bob Johnson",3]]],[45,"Comment nr. 45",[[224,"Alice Smith",0],[225,"Alice Smith",1],[221,"Bob Johnson",0],[222,"Bob Johnson",4],[223,"Bob Johnson",2]]],[46,"Comment nr. 46",[[226,"Alice Smith",4],[228,"Alice Smith",2],[229,"Alice Smith",2],[230,"Alice Smith",4],[227,"Bob Johnson",2]]],[47,"Comment nr. 47",[[231,"Alice Smith",3],[232,"Alice Smith",4],[233,"Alice Smith",1],[235,"Alice Smith",0],[234,"Bob Johnson",2]]],[48,"Comment nr. 48",[[236,"Alice Smith",0],[238,"Alice Smith",3],[240,"Alice Smith",4],[237,"Bob Johnson",3],[239,"Bob Johnson",1]]],[49,"Comment nr. 49",[[242,"Alice Smith",0],[245,"Alice Smith",4],[241,"Bob Johnson",0],[243,"Bob Johnson",0],[244,"Bob Johnson",1]]],[50,"Comment nr. 50",[[246,"Alice Smith",3],[247,"Alice Smith",0],[248,"Alice Smith",4],[249,"Alice Smith",1],[250,"Alice Smith",3]]],[51,"Comment nr. 51",[[251,"Alice Smith",3],[252,"Bob Johnson",4],[253,"Bob Johnson",3],[254,"Bob Johnson",1],[255,"Bob Johnson",2]]],[52,"Comment nr. 52",[[256,"Alice Smith",0],[257,"Alice Smith",1],[259,"Alice Smith",1],[258,"Bob Johnson",1],[260,"Bob Johnson",4]]],[53,"Comment nr. 53",[[262,"Alice Smith",3],[264,"Alice Smith",1],[265,"Alice Smith",2],[261,"Bob Johnson",4],[263,"Bob Johnson",0]]],[54,"Comment nr. 54",[[268,"Alice Smith",4],[270,"Alice Smith",3],[266,"Bob Johnson",4],[267,"Bob Johnson",0],[269,"Bob Johnson",2]]],[55,"Comment nr. 55",[[274,"Alice Smith",4],[275,"Alice Smith",2],[271,"Bob Johnson",0],[272,"Bob Johnson",0],[273,"Bob Johnson",0]]],[56,"Comment nr. 56",[[278,"Alice Smith",0],[279,"Alice Smith",1],[276,"Bob Johnson",0],[277,"Bob Johnson",4],[280,"Bob Johnson",3]]],[57,"Comment nr. 57",[[281,"Alice Smith",0],[283,"Alice Smith",2],[285,"Alice Smith",1],[282,"Bob Johnson",3],[284,"Bob Johnson",4]]],[58,"Comment nr. 58",[[286,"Alice Smith",2],[289,"Alice Smith",3],[290,"Alice Smith",0],[287,"Bob Johnson",2],[288,"Bob Johnson",0]]],[59,"Comment nr. 59",[[294,"Alice Smith",3],[291,"Bob Johnson",3],[292,"Bob Johnson",3],[293,"Bob Johnson",3],[295,"Bob Johnson",0]]],[60,"Comment nr. 60",[[296,"Alice Smith",4],[297,"Alice Smith",0],[298,"Alice Smith",4],[299,"Alice Smith",0],[300,"Bob Johnson",4]]]] | [[1,"Tag nr. 1"],[2,"Tag nr. 2"],[3,"Tag nr. 3"],[4,"Tag nr. 4"],[5,"Tag nr. 5"],[6,"Tag nr. 6"],[7,"Tag nr. 7"],[8,"Tag nr. 8"],[9,"Tag nr. 9"],[10,"Tag nr. 10"]] | | 4 | Post nr. 4 | [[61,"Comment nr. 61",[[305,"Alice Smith",1],[301,"Bob Johnson",1],[302,"Bob Johnson",0],[303,"Bob Johnson",1],[304,"Bob Johnson",0]]],[62,"Comment nr. 62",[[308,"Alice Smith",2],[309,"Alice Smith",0],[306,"Bob Johnson",4],[307,"Bob Johnson",4],[310,"Bob Johnson",0]]],[63,"Comment nr. 63",[[311,"Alice Smith",0],[313,"Alice Smith",1],[312,"Bob Johnson",2],[314,"Bob Johnson",1],[315,"Bob Johnson",1]]],[64,"Comment nr. 64",[[317,"Alice Smith",0],[318,"Alice Smith",0],[319,"Alice Smith",3],[320,"Alice Smith",3],[316,"Bob Johnson",2]]],[65,"Comment nr. 65",[[322,"Alice Smith",0],[325,"Alice Smith",1],[321,"Bob Johnson",3],[323,"Bob Johnson",2],[324,"Bob Johnson",1]]],[66,"Comment nr. 66",[[327,"Alice Smith",4],[328,"Alice Smith",1],[329,"Alice Smith",2],[330,"Alice Smith",2],[326,"Bob Johnson",3]]],[67,"Comment nr. 67",[[333,"Alice Smith",4],[334,"Alice Smith",4],[335,"Alice Smith",0],[331,"Bob Johnson",2],[332,"Bob Johnson",1]]],[68,"Comment nr. 68",[[336,"Alice Smith",0],[337,"Alice Smith",3],[339,"Alice Smith",4],[338,"Bob Johnson",2],[340,"Bob Johnson",2]]],[69,"Comment nr. 69",[[341,"Alice Smith",3],[344,"Alice Smith",3],[345,"Alice Smith",2],[342,"Bob Johnson",0],[343,"Bob Johnson",0]]],[70,"Comment nr. 70",[[350,"Alice Smith",4],[346,"Bob Johnson",2],[347,"Bob Johnson",1],[348,"Bob Johnson",1],[349,"Bob Johnson",3]]],[71,"Comment nr. 71",[[352,"Alice Smith",4],[354,"Alice Smith",1],[355,"Alice Smith",2],[351,"Bob Johnson",4],[353,"Bob Johnson",0]]],[72,"Comment nr. 72",[[359,"Alice Smith",1],[360,"Alice Smith",2],[356,"Bob Johnson",1],[357,"Bob Johnson",1],[358,"Bob Johnson",3]]],[73,"Comment nr. 73",[[361,"Alice Smith",4],[362,"Alice Smith",2],[364,"Alice Smith",2],[365,"Alice Smith",4],[363,"Bob Johnson",1]]],[74,"Comment nr. 74",[[367,"Alice Smith",4],[368,"Alice Smith",0],[370,"Alice Smith",1],[366,"Bob Johnson",4],[369,"Bob Johnson",3]]],[75,"Comment nr. 75",[[371,"Alice Smith",3],[372,"Alice Smith",0],[373,"Alice Smith",1],[374,"Alice Smith",1],[375,"Bob Johnson",4]]],[76,"Comment nr. 76",[[376,"Alice Smith",1],[378,"Alice Smith",0],[379,"Alice Smith",3],[380,"Alice Smith",2],[377,"Bob Johnson",1]]],[77,"Comment nr. 77",[[382,"Alice Smith",0],[381,"Bob Johnson",0],[383,"Bob Johnson",4],[384,"Bob Johnson",4],[385,"Bob Johnson",0]]],[78,"Comment nr. 78",[[386,"Alice Smith",3],[387,"Alice Smith",4],[388,"Alice Smith",2],[390,"Alice Smith",0],[389,"Bob Johnson",1]]],[79,"Comment nr. 79",[[391,"Alice Smith",0],[393,"Alice Smith",1],[394,"Alice Smith",4],[392,"Bob Johnson",4],[395,"Bob Johnson",1]]],[80,"Comment nr. 80",[[396,"Alice Smith",4],[397,"Alice Smith",0],[398,"Alice Smith",1],[400,"Alice Smith",1],[399,"Bob Johnson",0]]]] | [[1,"Tag nr. 1"],[2,"Tag nr. 2"],[3,"Tag nr. 3"],[4,"Tag nr. 4"],[5,"Tag nr. 5"],[6,"Tag nr. 6"],[7,"Tag nr. 7"],[8,"Tag nr. 8"],[9,"Tag nr. 9"],[10,"Tag nr. 10"]] | | 5 | Post nr. 5 | [[81,"Comment nr. 81",[[401,"Alice Smith",1],[402,"Alice Smith",0],[403,"Alice Smith",3],[404,"Bob Johnson",1],[405,"Bob Johnson",2]]],[82,"Comment nr. 82",[[406,"Alice Smith",3],[407,"Alice Smith",0],[408,"Alice Smith",2],[409,"Alice Smith",4],[410,"Alice Smith",2]]],[83,"Comment nr. 83",[[411,"Alice Smith",0],[413,"Alice Smith",0],[414,"Alice Smith",3],[415,"Alice Smith",0],[412,"Bob Johnson",4]]],[84,"Comment nr. 84",[[416,"Alice Smith",1],[417,"Alice Smith",2],[418,"Bob Johnson",2],[419,"Bob Johnson",4],[420,"Bob Johnson",0]]],[85,"Comment nr. 85",[[423,"Alice Smith",0],[424,"Alice Smith",4],[425,"Alice Smith",1],[421,"Bob Johnson",2],[422,"Bob Johnson",2]]],[86,"Comment nr. 86",[[426,"Alice Smith",1],[428,"Alice Smith",3],[429,"Alice Smith",3],[427,"Bob Johnson",0],[430,"Bob Johnson",2]]],[87,"Comment nr. 87",[[431,"Alice Smith",2],[432,"Alice Smith",0],[433,"Alice Smith",1],[434,"Alice Smith",4],[435,"Alice Smith",0]]],[88,"Comment nr. 88",[[436,"Alice Smith",0],[439,"Alice Smith",1],[437,"Bob Johnson",1],[438,"Bob Johnson",1],[440,"Bob Johnson",1]]],[89,"Comment nr. 89",[[444,"Alice Smith",3],[441,"Bob Johnson",4],[442,"Bob Johnson",1],[443,"Bob Johnson",4],[445,"Bob Johnson",4]]],[90,"Comment nr. 90",[[446,"Alice Smith",1],[447,"Alice Smith",2],[448,"Alice Smith",2],[449,"Bob Johnson",0],[450,"Bob Johnson",1]]],[91,"Comment nr. 91",[[451,"Alice Smith",1],[452,"Alice Smith",1],[453,"Alice Smith",1],[454,"Bob Johnson",4],[455,"Bob Johnson",3]]],[92,"Comment nr. 92",[[458,"Alice Smith",0],[459,"Alice Smith",1],[456,"Bob Johnson",3],[457,"Bob Johnson",4],[460,"Bob Johnson",1]]],[93,"Comment nr. 93",[[461,"Alice Smith",4],[462,"Alice Smith",4],[464,"Alice Smith",3],[463,"Bob Johnson",0],[465,"Bob Johnson",1]]],[94,"Comment nr. 94",[[466,"Alice Smith",1],[467,"Alice Smith",2],[470,"Alice Smith",0],[468,"Bob Johnson",4],[469,"Bob Johnson",1]]],[95,"Comment nr. 95",[[472,"Alice Smith",1],[473,"Alice Smith",3],[474,"Alice Smith",4],[475,"Alice Smith",1],[471,"Bob Johnson",2]]],[96,"Comment nr. 96",[[476,"Alice Smith",3],[477,"Alice Smith",3],[478,"Alice Smith",0],[479,"Alice Smith",3],[480,"Bob Johnson",1]]],[97,"Comment nr. 97",[[481,"Alice Smith",3],[482,"Bob Johnson",2],[483,"Bob Johnson",2],[484,"Bob Johnson",3],[485,"Bob Johnson",0]]],[98,"Comment nr. 98",[[489,"Alice Smith",1],[490,"Alice Smith",0],[486,"Bob Johnson",0],[487,"Bob Johnson",3],[488,"Bob Johnson",4]]],[99,"Comment nr. 99",[[491,"Alice Smith",2],[494,"Alice Smith",3],[492,"Bob Johnson",4],[493,"Bob Johnson",2],[495,"Bob Johnson",0]]],[100,"Comment nr. 100",[[496,"Alice Smith",2],[499,"Alice Smith",3],[497,"Bob Johnson",2],[498,"Bob Johnson",2],[500,"Bob Johnson",3]]]] | [[1,"Tag nr. 1"],[2,"Tag nr. 2"],[3,"Tag nr. 3"],[4,"Tag nr. 4"],[5,"Tag nr. 5"],[6,"Tag nr. 6"],[7,"Tag nr. 7"],[8,"Tag nr. 8"],[9,"Tag nr. 9"],[10,"Tag nr. 10"]] | ... | 50 | Post nr. 50 | [[981,"Comment nr. 981",[[4902,"Alice Smith",0],[4904,"Alice Smith",3],[4901,"Bob Johnson",2],[4903,"Bob Johnson",0],[4905,"Bob Johnson",4]]],[982,"Comment nr. 982",[[4907,"Alice Smith",4],[4908,"Alice Smith",0],[4909,"Alice Smith",1],[4906,"Bob Johnson",0],[4910,"Bob Johnson",3]]],[983,"Comment nr. 983",[[4914,"Alice Smith",4],[4915,"Alice Smith",1],[4911,"Bob Johnson",4],[4912,"Bob Johnson",4],[4913,"Bob Johnson",0]]],[984,"Comment nr. 984",[[4917,"Alice Smith",4],[4918,"Alice Smith",1],[4919,"Alice Smith",4],[4920,"Alice Smith",0],[4916,"Bob Johnson",4]]],[985,"Comment nr. 985",[[4921,"Alice Smith",4],[4923,"Alice Smith",1],[4924,"Alice Smith",4],[4925,"Alice Smith",2],[4922,"Bob Johnson",0]]],[986,"Comment nr. 986",[[4926,"Alice Smith",2],[4927,"Alice Smith",3],[4928,"Alice Smith",3],[4929,"Alice Smith",1],[4930,"Bob Johnson",2]]],[987,"Comment nr. 987",[[4931,"Alice Smith",4],[4932,"Alice Smith",1],[4933,"Bob Johnson",1],[4934,"Bob Johnson",3],[4935,"Bob Johnson",3]]],[988,"Comment nr. 988",[[4936,"Alice Smith",0],[4938,"Alice Smith",4],[4940,"Alice Smith",1],[4937,"Bob Johnson",2],[4939,"Bob Johnson",3]]],[989,"Comment nr. 989",[[4941,"Alice Smith",4],[4943,"Alice Smith",1],[4945,"Alice Smith",0],[4942,"Bob Johnson",4],[4944,"Bob Johnson",1]]],[990,"Comment nr. 990",[[4947,"Alice Smith",4],[4946,"Bob Johnson",3],[4948,"Bob Johnson",1],[4949,"Bob Johnson",0],[4950,"Bob Johnson",0]]],[991,"Comment nr. 991",[[4952,"Alice Smith",1],[4953,"Alice Smith",1],[4954,"Alice Smith",0],[4955,"Alice Smith",3],[4951,"Bob Johnson",3]]],[992,"Comment nr. 992",[[4956,"Alice Smith",1],[4957,"Alice Smith",2],[4959,"Alice Smith",0],[4960,"Alice Smith",3],[4958,"Bob Johnson",0]]],[993,"Comment nr. 993",[[4963,"Alice Smith",1],[4961,"Bob Johnson",0],[4962,"Bob Johnson",0],[4964,"Bob Johnson",0],[4965,"Bob Johnson",4]]],[994,"Comment nr. 994",[[4966,"Alice Smith",2],[4968,"Alice Smith",3],[4969,"Alice Smith",1],[4967,"Bob Johnson",4],[4970,"Bob Johnson",2]]],[995,"Comment nr. 995",[[4973,"Alice Smith",4],[4971,"Bob Johnson",4],[4972,"Bob Johnson",4],[4974,"Bob Johnson",3],[4975,"Bob Johnson",4]]],[996,"Comment nr. 996",[[4976,"Alice Smith",0],[4977,"Alice Smith",3],[4978,"Alice Smith",3],[4979,"Alice Smith",3],[4980,"Alice Smith",2]]],[997,"Comment nr. 997",[[4981,"Alice Smith",4],[4982,"Alice Smith",3],[4985,"Alice Smith",1],[4983,"Bob Johnson",4],[4984,"Bob Johnson",1]]],[998,"Comment nr. 998",[[4987,"Alice Smith",1],[4988,"Alice Smith",4],[4990,"Alice Smith",4],[4986,"Bob Johnson",2],[4989,"Bob Johnson",0]]],[999,"Comment nr. 999",[[4991,"Alice Smith",4],[4993,"Alice Smith",3],[4994,"Alice Smith",4],[4992,"Bob Johnson",4],[4995,"Bob Johnson",0]]],[1000,"Comment nr. 1000",[[4996,"Alice Smith",0],[4999,"Alice Smith",0],[4997,"Bob Johnson",2],[4998,"Bob Johnson",1],[5000,"Bob Johnson",3]]]] | [[1,"Tag nr. 1"],[2,"Tag nr. 2"],[3,"Tag nr. 3"],[4,"Tag nr. 4"],[5,"Tag nr. 5"],[6,"Tag nr. 6"],[7,"Tag nr. 7"],[8,"Tag nr. 8"],[9,"Tag nr. 9"],[10,"Tag nr. 10"]] |
The execution plan also confirms that only 50 records are returned to the JDBC Driver, and the most memory intensive operation is a HASH JOIN that consumes less than 1 MB, which is way lower than the previous query than needed 4 times more for sorting the records:
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | A-Rows | Used-Mem | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50 | | | 1 | SORT GROUP BY | | 1000 | 2048 (0)| |* 2 | HASH JOIN OUTER | | 5000 | 965K (0)| | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_VOTE | 5000 | | |* 4 | INDEX RANGE SCAN | IDX_USER_VOTE_POST_COMMENT_ID | 5000 | | | 5 | TABLE ACCESS FULL | USER | 2000 | | | 6 | SORT GROUP BY | | 50 | 8192 (0)| | 7 | TABLE ACCESS BY INDEX ROWID BATCHED | POST_COMMENT | 1000 | | |* 8 | INDEX RANGE SCAN | IDX_POST_COMMENT_POST_ID | 1000 | | | 9 | SORT GROUP BY | | 50 | 2048 (0)| | 10 | NESTED LOOPS | | 500 | | | 11 | NESTED LOOPS | | 25000 | | | 12 | TABLE ACCESS FULL | TAG | 500 | | |* 13 | INDEX RANGE SCAN | IDX_POST_TAG_TAG_ID | 25000 | | |* 14 | TABLE ACCESS BY INDEX ROWID | POST_TAG | 500 | | | 15 | SORT ORDER BY | | 50 | 340K (0)| |* 16 | FILTER | | 50 | | |* 17 | TABLE ACCESS FULL | POST | 50 | | ---------------------------------------------------------------------------------------------------
Cool, right?
I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
The MULTISET operator is very convenient when we want to fetch multiple to-many relationships and also aggregate the results into a hierarchical structure.
Not only can we get a much more efficient SQL query when using MULTISET, but the jOOQ query is type-safe. Therefore, a badly written query will fail at compile time rather than having to wait for the integration test to catch the issue.
If you are worried about the query complexity of aggerating data on the database side, then check out this article, which measures several alternatives and shows that the MULTISET operator performs better than the Cartesian Product alternative.
This research was funded by Data Geekery GmbH and conducted in accordance with the blog ethics policy.
While the article was written independently and reflects entirely my opinions and conclusions, the amount of work involved in making this article happen was compensated by Data Geekery.
