Thanks Koen I had suspected such things would exist but couldnt find them. I will take a look at this. Looks like a neat addon which would greatly help my project turnaround time.
cheers Sam On Wed, Jan 6, 2010 at 11:07 PM, koenb <koen.bierm...@werk.belgie.be> wrote: > > 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. > > > >
-- 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.
