orm: delete performance and queries optimization

[drakkan]

In my database I have some tables full of records, when I delete an
object django use the cascade behaviour and this is good, however
seems it make a query for every related object and after this django
seems to issue a delete where id in () for every 100 objects

look at this, Oggetti returns 8244 records:

t=Test.objects.get(pk=10)
Oggetti.objects.filter(test=t).delete()

I interrupted the operation after about 30 minutes and:

len(connection.queries)
4188

now in the database I have 6044 records so only 2200 were deleted and
this make 4188 queries

I think some optimization is needed,

reagards
drakkan

[Malcolm Tredinnick]

On Thu, 2009-01-08 at 04:54 -0800, drakkan wrote:
> In my database I have some tables full of records, when I delete an
> object django use the cascade behaviour and this is good, however
> seems it make a query for every related object and after this django
> seems to issue a delete where id in () for every 100 objects

We need to pull out the ids since deletes can occur across multiple
tables in various situations. We only do deletes in chunks since there
are maximum lengths of SQL queries. 100 isn't a bad number. If you're
needing to do tens of thousands of deletes regularly, use raw SQL.

[...]

> I think some optimization is needed,

We take patches. :-)

Just make sure the changes have a chance of working on all our database
backends.

Seriously, I'm a little surprised your database is only able to do less
than 2.5 queries per second. Be that as it may, if you're needing to do
mass deletes, do them directly. Django is never intended to be a
complete replacement for SQL and typical "delete" usage for a web
framework is a record or two, not thousands and thousands. Although it
should still be able to handle the latter case comfortably (I just
tested a 10,000 object delete and it only took a few minutes) unless
your models are quite complicated or there are lots and lots of indexes
to update or something. In other words, there are going to be
circumstance-specific exceptions, so testing and profiling is a good
idea. If this is a big problem for you, by all means look at what
changes might be possible. The code in question is in
django/db/models/base.py and django/db/models/query.py. It's a little
complex, but not horribly scary.

Regards,
Malcolm

[drakkan]

Thanks for the answer, I already solved the problem using raw sql and
reconfiguring postgres with on delete cascade on relevant foreign key
(obviously this doesn't work with mysql and myisam),

my database can seems slow but consider I have about 20.000.000
records on some tables and I tested a mass delete, maybe my users will
never do this but I need to test an application before release

consider that other orm such sqlalchemy (with the right configuration)
can manage the mass delete without problems (tested on postgres), the
trick is to allow control on the database cascade behaivour, I
understand you are not interested to add this features to django :-(,
we can however use django and raw sql

regards
drakkan

[Malcolm Tredinnick]

On Fri, 2009-01-09 at 00:33 -0800, drakkan wrote:
[...]

> consider that other orm such sqlalchemy (with the right configuration)
> can manage the mass delete without problems (tested on postgres), the
> trick is to allow control on the database cascade behaivour, I
> understand you are not interested to add this features to django :-(,

Well, like I said, we take patches. All contributions to Django come
from volunteers who want to solve a particular problem. I'm not going to
drop the few dozen things I'm working on to do this particular thing,
because it simply isn't high enough priority for me. If it's high
priority for you, by all means, go for it. There are no problems we
aren't interested in having solutions for. There are problems that are
much lower priority than others, though.

Malcolm