5 Replies Latest reply on Apr 15, 2002 12:21 PM by Dain Sundstrom

    Redundant queries in JBossCMP

    Marcus Brito Novice

      [This is about JBoss HEAD 3.0.0beta2(200203221637)]

      Hello, folks. I noticed my last post about JBossCMP performance has gone unanswered. Alright, I've solved most of my problems by now.

      Anyway, it seems there is still a problem with JBossCMP. I turned on-find read-ahead for all my entities so I can speed up some findAll() queries. However, the result was not what I expected. I get the following in the log file:

      DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.Divisao.findAll] Executing SQL: SELECT t0_o.DIVISAO_ID, t0_o.DESCRICAO, t0_o.FILIAL_ID, t0_o.DATASINC FROM DIVISOES t0_o ORDER BY t0_o.DESCRICAO ASC

      And I thought: "Great, my whole entity is being loaded on the finder command. There is no need for further SQL". It seems I'm wrong, because the 5 or so following log lines are like this one:

      DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Divisao] Executing SQL: SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?) OR (DIVISAO_ID=?)

      And now I'm thinking: why JBoss is issuing all these individual load commands since all my entities were already loaded on the finder command? Can I do something to prevent this? My goal is to tune DB acess to the maximum permitted. I don't want JBoss issuing any unecessary queries.

      This entity I discuss here my simplest entity. I used it just to have a small, comprehensible example. I've much larger entities where these extra SQL queries are quite a hit on performance.

      Just te reaffirm: DIVISAO_ID, DESCRICAO, FILIAL_ID and DATASINC are fields from the Divisao CMP Entity bean. All of them. There aren't any more fields.

      Here is the findAll() JBoss-QL: "SELECT OBJECT(o) FROM Divisao o ORDER BY o.descricao"

      My standardjbosscmp-jdbc.xml is configured with <read-ahead>on-find</read-ahead>. Everything else should be pretty much standard values. Any hints?

      I'm even thinking about tinkering in the jbosscmp source code if I get some assistance. Dain, could you provide me with some info to start hacking there?

        • 1. Re: Redundant queries in JBossCMP
          Marcus Brito Novice

          I installed p6spy here to get some concrete SQL and look at the result. It got scarier! JBossCMP is now repeating an extensive SQL command to load a single entity! What's happening? Here is the SQL issued by JBossCMP:

          SELECT t0_o.DIVISAO_ID, t0_o.DESCRICAO, t0_o.FILIAL_ID, t0_o.DATASINC FROM DIVISOES t0_o ORDER BY t0_o.DESCRICAO ASC

          This is issued on Divisao.findAll(). This should have loaded all the entities already. I got 9 entities an this table. However. take a look and the following 8 SQL statements:

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='5') OR (DIVISAO_ID='3') OR (DIVISAO_ID='6') OR (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='3') OR (DIVISAO_ID='6') OR (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='6') OR (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='9') OR (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='1') OR (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='2') OR (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='7') OR (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DIVISAO_ID,DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='4') OR (DIVISAO_ID='8')

          SELECT DESCRICAO, FILIAL_ID, DATASINC FROM DIVISOES WHERE (DIVISAO_ID='8')

          As you can see, there is one less OR clause on each statement. It seems that JBoss sucessfuly loads only a single entity at each statement, including the finder statement. This is nonsense. What's happening here?

          Now imagine what happens if my table contains 1000+ entities. And yes, I have other tables that will contain much more than this.

          • 2. Re: Redundant queries in JBossCMP
            David Jencks Master

            How many transactions are executing? What is your commit-option? If you have commit option B or C and each call is in a separate tx, and read-ahead is on, you will get what you see. If all calls are within one tx and/or commit option A is on, I would expect only one db call.

            • 3. Re: Redundant queries in JBossCMP
              Marcus Brito Novice

              I have CommitOption A, and everything is executed on a single transaction (I hope). Besides, all this SQL output is caused by a *single* call to Divisao.findAll(), so even with CommitOption B or C we shouldn't have all this SQL.

              • 4. Re: Redundant queries in JBossCMP
                Marcus Brito Novice

                An update: I've checked the code, added some log lines and discovered that this whole SQL is not generated by a single call to findAll(). Here is the calling code:

                Collection ifaces = divisaoHome.findAll();
                Iterator it = ifaces.iterator();
                
                while(it.hasNext())
                 result.add(((DivisaoLocal)it.next()).getDivisaoValue());
                

                Each time it.next() is called, one of the above SQL statements is executed. However, I still think that all entities should have been loaded in the findAll() call.

                Can someone point me to how can I avoid all these SQL statements?

                • 5. Re: Redundant queries in JBossCMP
                  Dain Sundstrom Master

                  Wrap you code in a single transaction.