Re: Optimize cascade deletes

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Sat Sep 22 2007 - 05:48:53 EDT

  • Next message: Andrus Adamchik: "Re: Temp ID issue - Exception"

    On Sep 21, 2007, at 7:45 PM, Giulio Cesare Solaroli wrote:

    >
    > [18:09:40] DELETE FROM clipperz.RCRVRS WHERE ID_RCRVRS = ?
    > [18:09:40] [batch bind: 968]
    > [18:09:40] [batch bind: 875]
    > [......]
    > [18:09:40] [batch bind: 1177]
    > [18:09:40] [batch bind: 2792]
    > [18:11:54] === updated 68 rows.
    >
    > In this case, to delete just 68 rows it took more than two minutes,
    > but I don't know if this time is spent somewhere inside the cayenne
    > code, or if this is simply the time it took PostgreSQL to "physically"
    > delete the rows.

    Initially I suspected performance problems with faulting the
    relationships, but if the delete itself is so slow, it points to
    PostgreSQL as the bottleneck as Ari said (coincidentally the query
    above is a BatchDeleteQuery generated by Cayenne). If it takes that
    long, something is wrong with the DB. I second a suggestion to try
    installing PostgreSQL on Mac and it without Parallels.

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

    Cayenne 3.0M2 (yet unreleased) supports such functionality via an
    EJBQL query, but maybe you don't need to do direct DB deletion just yet.

    Andrus



    This archive was generated by hypermail 2.0.0 : Sat Sep 22 2007 - 05:49:29 EDT