Hi Garret, I'm sorry for the delay. I was on a quick time off on Kos island, now more than ever ready to answer questions again :)
2014-07-03 21:50 GMT+02:00 Garret Wilson <[email protected]>: > On Wednesday, July 2, 2014 11:42:40 PM UTC-7, Lukas Eder wrote: >> >> >> 2014-07-01 16:57 GMT+02:00 Garret Wilson <[email protected]>: >> >> Hi, Lukas. I'm not sure you understood what I meant by "duplicate >>> information", >>> >> >> I'm pretty sure I did: You're repeating "parent" (e.g. author) record >> data for each "child" (e.g. book) >> >> >>> and what I meant by "multiple queries... less efficient". >>> >> >> Hmm, what could you mean other than N+1 querying? :) >> > > > Ah, pardon me, so you did understand. :) I had assumed that there must be > a nice and tidy jOOQ solution, and simply I must not have been explaining > myself clearly. I see now that this is a larger, common issue---this is why > people use Hibernate, monster that it is. > Hibernate's (JPA's) main reason for being is precisely to solve this kind of mapping problem. In my opinion, the mapping problem only happens in situations where the Java domain model is essential to an application and to its business logic. In more SQL-centric (relational-centric) applications, people are usually happy to keep operating on flat tabular data structures. This is summarised on this website here: http://www.hibernate-alternative.com Of course, this doesn't help solve your concrete problem, but it's always good to think about these things in a broader context. ORM is still an unsolved problem (maybe because it cannot be solved). If you're interested in further (academic) reading on the subject, I recommend reading Erik Meijer's and Gavin Bierman's paper "A co-Relational Model of Data for Large Shared Data Banks" http://queue.acm.org/detail.cfm?id=1961297 > > >> ... You have these options from my point of view: >> >> 1. Use joins and implement that grouping yourself, but factor out common >> parts. Right now, you have a lot of redundant logic because you're >> explicitly tying stuff to the AUTHOR / BOOK relation. I'm sure there are >> elements that can be factored out generically. jOOQ provides you with >> foreign key meta information >> > > Yeah, I can definitely refactor some of the stuff above. I was just > looking for some help from jOOQ. > > >> 2. Use joins and use jOOQ's existing Result.intoGroups() methods. It'll >> help doing the "grouping" (which you currently do manually, and iteratively) >> > > That's interesting, but from a cursory examination (pardon the > unintentional pun) > Clever ;) > it doesn't seem to support cursors, > Hmm, maybe you'd like to elaborate on that? Do you think there is a missing feature? org.jooq.Cursor (obtained from fetchLazy()) is in fact a reference to an open JDBC ResultSet (CONCUR_READ_ONLY, TYPE_FORWARD_ONLY). The ResultSet itself models a cursor on the database level, which materialises new records each time it is forwarded using ResultSet.next(). There may be databases that are able to optimise data transfer through the wire to avoid transferring redundant information originating from joins, but by default, I don't think you can optimise this because your JOIN will simply denormalise your data to produce this redundancy. That's what JOINs do :) > meaning I'm going to get a humongous map of all those records, each of > them with duplicate, duplicate, duplicate information for each of the items > in the parent relationship. > Yes, that's how things usually work with joins. I'm not sure if I have mentioned this before: We're hoping to be able to introduce a new synthetic join that works like an aggregation to produce nested tables. Simply put, the output should be: // With Java collections Map<AuthorRecord, List<BookRecord>> // With jOOQ Records Result<Record2<AuthorRecord, Result<BookRecord>>> At some point, we'll support this kind of feature, but right now, we haven't done all the thinking about the myriad edge cases that such synthetic table nesting will introduce. > >> 3. Use several queries instead of joins, but don't fetch each author for >> each book. Fetch all books first, and then fetch *ALL* authors for *ALL* >> books >> > > Yes, that would be cleaner, but I would guess that's not as efficient as a > join. (I am *not* an SQL query expert.) > Not necessarily. For several reasons: 1. The database may still hold the data in its buffer caches, which allows it to access the data quickly 2. Both consecutive queries are very likely to be able to profit from the same indexes 3. You can avoid transferring all that duplicate data over the wire from your database to JDBC > > The "N+1 problem" seems to be two separate issues: > > > 1. *How to nicely map stuff from joins into objects.* This is where > Hibernate is light years ahead, I guess. But I would have thought that jOOQ > would give me some utilities to help with this. You did give some links to > some mappers, which I will look into, but I didn't see any examples > offhand. > 2. *How to efficiently query data for a hierarchy of objects.* From > reading a tiny bit, this seems like a general problem of relational > databases, one that Hibernate may not have solved, either. > > Hibernate / JPA could only tackle this problem because they invented a *new* query language that only supports about 20% of the features that SQL has. It is (probably) impossible to map arbitrary joins to objects. Or vice versa. Again, Erik Meijer's paper sheds some light on this duality between relational models and object models. > Thanks for all the links. I'll do some more research and see where that > gets me. > Great! Let us know where your research is leading you. Cheers 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]. For more options, visit https://groups.google.com/d/optout.
