Optimize cascade deletes

From: Giulio Cesare Solaroli (giulio.cesar..mail.com)
Date: Fri Sep 21 2007 - 10:31:20 EDT

  • Next message: Aristedes Maniatis: "Re: Optimize cascade deletes"

    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



    This archive was generated by hypermail 2.0.0 : Fri Sep 21 2007 - 10:31:56 EDT