5 Replies Latest reply on Jul 25, 2003 9:34 AM by mgariepy

    Relationship and on-find strategy

    lbroudoux

      Hello,

      I'm using JBoss-3.0.4 and I'd like to set a "on-find" read-ahead strategy to my CMR field. I have bought and read the JBossCMP doco and try to reproduce the "gangster-hangout" optimized loading with a 1-Many relationship.

      My use-case is the following : I have to retrieve the permissions/roles of a user during loggin. The relationship is between permission and role (getRole() method into PermissionBean).

      Without optimized loading the queries are (my user has 5 roles):
      [User.findByPrimaryKey] Executing SQL: SELECT s_id, s_password, s_lastname, s_firstname, s_mail, FROM users WHERE s_id=?
      [Permission.findByUserId] Executing SQL: SELECT DISTINCT t0_h.s_id, t0_h.s_reference, t0_h.s_user_fk, t0_h.s_role_fk FROM permissions t0_h WHERE t0_h.s_user_fk = ?
      [Role] Executing SQL: SELECT s_label, s_description FROM roles WHERE (s_id=?)
      [Role] Executing SQL: SELECT s_label, s_description FROM roles WHERE (s_id=?)
      [Role] Executing SQL: SELECT s_label, s_description FROM roles WHERE (s_id=?)
      [Role] Executing SQL: SELECT s_label, s_description FROM roles WHERE (s_id=?)
      [Role] Executing SQL: SELECT s_label, s_description FROM roles WHERE (s_id=?)


      From what I've seen in the book, this loading scenario can become :
      [User.findByPrimaryKey] Executing SQL: SELECT s_id, s_password, s_lastname, s_firstname, s_mail, FROM users WHERE s_id=?
      [Permission.findByUserId] Executing SQL: SELECT DISTINCT t0_h.s_id, t0_h.s_reference, t0_h.s_user_fk, t0_h.s_role_fk FROM permissions t0_h WHERE t0_h.s_user_fk = ?
      [Role] Executing SQL: SELECT s_id, s_label, s_description FROM roles WHERE (s_id=?) OR (s_id=?) OR (s_id=?) OR (s_id=?) OR (s_id=?)

      when using a "on-find" read-ahead strategy with a page-size >= 5.

      I've modifed the jbosscmp-jdbc.xml descriptor of my app that way :

      <ejb-relation>
      <ejb-relation-name>Role-OneToMany-Permissions-Uni</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Permission-refers-Role</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields/>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Role-has-Permissions</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>id</field-name>
      <column-name>s_role_fk</column-name>
      </key-field>
      </key-fields>
      <read-ahead>
      on-find
      <page-size>8</page-size>
      <eager-load-group>base</eager-load-group>
      </read-ahead>

      </ejb-relationship-role>
      </ejb-relation>
      where the "base" load-group contains all the attributes of Role.
      Am I missing or misunderstanding something ?

      Thanks a lot for your help.
      Regards,
      --
      Laurent

        • 1. Re: Relationship and on-find strategy
          lbroudoux

          Hi,

          I forgot to say in the previous post that it doesn't work with the modification I've made. I've test the same app on JBoss-3.2.1 and have the same results ...

          I have also checked the transaction attribute of the session bean method making these calls and it is "Required" (so that the JBossCMP preload cache can be used) ...

          Has someone a clue on what's wrong ?
          Regards,
          --
          Laurent

          • 2. Re: Relationship and on-find strategy
            mgariepy

            Laurent,

            I'm having the same problem. What database/JDBC driver are you using? I'm working with MS SQL2000 & MS JDBC driver.

            In a nutshell, I've got two related tables (AwDocument & AwNode) linked by nodeId. The method AwDocument.findAllByNodeId works fine for small numbers of rows, but I'm trying to get it to perform better since we're going to be retreiving 2-7K rows per query.

            I too, have the most recent docs and I'm running JBoss 3.2.1. I've very carefully followed the steps in Chapter 11 regards updating the DD and keep getting the error:

            2003-07-23 16:12:43,908 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.AwDocument.findAllByNodeId] Executing SQL: SELECT t0_d.docId FROM AwDocument t0_d, AwNode t1_d_awNode WHERE (t1_d_awNode.nodeId = ?) AND (t0_d.awNode=t1_d_awNode.nodeId)
            2003-07-23 16:12:44,558 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.AwDocument.findAllByNodeId] Find failed
            java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid object name 'AwDocument'

            Invalid object name is kinda screwy ... works fine if I take out the <read-ahead> in the DD.

            Are you running MS SQL2000 also? Just wondering if perhaps Microsoft's JBDC driver can't handle the query?

            Thanks,

            Mark

            • 3. Re: Relationship and on-find strategy
              lbroudoux

              Hello,

              I have tested my app with MySQL (mm.mysql drivers) and Oracle 8 (oracle drivers) and have the same results.

              I think that our problems are slightly different because the read-ahead optimizations that I'd like to setup is on a relationship loading query and not on a finder query.

              Anyway, your SQL query just seems to be correct. Have you tested it through Enterprise Manager ? What query do you get when removing the read-ahead attribute in the DD ?

              Is there a JBossCMP guru round that can help us ?
              Regards,
              --
              Laurent

              • 4. Re: Relationship and on-find strategy
                mgariepy

                Laurent,

                I'll be testing again today. My Entity EJBs have realationships setup as well. I also played around with creating a "read-only" EJB without the relationship mapping ... it makes the query quicker.

                One big thing is to set the Commit option to "A" in the conf/standardjboss.xml file for the CMP Beans. This option speeds large queries by many magnitudes provided that Jboss and your Entity EJBs are the only ones updating the database.

                I'm running the JBoss 3.21 w/ tomcat 4.1.24 bundle, MS SQL 2000 as the DB. I'm having problems getting the load-groups and the on-load or on-find strategies to work correctly. The Deployment Descriptors validate againtst the dtd, the Entity EJBs deploy correctly, etc. , but I get the errors in the log whenever I run the finder().

                I'm running through some more tests to today and I'll keep you posted if I get any positive results.

                • 5. Re: Relationship and on-find strategy
                  mgariepy

                  Oh yeah ... the query works fine whenever I remove the read-ahead attribute in the DD. I'm trying to make it run faster ... so I'd think that adding the correct attributes in the DD should do the trick, but it causes the finder to fail instead.

                  Regards the JBoss CMP guru ... the Jboss Group offers services, but it pretty expensive. I think the least expensive route starts at $5,000 US.