Hello everybody, I am facing a performance bottle neck while commiting the dataContext when many "cascade" rules are applied to relevant relationships.
This scenario is quite simple: in my db schema there is a core entity that is the User; this entity has many subordinate information that are collected in order to provide a useful service. When I try to delete an user, all the cascade rules are correctly applied, but the all process takes about 7 minutes for a regular account. This is much more than we can bear for any operation. My understanding is that Cayenne, in order to perform a cascade delete, load all the objects matched by the given relationship, and delete them one at a time. This works beautifully when I have a few records attached to the main record, but it is failing miserably when the number of depending records grows considerably. Looking into the documentation I could found a very promsing "BatchDeleteQuery", but I have not understood how I am supposed to use it. What I would like to do, is using the query structure stored into the model to tell the which object to delete in batch. The SQL I am expecting Cayenne to generate would look like this: delete from user_detail where id_user = <id of the user I want to delete>; Am I barking at the wrong tree, or is BatchDeleteQuery the right place where to look to achieve this result? Thank you in advance for any comment or suggestion. Best regards, Giulio Cesare
