I also read a good article. As it turns out im using a lot of inner joins: http://www.caktusgroup.com/blog/2009/09/
This was quite handy. My processis usually build it to be as fast as possible in raw SQL then go back in and try and makre it with the ORM. "I've read your description and it doesn't mention how often your data changes. If it's not too often, you might want to consider simply caching the whole results or parts of it. You'll then have to add a bit of code to regenerate the cache every time the data changes." Yes actually in some of the scenarios the data is quite static, however im doing a lot of stuff with lat/long coordinates now so it means i have a bunch of keys in the results dict returned which are all dynamic, per click values and need to be recalculated a lot. Note: I have taken your advice on board for some of my views Philippe. Cheers Sam_W On Fri, Jan 8, 2010 at 12:18 AM, Philippe Raoult <philippe.rao...@gmail.com> wrote: > Hi Sam, > > I've read your description and it doesn't mention how often your data > changes. If it's not too often, you might want to consider simply > caching the whole results or parts of it. You'll then have to add a > bit of code to regenerate the cache every time the data changes. It > looks to me like you have one query for selecting which entries to > print, then many repetitive queries to find information about each > entry. If you had a per-entry cache then you would probably be down to > one main query plus a lot of cache lookups. > > Also you were on the right track when using addresses = > Address.objects.filter(directory__in=directories). I've often used > that kind of queries to reduce (1 + many queries) down to (2 + lots of > dictionnary operations) > > Hope that helps. > > Regards, > Philippe > > > On 7 jan, 11:39, Sam Walters <mr.sam...@gmail.com> wrote: >> 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 >> > athttp://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. > > > > -- 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.
