6 Replies Latest reply on May 10, 2004 2:33 PM by sesques

    CMP referenced collection

    Oleg Newbie

      I've got 1-to-N relation from User to License CMP beans.

      When requesting licenses collection from User beans Jboss performs the folloing SQL queries:
      1) SELECT LICENSE_ID FROM LICENSE WHERE (OWNER_USER_ID=?)
      2) SELECT LICENSE_ID, ... FROM LICENSE WHERE (LICENSE_ID=?) OR (LICENSE_ID=?) OR etc..

      The problem is my DB2 8.1 hangs up performing second query in case WHERE clause consist of more then 341 OR statements.

      My question is is there a way to configure JBOSS to use the query like
      3) SELECT LICENSE_ID, ... FROM LICENSE WHERE (OWNER_USER_ID=?)
      i.e. use one statement insted of two separate?

      Any other thoughts are welcome.

        • 1. Re: CMP referenced collection
          ithehorrible Newbie

          As far as I know you can't specify multiple columns as you're referring to the entire entity, so you should use:

          SELECT OBJECT(lic) FROM LICENSE lic WHERE ...
          


          • 2. Re: CMP referenced collection
            sesques Novice

            Hi gold,

            I think you can just make JBoss load the entity's one by one by setting the relation read-ahead strategy to "on-load".
            Otherwise, define your own finder in which you can do exactly what you suggest.

            Pascal

            • 3. Re: CMP referenced collection
              sesques Novice

              Perhaps try also to limit the page-size (within the relation read-ahead tag) : this is the max number of entities loaded in a single read. This will limit the query size BTW.

              Pascal


              • 4. Re: CMP referenced collection
                Oleg Newbie

                 

                "sesques" wrote:
                Perhaps try also to limit the page-size (within the relation read-ahead tag) : this is the max number of entities loaded in a single read. This will limit the query size BTW.

                Pascal


                Well, Pascal, it works. Thank you.

                Actually, I added <read-ahead> section to <defa ults> section of my jbosscmp-jdbc.xml. This completelly solves such issue with DB2.

                PS. Own finder is a most evident solution, but it rater a hot fix then a solid design :)

                • 5. Re: CMP referenced collection
                  ebrain13 Newbie

                  Hi Pascal,

                  Specifying the read-ahead in the jbosscmp-jdbc.xml fixed the problem, what actually is wrong with the query generation ?

                  thanks,
                  -vikram.

                  • 6. Re: CMP referenced collection
                    sesques Novice

                    Hi vikram,

                    I'm not in the JBoss gurus confidence, but it seems that the implementation of the CMP engine is just like this.
                    I think the fisrt query is generated by the CMR collection handler (just querying id's) and the second by the persistence engine which collects all the CMP fields for each entity. The whole optimization here is to factorize all the entity beans in a single query using (id=?) or (id=?) and so on. But the CMP engine seems not to be aware of the originated CMR.

                    Actually, the best way (means the most performant) to collect "entities in relation with" is to write your own finder with the "one entity" id as parameter.

                    If we have some chance, the JBoss CMP guru (Alex) will post his advice here.

                    Pascal