4 Replies Latest reply on May 15, 2002 3:10 PM by philc

    Entity Bean SQL load query caching

    philc

      When Entity beans are loaded using read-ahead, JBoss generates a big sql statement with 255 ORed primary keys. Using commit-option B, Entity beans are loaded from the database everytime an Entity bean is used in a transaction.

      The problem is that JBoss seems to cache the original SQL used to load the bean and uses it to refresh the beans. So when I access one Entity Bean, it refreshes all 255 Entity Beans and it slows down performance.

      What is this feature called? (SQL load query caching?) Is there a way to turn it off? Maybe a work-around would be to flush the cache right after a full load since commit-option B refreshs the Beans anyway...

      Thanks,
      Philippe

        • 1. Re: Entity Bean SQL load query caching
          dsundstrom

          > When Entity beans are loaded using read-ahead, JBoss
          > generates a big sql statement with 255 ORed primary
          > keys. Using commit-option B, Entity beans are loaded
          > from the database everytime an Entity bean is used in
          > a transaction.

          yep

          > The problem is that JBoss seems to cache the original
          > SQL used to load the bean and uses it to refresh the
          > beans.

          I SQL is not cached.

          > So when I access one Entity Bean, it refreshes
          > all 255 Entity Beans and it slows down performance.

          This is because you are accessing the bean using lots of transactions. The preloaded data is only valid for the length of transaction in which it was loaded. Use large granular transactions.

          > What is this feature called? (SQL load query
          > caching?) Is there a way to turn it off? Maybe a
          > work-around would be to flush the cache right after a
          > full load since commit-option B refreshs the Beans
          > anyway...

          It is called readahead. And you can turn it off by setting the strategy to none, but I don't recommend it. Just wrap code in one big transaction.

          • 2. Re: Entity Bean SQL load query caching
            philc


            > This is because you are accessing the bean using lots
            > of transactions.

            That's right.

            > The preloaded data is only valid
            > for the length of transaction in which it was loaded.
            > Use large granular transactions.

            This is the scenario:
            When client app starts:
            - Open Tx
            - findActiveOrders (EJB_QL) return 1000 EntityBeans
            - EntityBeans SELECTed with read-ahead in blocks of 255
            - Close Tx

            When client deceides to modify an order.
            - Open Tx
            - findByPrimaryKey(pk) returns 1 EntityBean
            - Single EntityBean SELECTed with it's 255 neighbors
            - Order is modified using set methods
            - Close Tx

            Why is read-ahead used when during the second transaction when only one Entity Bean was found using findByPrimaryKey. It seems like JBoss is using the previous transaction's read-ahead parameters thinking I'm still about to read 1000 Entity Beans.

            • 3. Re: Entity Bean SQL load query caching
              dsundstrom

              > Why is read-ahead used when during the second
              > transaction when only one Entity Bean was found using
              > findByPrimaryKey. It seems like JBoss is using the
              > previous transaction's read-ahead parameters thinking
              > I'm still about to read 1000 Entity Beans.

              That is the way read-ahead works. It is based on the assumption you will be iterating over the result set. Simply set read-ahead strategy to none, and you will get the results you desire.

              • 4. Re: Entity Bean SQL load query caching
                philc

                >That is the way read-ahead works.
                Are there Read-ahead specifications?

                The result set in the case of a findByPrimaryKey has a size of 1. Read-ahead does not improve performance by selecting 255 Entity Beans. If Read-ahead slows down the execution of basic operations such as findByPrimaryKey it does not qualify as a performance improvement of the overall system.

                There are a few options to consider:
                - Read-ahead could be reinitialized after a find method is executed.
                - Read-ahead could be reinitialized for each transaction.
                - Read-ahead could be setup on a findBy method basis so that findByPrimaryKey never reads ahead.

                These are simply suggestions. I understand I probably don't see the big picture clearly since I did not write or even read any of the jboss code.

                Philippe