2 Replies Latest reply on Mar 25, 2004 4:02 PM by mkyaj

    CMP - repeated sql condition

    Petr Fischer Newbie

      This is from selver.log:
      2004-03-23 17:25:26,205 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.FundPerfPeriodEJB] Executing SQL: SELECT ID, NAME, PERIOD_FOR_VALUE FROM FW_PERIODS WHERE (ID=?) OR (ID=?) OR (ID=?) OR (ID=?) OR (ID=?) OR (ID=?) OR (ID=?) OR (ID=?) OR (ID=?)

      Why the part "(ID = ?)" is repeated many times? No problem in this query, but in another query, "where" sql condition is repeated 3876x and my FireBird server resets connection (too long query).

      I am confused (sorry for my bad English). Thanks for help.
      JBoss 3.2.3, JDK 1.4.2_04, Win2000, FireBird 1.0

        • 1. Re: CMP - repeated sql condition
          Benjamin Walstrum Newbie

          I have seen this before, and generally it is related to a finder that returns a large number of records. It seems to be that JBoss will do two queries when you do a finder - first one to get the primary keys of the records that match your criteria, then one to actually fetch the row data for the rows that match.

          That behavior may be configurable somewhere, but I don't know where. One option is to use the LIMIT JBOSS-QL in your finder which will keep the query down to a managable number of records. Hopefully someone else will be able to tell you how to tweak the ways in which JBoss does its CMP finders for large result sets, because I am not familiar with it.



          • 2. Re: CMP - repeated sql condition
            mkyaj Newbie

            Hi ,

            As Ben said by default the JBOSSCMP will load only the PKs for the first time the finder method is fired. The actual entity will be loaded when the client calls any business method on that entity.

            To reduce these number of queries and to reduce the amount of data for that finder method we can configure load-groups for each query or entity and use either on-find(all the data will be loaded in a single query) and on-load(you can configure the number of entities to be loaded at a time) read-ahead methods in jbosscmp-jdbc.xml.

            The following case is for a CMR of one-many relation. In CMR also when u call the getCMR it will just load the PKs of the CMR fields like SELECT ID FROM FW_PERIODS WHERE (FW_FK=?)

            When you access the first CMR entity then JBOSSCMP will load all the CMR field entities.

            I dont know how can we control loading the CMR entities.