On Sun, 2006-07-23 at 13:03 +0200, Maciej Bliziński wrote: > Hi Eric and Malcolm, > > On Sun, 2006-07-23 at 17:53 +1000, Malcolm Tredinnick wrote: > > At the moment, you need to write some custom SQL to implement this. One > > possible solution is here: > > http://www.pointy-stick.com/blog/2006/06/14/custom-sql-django/ > > I'd like to suggest alternative SQL solution which performs better. > With my example database Malcolm's query takes 214ms to execute, while > my query does the same job in 5.8ms, which is 36 times faster. > > My solution is described here: > http://groups.google.com/group/django-users/browse_thread/thread/f5021614b77993a4/5e64e2fa06255f90#5e64e2fa06255f90
Like all database benchmarks, these are very sensitive to the data used, the database and hardware involved. For example, on my reasonably powerful desktop machine, running similar tests to yours (200 & 2000 items in each table and 70,000 cross-joins), the multi-join method is slightly faster on large (3 - 10) numbers of tables, but only by 25% even at 10 tables. For two items, they were essentially identical after I added indices on the m2m table. In all cases, the queries were less than 1 ms, though, in "explain analyze" mode, which is slower than normal querying mode. (I'm also testing on PostgreSQL, btw). Whilst your suggestion is slightly faster for smaller cases, the problem is that it doesn't scale quite as well: there is an upper limit for the number of joins a database can do, for example. So take those numbers (particularly 35 times faster) with a small grain of salt: if performance is critical for somebody's use case, benchmarking with realistic data on production systems is the real test case. Absolute times are dependent on a lot of things, including usage patterns. Not knocking your solutionas an idea, though: it's quite cute and definitely shows thinking outside the box. Best wishes, 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~----------~----~----~----~------~----~------~--~---
