Re: Optimize cascade deletes

From: Giulio Cesare Solaroli (giulio.cesar..mail.com)
Date: Fri Sep 21 2007 - 12:45:00 EDT

  • Next message: Craig L Russell: "Re: Optimize cascade deletes"

    I have tried to attach the logs and the model to the message, but the
    maililng list system has rejected it has spam. :-(

    If the full log and/or model could be useful in understanding the
    problem, I will upload them somewhere else.

    Hello Aristedes,

    thanks for your reply.

    Before answering your questions, let me describe my "environment":

    I am running everything on my own MacBookPro, with Postgresql running
    on a CentOS based Parallels virtual machine, while tomcat is running
    on the MacOS environment.

    This is definitely not a performance wise reference configuration, but
    I am not comfortable with these times anyway.

    On 9/21/07, Aristedes Maniatis <ar..aniatis.org> wrote:
    >
    > On 22/09/2007, at 12:31 AM, Giulio Cesare Solaroli wrote:
    >
    > > 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.
    >
    > That does seem extraordinary. Can you give us some background:
    >
    > * how many records are deleted

    about 100 objects, from 6/7 different tables.

    > * what version of Cayenne you are using

    2.0.2

    > * what database are you using

    PostgreSQL, probably version 8.1

    > * can you get a log of the sql being executed and discover what
    > precisely is taking so long

    I have attached the full log (and also the zip of the cayenne model,
    in case it is relevant); but the odd part is highlighted here (at the
    begin of each line is the time stamp with format "hh:mm:ss"):

    [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.

    Do these details help understanding what is going on?

    Thanks,

    Giulio Cesare

    This archive was generated by hypermail 2.0.0 : Fri Sep 21 2007 - 12:45:33 EDT