Perhaps, you should have asked, before refactoring ;) This isn't a software design question, but just a simple mistake
On Fri, Oct 3, 2025 at 5:07 PM 'Bernd Huber' via jOOQ User Group < [email protected]> wrote: > Hello guys, > > jOOQ provides great power, and with great power comes great responsibility > :) > > I happily implemented a Repository Abstraction for my Team to use, but it > currently shows it's limits that result in bad performance, which is mainly > resulting from my inability to understand specific performance > implications, when writing SQL for my target database (mariadb). > > I don't have a specific question but seek a bit for a guide / cookbook for > writing performant SQL (via jOOQ DSL) for the business use case of a > Repository with remote-paginated query-functionality (filter, sort, keyset > pagination, ...). > > My initial abstraction expected the users to write everything in one big > query, that uses multisets and joins. The joins are needed for filtering > etc, and the multisets to get the complex results. For example: > > > *Repository Query Abstraction - Part 1 (Performance Problems)* > @Dependent public class TopicRepository extends AbstractRepository<TopicDTO, > UUID> { @Inject TopicMapper topicMapper; @Inject TopicLangMapper > topicLangMapper; @Inject TopicTagLangMapper topicTagLangMapper; @Inject > LabelMapper labelMapper; @Inject LabelLangMapper labelLangMapper; @Inject > UserTopicAnswerMapper userTopicAnswerMapper; @Inject > UserTopicAnswerLangMapper userTopicAnswerLangMapper; private boolean > onlyUntagged = false; // Default constructor for CDI public > TopicRepository() { super(TOPIC.TOPICID); } public TopicRepository > onlyUntagged(boolean onlyUntagged) { this.onlyUntagged = onlyUntagged; > return this; } @Override protected SelectFinalStep<Record1<TopicDTO>> > prepareQuery(XQuery query) throws InvalidDataException { final > QueryJooqMapper queryJooqMapper = new QueryJooqMapper(query, TOPIC) > .addMappableFields(TOPIC) .addMappableFields(TOPICLANG) > .addMappableFields(TOPICLABEL) .addMappableFields(LABEL) > .addMappableFields(LABELLANG) .addMappableFields(USER) > .addMappableFields(TOPICTAGLANG) .addMappableFields(USERTOPICANSWER) > .addMappableFields(USERTOPICANSWERLANG); UUID userId = null; if > (this.request() > instanceof UserRequestContext) { userId = ((UserRequestContext) > this.request()).getUserId(); > } final Condition onlyUntaggedCondition = TOPIC.TAGSGENERATEDAT.isNull(); > return dsl() .select( row( TOPIC, multiset( select( LABEL, multiset( > select(LABELLANG) .from(LABELLANG) > .where(LABELLANG.LABELID.eq(LABEL.LABELID)) ).convertFrom(r -> r.map(rec -> > labelLangMapper.map(rec))) ).from(TOPICLABEL) > .join(LABEL).on(LABEL.LABELID.eq(TOPICLABEL.LABELID)) > .where(TOPICLABEL.TOPICID.eq(TOPIC.TOPICID)) ).as("multisetLabels") > .convertFrom(r -> r.map(rec -> labelMapper.map(rec))), multiset( > select(TOPICTAGLANG) .from(TOPICTAGLANG) > .where(TOPICTAGLANG.TOPICID.eq(TOPIC.TOPICID)) ).as("multisetTags") > .convertFrom(r -> r.map(rec -> topicTagLangMapper.map(rec))), multiset( > select(TOPICLANG) .from(TOPICLANG) > .where(TOPICLANG.TOPICID.eq(TOPIC.TOPICID)) ).as("multisetLangs") > .convertFrom(r -> r.map(rec -> topicLangMapper.map(rec))), row( > USERTOPICANSWER, multiset( select(USERTOPICANSWERLANG) > .from(USERTOPICANSWERLANG) > .where(USERTOPICANSWERLANG.ANSWERID.eq(USERTOPICANSWER.ANSWERID)) > ).convertFrom(r -> r.map(rec -> userTopicAnswerLangMapper.map(rec))) > ).convertFrom(r -> userTopicAnswerMapper.mapOrNull(r)) ).convertFrom(rec -> > topicMapper.map(rec)) ) .from(TOPIC) > .join(TOPICLANG).on(TOPICLANG.TOPICID.eq(TOPIC.TOPICID)) > .leftJoin(USERTOPICANSWER).on(USERTOPICANSWER.TOPICID.eq(TOPIC.TOPICID).and(USERTOPICANSWER.USERID.eq(userId))) > .where(queryJooqMapper.getFilters()) .and(onlyUntagged ? > onlyUntaggedCondition : DSL.trueCondition()) .groupBy(TOPIC.TOPICID) > .orderBy(queryJooqMapper.getSorter()) .offset(queryJooqMapper.getOffset()) > .limit(queryJooqMapper.getLimit()); } } > > After testing a bit, i found that following two points are mainly > resulting in performance problems: > - many unbound joins (meaning that a join is made without further > filtering on the joined table) > - many multisets that mariadb does not seem to apply at the very end on > the already filtered results (unclear) > Why are you joining *and* collecting the child table in a subquery? Do either one thing or the other thing, but not both. When you look at any nested collection examples from jOOQ, you'll always find either one of these three approaches: 1. multiset() subqueries and (usually) no joins in the outermost query. If there are joins, they're typically to-one joins, not to-many joins 2. multsetAgg() on queries with only one to-many join path, not several branching paths 3. Collector based deduplicating and nesting of results on queries with only one to-many join path, not several branching paths In general (not in this case), if you're joining only one path of child tables, joins and multiset are indeed alternatives, and I've explored performance characteristics here: https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/ But you shouldn't really join two child tables to one parent table in SQL (independently of jOOQ), or you're getting a cartesian product between TOPICLANG and USERTOPICANSER. Try it with a simple example, assuming PostgreSQL: with topic (id) as (values (1),(2),(3)), topic_lang (id, lang) as (values (1, 'en'), (1, 'de'), (2, 'en'), (3, 'de' )), topic_user (id, name) as (values (1, 'Jon'), (1, 'Doe'), (2, 'Jon'), (2, 'Doe')) select * from topic as t join topic_lang as tl on t.id = tl.id left join topic_user as tu on t.id = tu.id This results in: |id |id |lang|id |name| |---|---|----|---|----| |1 |1 |de |1 |Jon | |1 |1 |en |1 |Jon | |1 |1 |de |1 |Doe | |1 |1 |en |1 |Doe | |2 |2 |en |2 |Jon | |2 |2 |en |2 |Doe | |3 |3 |de | | | Can you see the cartesian product for topic 1? There are a total of 2x2 rows. 2 for the languages x 2 for the users. This can result in a ton of rows, which are all unnecessary, because you're correlating each of these relations again in multiset derived tables *on each row*! I hope this helps, Lukas -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4%3DHjZBy%2BcZeh1oAaG_wxT61CPn4oonb%2BH9Hz3Q3vjAug%40mail.gmail.com.
