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 <andru..bjectstyle.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
>
>
This archive was generated by hypermail 2.0.0 : Mon May 31 2010 - 13:02:27 UTC