2 Replies Latest reply on Jul 19, 2006 8:01 AM by Stefan Craatz

    Progressively decreasing query performance within transactio

    Andreas Zimmer Newbie

      We are evaluating EJB3 performance and observed a progressive performance downgrade during query operations within a transaction:

      1. Reading 20 simple EntityBean-POJOs in a single transaction, each by unique key, results in an average elapsed time of about 10 msecs per query operation (OK so far!)

      2. Reading 1000 simple EntityBean-POJOs the same way (identical code, just changing the number of unique key query calls), results in an average of over 130 msecs (!!) per query operation (factor 13 and sometimes even higher!).

      The negative progression is more or less directly related to the number of queries which are called during one transaction, i.E. for 500 queries, the average for one query is about 50-60 msecs (always the same query, just reading different entities).

      Having studied the "ejb-3_0-fr-spec-persistence.pdf" we found the possibility to manage the PersistenceContext by the application itself. So, when we changed the code to application managed PersistenceContext and cleared the EntityManager (em.clear()) after each query (detaching the query-resulting EntityBean-POJO immediately after it has been processed), the query time was finally constant, irrespective of the number of queries within the transaction.

      The EntityBean-POJO which was looked up by the query is a very simply and small Class with just a few attributes and relations, but just 4 String attributes have got a few characters allocated and none of the relations was filled. We therefore don't think that we had a memory related problem nor have we been able to identify any such memory related problem (memory parameters and indicators have been quite constant all the time).

      It looks like the PersistenceContext iteself creates very significant overhead as more Beans become attached (?). Though increasing overhead with a growing PersistenceContext may be ok, the concrete result seems not to be normal, particularly as we've always had quite constant response times in scenarios like this with EJB2 (despite an expectedly/normally growing EntityBean cache during such transactions).

      Any idea? We would hope that we don't need to manage the PersistenceContext on our own just to reach acceptable query performance in such simple scenarios.


        • 1. Re: Progressively decreasing query performance within transa
          Andreas Zimmer Newbie

          We've further investigated into this problem and finally found the reason for the bad query performance:

          The reason seems to be related with the FlushModeType.The default FlushModeType.AUTO requests the appserver to make all updates of a transaction visible to subsequent queries within the same transaction (ejb-3_0-fr-spec-persistence.pdf, chapter 3.6.2 Queries and Flush Mode). Exactly this seems to become a performance problem for JBoss as soon as the number of queried EntityBean-POJOs within a transaction starts growing in the PersistenceContext.

          When we either clear the EntityManager after each query operation within the transaction (em.clear(), which is not really feasible under many circumstances) OR when we set the FlushModeType.COMMIT (which is also NOT what we normally need, we expect to work with the EJB3 default FlushModeType.AUTO), the performance for queries within a transaction is constant (Performance OK, but NOT the required functionality with em.clear() or FlushModeType.COMMIT). With FlushModeType.AUTO the performance is progressively decreasing, even if only 1000 EntityBean POJO's have been read within the transaction. Just to recall the difference again:

          20 queries/reads within transaction - 10 msecs average per query/read
          1000 (same as before) queries/reads - 130 msecs average per query/read (factor 13+)

          This can't be OK! Possibly we need to do some specific JBoss configuration? Otherwise I tend to report this a a bug on JIRA, because such a significant performance downgrade can't be by intend or by design?

          Thanks for any feedback as to how we should best proceed with this problem.


          • 2. Re: Progressively decreasing query performance within transa
            Stefan Craatz Newbie

            Do you use em.Find() or a query to load your objects?
            From what I know the query methods has to check all loaded entities for changes which are to applied to the DB before the query is executed.
            You could add the following attributes to your persistance.xml to see whats going on:

            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.use_sql_comments" value="true"/>

            On the other hand, why do you load 1000 single objects within a single transaction? Can't you load them all at once. That should perform much better...