4 Replies Latest reply on Jan 8, 2003 4:27 AM by Markus Menner

    CMR Many to One read-ahead

    Andrew May Newbie

      Is it possible to have read-ahead on a M-1 relationship? I've successfully used it for a 1-M relationship (with a nice performance increase), but similar configuration didn't have any effect on a M-1 relationship.

      I'm working on a system with 3 main entities: Identity, Licence and Resource, with a 1-M relationship between Identity and Licence, and a M-1 relationship between Licence and Resource.

      When I find all the licences for a particular identity I'm looking up some of the details of the attached resource. Where there's 100's of licences I'm seeing a seperate select for each distinct resource (in most cases each licence is for a different resource), which takes a while.

      I've tried this type of config:

      <ejb-relation>
      <ejb-relation-name>Resource-Licence</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Resource-for-Licences</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>id</field-name>
      <column-name>resourceid</column-name>
      </key-field>
      </key-fields>
      <read-ahead>
      on-find
      <page-size>50</page-size>
      <eager-load-group>*</eager-load-group>
      </read-ahead>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Licence-for-Resource</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>

      (and putting it on the other side of the relationship just in case), but not seen any different in the queries being performed.

      Any ideas?

      -Andrew

        • 1. Re: CMR Many to One read-ahead
          Markus Menner Newbie

          I have the same problem, did you find a solution meanwhile ?
          TX
          Markus

          • 2. Re: CMR Many to One read-ahead
            Markus Menner Newbie

            After looking at the SQL statments in my server.log, I noticed, that this problem has nothing to do with the fact of a relation being 1-M or M-1, but that read-ahead (with the default load group) seems to work only on the first layer of related entities.

            Related entities of the related entities seem to have a problem with read-ahead: for each entity there is a single SQL-statement.

            Is this true ? Is it a bug, or has it to do with load-group definitions ?

            BTW: I bought the CMP-docs, but I feel that the behavior concerning optimized loading is not explained enough.

            Markus

            • 3. Re: CMR Many to One read-ahead
              Markus Menner Newbie

              I tried to debug the ReadAhead code without success yet.

              The scenario is the following:

              Three entities A,B,C.

              Relationships:

              A-B 1-N (foreing key)
              B-C N-1 (foreing key)

              Default strategy: on-find.

              Finder is issued on A (on-load strategy).

              B-entities read-ahead correctly (one SQL-query).
              C-entity accessor leads to an SQL-query for every B-entity.

              IMO C entities should be read-ahead too !?

              Is this the "normal behavior" or not ?

              I have the CMP-docs, but it did not help.

              Thanx
              Markus

              • 4. Re: CMR Many to One read-ahead
                Markus Menner Newbie

                After analyzing the code in JDBCLoadRelationCommand, JDBCLoadEntityCommand and ReadAheadCache (JBoss 3.2 beta3), I think, that I can explain the the behavior.

                Read-ahead works only on the N-side of a 1-N foreing-key based relationship (I hope, that I am right :-)).

                The (simplified) principle is the following:

                When a finder is called, collect the resulting primary keys in bunches of page-size (lists) and associate each key in a list with the whole list.

                When a related entity shall be loaded (on-find), distinguish whether it is -N related or -1 related.

                When it is -1 related perform a JDBCLoadEntityCommand.

                When it is -N related perform a JDBCLoadRelationCommand:

                Look if there is a list, for the primary key, for which the related entities shall be loaded.

                If there is one, load not only the related entities of the actual entity, but also those of the entities in the found list.

                If I am right, you have a very bad performance (in the scenario described earlier in this thread), especially if you have to use commit-option B.

                So, there is a need to maintain additional lists for N-1 foreing-key relationships. Is this planned ?

                Thanx
                Markus