I'm not sure how deep our worse-case tree is, but we probably could get by with joining all tables. It's really the width of the tree that would prohibit joining everything. I agree that using an outer join on all tables be more performant for certain types of tables with small numbers of tables -- this is the case for most of our vertical inheritance -- five to ten tables, and I like the idea of being able to do so.
We should probably come up with some kind of limit on how many tables (or maybe columns) we want to fetch at one time, and then try to pick the right strategy based on that. I'd say that we would try to always maximize the amount of data pulled in for each strategy. So it sounds right to me. I'd say we'd want to avoid the "without joins" query as much as possible. Do we need to fetch the id/discriminator from the root table as a separate query? I guess we do for the cases where we're not fetching a leaf class. But for a leaf class, we could fetch all records in one query. I suppose we should start with the worse-case scenario and that will cover every other type of query as a first pass. I see that as 1) fetch the root table to determine the discriminator value. (subclass unknown worse case) 2) fetch each sub table individually (depth too large worse case) Then we could consider optimizations: a) leaf subclass is known -- one query can fetch root and subtables if depth is not too deep b) depth is small -- one query can fetch all subtables (and possibly root if leaf subclass) c) total tables involved is small -- can do an outer join of all tables and sort things out in memory I didn't realize how complicated this was going to get :) I think it's probably a good idea to be able to specify a fetch strategy, at least at first -- might be needed to get around limitations or bugs in our fetch strategies. But I think we should also be able to derive a best-case fetch strategy based on the model as well. On Mon, May 31, 2010 at 8:29 AM, Andrus Adamchik <and...@objectstyle.org> wrote: > Great. this is the kind of feedback I was looking for :-) > > On May 31, 2010, at 8:17 AM, Mike Kienenberger wrote: >> >> What you're suggesting could work, but we have at least one very deep >> and wide inheritance tree in our current app, and the performance hit >> of joining more than a hundred tables (so far) unnecessarily would >> kill us. > > So how would you do a fetch based on discriminator column approach? I can > think of a 2 step process: > > 1. Fetch ID's + discriminator columns from the root table. > 2. Fully resolve objects, either doing joins only for the subset of tables > that appeared in result in #1, or doing a separate query (without joins) for > each subclass table matching the set of IDs. > > Does that sound right? > > Also I am sort of in favor of my original discriminator-free approach for > smaller hierarchies, so wonder if a fetch strategy can be something > specified for a given super-entity and/or query. > > Andrus > >
