How does cascade-delete work?

[Karthik Guru]

Since we can specify <cacade-delete> for a CMR in the ejb-jar.xml, i was wondering as to how it works behind the scenes. Does the app servers first load the related bean and then does a remove() on it?
or it just calls remove on the home supplying the primary key?
optoin#2 defintely looks more efficient. Thoughts?

[David Harkness]

It's been a while since I tested it out, but IIRC it's even smarter (at least WebLogic is): it issues a delete on the child table where the FK equals the parent's PK. For example, with User 1:N Address, it does "delete address where user_id = <User.id>". There's no point in loading the beans or going through the home interface for each related entity.

[Karthik Guru]

Originally posted by David Harkness:
It's been a while since I tested it out, but IIRC it's even smarter (at least WebLogic is): it issues a delete on the child table where the FK equals the parent's PK. For example, with User 1:N Address, it does "delete address where user_id = <User.id>". There's no point in loading the beans or going through the home interface for each related entity.

Yes issuing a delete is something we w'd do if asked to write pure SQL.
Infact we w'd probably specify a ON DELETE cascade constraint on that column and expect the database to take care of it. But does weblogic really do such stuff.
Then what it means is that ejbRemove() etc would not be called for the rest of the related beans since they are not even getting loaded in the first place.
Which version of weblogic did you check ? I installed spy database wrapper
on my machine to test the way it executes the finders. I was just shocked at the way weblogic6.1 handles basic finders and i'm not even talking about CMRs here. From the SQL trace, this is what i found:
for a finder findByName(),
it first selects the pimary keys from the table that match the name.
Then for every id in that resultset, it fires selects on the table to get the information. So if there are n records, it executes n+1 queries at the minimum. Please do let me know if you can verify this.I frankly c'd not trust my eyes when i noticed that for my simplest of workflows that fetches 70 records from a table, WL internally fired 71 selects to the database.
Many people tell me that i s'd learn to tune weblogic first and that we actually dont know how to use it. Now i kind of wonder if i have to tune a container to get such basic stuff done efficiently :roll:

[David Harkness]

Originally posted by karthik Guru:
Then what it means is that ejbRemove() etc would not be called for the rest of the related beans since they are not even getting loaded in the first place.
Which version of weblogic did you check ?

I'm running WebLogic 7.0sp1 and 7.0sp4. WebLogic has all it needs to issue a performant SQL delete to the database, and I'm pretty sure it does.

...continuing...
I installed spy database wrapper
on my machine to test the way it executes the finders. I was just shocked at the way weblogic6.1 handles basic finders and i'm not even talking about CMRs here. From the SQL trace, this is what i found:
for a finder findByName(),
it first selects the pimary keys from the table that match the name.
Then for every id in that resultset, it fires selects on the table to get the information. So if there are n records, it executes n+1 queries at the minimum. Please do let me know if you can verify this.

Yes, this is correct. There is a deployment parameter that you can set (finders-load-beans) that will tell WebLogic to load the data for the found beans *with the original query* instead of querying for the PKs and then loading each bean. There is a trade-off here, as usual. If you don't have it load beans, then each subsequent find-by-PK call will first look in the entity bean cache (and with cache-between-transactions, this can be a big win) before going to the database. With it enabled, it *always* loads the data for each bean in the first query and then rectifies the loaded beans with those already in the cache.
Note that the "recification" is an assumption on my part, as the J2EE spec wouldn't work if it didn't do this. If it does caching, it *must* be invisible to your business logic (you can't have find-by-PK return the same bean with *different* attribute values as one retrieved with a custom finder).
So it comes down to how often you predict a bean will be in the cache. If the beans are likely to be in the cache (few beans of that type), then you'll want finders-load-beans to be false. If you expect that each call to the finder will end up requiring loading the beans from the database, then you'll want the finder query to load the beans' data rather than issuing N individual queries.
So yes, you do need to learn the ins-n-outs of WebLogic and your database to effectively tune your application. Things like optimistic concurrency allow you to cache beans between transactions, but then you need to plan for the case of a failed update due to some other node modifying the database behind your back as well as the possibility of using stale data. However, this can give you a big performance boost if you use it for the right beans.

[Karthik Guru]

thanks a lot! that helped.
I looked at our application usage.
The problem i have now is in the way most of the domain objects are used, we might want to cache them in certain scenarios while we might want to execute a fetch from the database in others.the finder-load-beans applies to the bean in general. Guess, it is not easy making such decisions in practical situations.
May be it c'd be a little easier if we can specify settings at the query level. Looks like they are available too in later versions of weblogic.
One more thing if you have any idea about this:
I can use link tables to represent relationships. i can either convert the links to beans / indirectly use them to specify CMRs.
Do u think using beans to represent the links could degrade performance?.
yeah we do have certain link tables that store data specific to the link. Guess, we dont have an option there. We have to model them as a separate entity?.
We have certain look up data that is not likely to change through workflows. They seem to be an ideal candidate to be modelled as entities since it buys me caching for nothing.
thanks.