On 31 dec 2009, 01:56, Sam Walters <mr.sam...@gmail.com> wrote: > Thanks for the replies. > > Yes, there is the option of going to raw MySQL. However the project > requirements mean i can't use raw SQL. (portability, readability) > From what i can see using django's db API i have to execute the > queries 500 times. > I am very familiar with the query documentation and i know that > select_related will prevent foward facing foreign keys translating to > an individual sql queries which hit the db and would slow it down. > > Fact is even when i dont use 'select_related' the major performance > problem occurs with the 'many-to-many' and 'reverse foreign' keys > (some 75% of the performance penalty for my package method is with > these) and only 20% can be solved by select_related. > > To be specific about how the multiplicities unfold: > > search_querySet is a Directory.objects.filter(... > > for s in search_querySet: > address_info = s.address_set.all() #.select_related(depth=2) - > yes i can/will put select related here but it really does not help > that much 20% tops > #address_info is usually 2-3 rows from an address table > for a in address_info:#.select_related(depth=2): > if a.addresstype.adrtype == 'Physical' and > a.addradmin.addr_enabled == True: > #further reduction in the number of rows which we need to > get values from. > related_phone=a.phone_set.all() > related_email=s.email_set.all() > #phones and emails are a small number of rows 2-3 tops > > It is these lines which produce the performance hit. > I cant see a way of using django's query language to avoid having to > descend into each of the 500 'directory' objects because of the > necessity to get all rows from the related tables in phones an emails > and to inspect the type of 'address' object. > > thanks for the ideas. will continue testing and looking for answers > > -Sam > > On Thu, Dec 31, 2009 at 2:41 AM, Nick Arnett <nick.arn...@gmail.com> wrote: > > > On Wed, Dec 30, 2009 at 7:15 AM, Adam Playford <adam.playf...@gmail.com> > > wrote: > > >> I'm not an expert on this, but a few thoughts. > > >> First, if I'm reading your message right, it sounds like your problem > >> probably isn't with the query, but with how many times you're running > >> it. > > > I'll echo that... the problem is not the database - the queries are as good > > as it gets. The problem is running them repeatedly. > > > If all else fails, I'd replace those queries that execute 500 times with raw > > SQL that uses the IN operator to get the required rows. > > > E.g.: SELECT `common_addresstype`.`id`, `common_addresstype`.`adrtype` FROM > > `common_addresstype` WHERE `common_addresstype`.`id` IN (1,6,8,52,173) > > > I imagine there's an ORM query that will do the same thing, but I know MySQL > > far better than I know Django. > > > Nick >
You can take a look at apps like django-selectreverse [1] or django- batch-select [2] for some ideas to make this kind of optimisations easier. Koen [1] http://code.google.com/p/django-selectreverse/ [2] http://github.com/lilspikey/django-batch-select
-- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@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.
