CMP redundantly loading data

[ioparra]

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???

[ioparra]

Here is another interesting situation that is confusing me.

If I use the CMP2.x Container, but enable OptimisticLocking, each update does a very complex WHERE clause to match the original data. For example, to change a tuple from (A,B,C) to (A,B,D), the CMP engine creates an SQL:
UPDATE foobar SET first=A,second=B,third=D WHERE
first=A, second = B, third = C.

That is fine, IF the types are simple types(String, ints). The CMP engines has a few issues with booleans(jdbc bit), nullables, dates.

I created a row with a PK=3 and set its data(within a single transaction) and it failed because it couldn't find the row that this transaction originally created. I can see the insert of (3,null,null,null,null, etc). And the update of (3,"foo","bar","sept 15") WHERE (3,null,null,null,null). I don't understand why this final query didn't find the row it originally created?

Anyone know why?
-Ivan

[raja05]

what database are u using? did u try doing a sql query to see if the records are infact null?

BTW, do u have a testcase i can try here on my machine ?

-Raj

[ioparra]

> what database are u using?
Oracle9i

> did u try doing a sql query to see if the records are infact null?

Hmm.. well, the only insert I see is the insert during the ejbCreate stage. I haven't done an insert but can only assume it exists during the transaction. If I change to NoLock, then the where clause is based on the PK and is successful during the update.

>>BTW, do u have a testcase i can try here on my machine ?
test machines are at work, i'll post again tomorrow...

:) Thanks---
-Raj

[ioparra]

A coworker found this issue:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=791946&group_id=22866

Its title is
Summary: Optimistic Locking Problem with v.3.2.1

Initial Comment:
All updates fail if any locked field has been intialized with
null as its value.



I'm cool with issue now(rather I know its been noted), I'm still confused with my original problem.

-Ivan

[ioparra]

After even more looking, I found a similar case:

http://sourceforge.net/tracker/index.php?func=detail&aid=722539&group_id=22866&atid=376685

Nice to know I'm not freaking out.

Perhaps Alex can post which version(RC1,RC2, etc) this was fixed on????
I'll look through the CVS logs, perhaps I'll get lucky.

-Ivan