Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of giulio.cesare@gmail.com
 designates 209.85.198.190 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=DcnFFG/JbXdoB3HzWnIYrI48CnOE9zUM/ERTbHvcQrBvZ2Qi6xQH3nlbASvi/+t3IwO8ly44R8oO6MSUEN/Da6HykbMJs2utCV1mOgfW2rPwoNZoekbgRDmLwYN9yS9EfrPrihu9iS2rsnY6TZQFH3fzccLxJLYg7TlzCkLyFDU=
Message-ID: <ff737ac30709220641g171c2a1as89d301181b35e6ce@mail.gmail.com>
Date: Sat, 22 Sep 2007 15:41:19 +0200
From: "Giulio Cesare Solaroli" <giulio.cesare@gmail.com>
To: user@cayenne.apache.org
Subject: Re: Optimize cascade deletes
In-Reply-To: <0A99EB82-4E31-46BB-8682-A98205290A5E@objectstyle.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <ff737ac30709210731y2b30484bjdfddc4552ed7f932@mail.gmail.com>
	 <EEE6AB13-F528-439D-99FA-2547F6068518@maniatis.org>
	 <ff737ac30709210945s5a203a32mb7d4d72eb7a1e4bc@mail.gmail.com>
	 <0A99EB82-4E31-46BB-8682-A98205290A5E@objectstyle.org>

On 9/22/07, Andrus Adamchik <andrus@objectstyle.org> wrote:
>
> 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.

I have being able to run the same test on the deployment server, that
is running on a Solaris zone c/o Joyent, and the issue is much
smaller:

[15:21:12] DELETE FROM clipperz.RCRVRS WHERE ID_RCRVRS = ?
[15:21:12] [batch bind: 1433]
[15:21:12] [batch bind: 797]
[....]
[15:21:12] [batch bind: 55637]
[15:21:12] [batch bind: 51363]
[15:21:27] === updated 182 rows.

So, to delete 182 rows it took about 15 seconds. I was a little
skeptical, but it really seems that even with such a small operations,
running from inside a virtual machine can make a huge difference for
the DB engine.

15 seconds are a lot of time, but they would be bearable. The problem
is that I suppose this time will gradually increase with the growth of
the number of records to delete.

And if I will end-up needing to delete thousands of records, the
outcome will be unbearable.

So I can probably live with this limits for a few more weeks, but I
need to immediately take action to fix the problem definitely.


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

Where should I start looking to try to integrate the new revision of
Cayenne in my project in order to take advantage of this feature?

Thank you very much for your support.

Best regards,

Giulio Cesare