Re: Vertical inheritance

From: Mike Kienenberger (mkienen..mail.com)
Date: Mon May 31 2010 - 13:01:37 UTC

  • Next message: Andrus Adamchik: "Re: Vertical inheritance"

    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