Re: orm: delete performance and queries optimization
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: orm: delete performance and queries optimization
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 On 9 Gen, 02:26, Malcolm Tredinnickwrote: > 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: orm: delete performance and queries optimization
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
orm: delete performance and queries optimization
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---