5 Replies Latest reply on Sep 24, 2003 3:16 PM by Ivan O. Parra

    CMP redundantly loading data

    Ivan O. Parra Novice

      JBoss3.2.1


      I'm currently trying to optimize a few of our entity beans and have come across an interesting scenario. Here is my configuration:

      Container-Configuration:
      OptimisticLocking(modified strategy)
      MultiInstance(Instance per Transaction, CMP2.x)
      on-find
      eager-load-group=*
      page-size=100000
      NO-CMR(at least not yet)

      Database Configuration:
      num_columns=10
      num_rows=5(primary keys are integers from 1-5)
      table_name=test
      DB=Oracle9i

      I turned on JDBC logging to monitor each call. This is what I saw:

      during ejbFindAll- SELECT <num_columns columns> FROM test t0_g (notice the t0_g)

      If I iterate through the results of this finder, I see this SQL between each iteration(I added println before each iteration):
      SELECT <num_columns columns> FROM test (no t0_g) WHERE ID = 1 OR ID = 2, ...., OR ID=5

      Each iteration decreased the where clause by 1, I don't know which order these number decrease. So the next iteration may have been : WHERE ID=2.. ID=5 ....... OR...... WHERE ID=1...ID=4. But each iteration decreased the where clause by 1.

      While this may not seem too bad for a table of 4 rows, this can be catastrophic for a large table. # of queries=1+2+...N(num of rows)... I was expecting 1 query, the initial SELECT * FROM TEST.

      Is there some sort of configuration that I can turn on to remove these queries? Did I misunderstand the documentation.?

      A few more pieces of data that may assist in explanation:
      1)All operations are under a single transaction
      2)commit option A does the same thing
      3)b/c of instance per transaction, cacheing is not an option - I think(I still tried :) )
      4)Of the 10 rows, I am looking at only 1(I wanted to force a load if the record hadn't been loaded yet)
      5)I'm not setting any data
      6)Local(not remote) calls
      7)I have the 3.0.4 CMP Docs
      8)I've tried groups, no-good(eager-load-group=* should handle all rows)

      Anyone have any suggestions on how to get rid of these extra queries???