Hi Maarten,
also others have recognized the same problem before. The issue CASTOR-81
about some changes to the loading strategy is open for a long time now.
We are working in the direction to resolve that but as this is quite
complicated, we progress slow and it will take to long for you.
Having said that the solution I have in mind for this issues is exactly
what you mentioned in your mail. In addition I think we may gain further
improvement by caching the results of the foreign key queries.
For the time being you need to modify your code to work around the
problem. But there is not only bad news, I can point you to 2 sources
where you can find examples and hints for improvements. The first one is
a performance test suite (ptf) I wrote about half a year ago. It
measures execution time to load a tree of objects with different loading
strategies. You may want to have a look at:
src/tests/ptf/jdo/rel1toN
The other sourec is a summery of hints copied together from a thread on
mailing list. It's not in a state to be published yet but includes quite
some valueable ideas to improve performance. You can find it under:
http://docs.codehaus.org/display/CASTOR/Best+practis+to+use+CastorJDO
I hope this will help you to solve your problem.
Ralf
info schrieb:
Hello everybody,
I’m using Castor JDO (9.7) in a J2EE/Oracle application I’m maintaining
and I have some performance problems. I hope somebody can help me with
this. I noticed that Castor retrieves a vast amount of records from the
relational database when it actually only needs a few. This is the case
when I have one parent object with a number of collections of child
objects. I wonder whether this is by design and/or I can change this
behavior. Here is an example:
I have an object/class P, which is a parent that may refer to one or
more child objects A. It may also refer to one or more child objects B,
C, D and E. The mapping file looks something like this:
<mapping>
<class name=”P” identity="Pid">
<description>Sample</description>
<map-to table="P"/>
<field name="Pid" type="integer" >
<sql name="P_PID" type="integer"/>
</field>
<field name="As" type="A" lazy="true" collection="collection">
<sql many-key="A_PID" />
</field>
<field name="Bs" type="B" lazy="true" collection="collection">
<sql many-key="B_PID" />
</field>
:
<field name="Es" type="E" lazy="true" collection="collection">
<sql many-key="E_PID" />
</field>
</mapping>
From the debug output I can see that Castor generates the following SQL
statement to load the object from the relational database:
SELECT P.*,
A_PID,
B_PID,
C_PID,
D_PID.
E_PID
FROM P, A, B, C, D, E
WHERE P_PID = 12345
AND A_PID = P_PID
AND B_PID = P_PID
AND C_PID = P_PID
AND D_PID = P_PID
AND E_PID = P_PID
Well, here begins my trouble. Say table A thru E each contains 10
related records for record [Pid=12345] in P, then this query will
retrieve 10 * 10 * 10 * 10 * 10 = 100.000 records, where only 1 (P) + 10
+ 10 + 10 + 10 + 10 = 51 records are required! This is tremendously
slowing down the application. What I Castor would like to do is the
following:
SELECT * FROM P WHERE P.P_PID = 12345;
SELECT A_PID FROM A WHERE A_PID = 12345;
SELECT B_PID FROM B WHERE B_PID = 12345;
SELECT C_PID FROM C WHERE C_PID = 12345;
SELECT D_PID FROM D WHERE D_PID = 12345;
SELECT E_PID FROM E WHERE E_PID = 12345;
Is there a way to force Castor into this kind of behavior? As you can
see, I tried lazy loading. However I think this makes Castor just
retrieving the ids from the related table and doesn’t make it break down
the query. If nothing else helps I’ll remove the references to the child
records from the mappings file and assemble class P more or less
programmatically. Is that the only way out? Is there a better solution?
Thanks in advance.
Maarten Wijers
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
