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 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.
