It has been a while since jOOQ 3.15 was released, introducing its revolutionary standard SQL MULTISET emulation feature. In this article, I will be running benchmarks to compare the performance of different approaches to nesting to-many relationships with jOOQ.
First, let’s discuss the different queries that will be tested in the benchmark. We have two types of queries: one that double-nests child collections (DN) and one that nests two child collections in a single parent (MCC). These queries will be executed on the Sakila database.
Now let’s talk about the data set size. The benchmark will pull in either the entire data set or only a subset of it. The expectation is that a JOIN performs better on larger result sets, while nested loop joins tend to be better on smaller subsets.
The benchmark will test the following approaches for each query type and data set combination:
1. A single MULTISET query with its available emulations using XML, JSON, and JSONB.
2. A single JOIN query.
3. An approach that runs multiple queries and performs nesting in the client.
4. An N+1 query approach.
Now let’s go through each approach and its implementation.
1. Single MULTISET query (DN):
The query selects data from the database using jOOQ’s select statement and converts the result into nested records using the mapping function.
2. Single JOIN query (DN):
This query performs a JOIN operation on the necessary tables and transforms the flat result into a doubly nested collection using JDK Collectors.
3. Two queries merged in memory (DN):
This approach runs multiple queries to fetch the necessary data and performs nesting in the client. The fetched data is stored in intermediate data structures and then grouped and mapped to create the nested records.
4. N+1 queries (DN):
This approach executes multiple queries in a loop, fetching data one record at a time. Although not recommended, this approach is included for comparison.
Each approach is implemented using jOOQ’s API and is tested with different data set sizes. The full benchmark logic can be found at the end of the article.
In conclusion, the benchmark shows that jOOQ’s MULTISET emulation performs well in real-world scenarios, especially with small to medium-sized data sets. It outperforms the N+1 approach and is more readable and maintainable. However, for larger data sets, other approaches like multiple queries per nest level or a hash join/merge join may be more appropriate.
This benchmark provides insights for jOOQ users on when to use MULTISET and for ORM vendors on choosing the right approach for materializing object graphs.
(Note: The content has been rewritten with HTML tags preserved.)
Source link