2013/8/9 Stéphane Cl <[email protected]> > > Huh, I haven't given this proper thought. Yes, you're probably right. The >> show-case is probably good for linear "graphs" only, not for graphs with >> cartesian products in them. I guess that's where LLBLGen shines, as the >> proprietary query extensions holds all the relevant meta-data to know how >> to "untangle" the cartesian product between the >1 joined relationships. >> >> > Exactly... Efficiently loading graphs often requires multiple queries > (eg: 1 for the base entity, and 1 for each of the branches, except > one-to-one that can be obtained through joins). The base idea is that > eliminating duplicates from large resultsets isn't going to be that much > faster than issuing a second SQL query, not talking about network traffic > and memory bloat. IN (....) statement or EXISTS subqueries are usually well > optimized in modern rdbms. > > I am not sure that llblgen actually used joins for 1:1 and 0:1 at the time > I was using it, it followed the one branch -> one query principle which was > already good enough in most use cases, so much better out of the box than > any N+1. >
Yes, this solution turns N+1 into 1+1, which is acceptable. I'd have to think about nesting pre-fetch paths, though, i.e. when a pre-fetch path contains another pre-fetch path... Probably, rather than designing some wicked SQL syntax extension for this, there should be simple API methods, which can fetch "parents" or "children" in a single query, based on a Result<R>. This would align well with the existing - http://www.jooq.org/javadoc/latest/org/jooq/TableRecord.html#fetchParent(org.jooq.ForeignKey) - http://www.jooq.org/javadoc/latest/org/jooq/UpdatableRecord.html#fetchChildren(org.jooq.ForeignKey) Maybe Result.fetchParents(ForeignKey) and Result.fetchChildren(ForeignKey) ? In-memory join operations could then be done through Result.intoGroups() methods - http://www.jooq.org/javadoc/latest/org/jooq/Result.html#intoGroups(org.jooq.Field) I'll register this idea as #2686: https://github.com/jOOQ/jOOQ/issues/2686 -- 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]. For more options, visit https://groups.google.com/groups/opt_out.
