Is this loop over queryset optimal?

Question

If not what can i do to improve it? as currently it's taking 13s to evaluate with just our development data. ideas for Raw SQL?

    suppliers = models.Supplier.objects.all().order_by('company')
    for supplier in suppliers :
        sup = {}
        sup['company'] = supplier.company
        sup['supplies'] = get_supplies(1, supplier.uuid)
        sup['category'] = 'Supplier'
        if isocode == None :
            addresses = models.Address.objects.filter(company = supplier.company).iterator()
        else :
            addresses = models.Address.objects.filter(company = supplier.company, country_iso = isocode).iterator()
        sup['contacts'] = list(models.Contact.objects.filter(address__in=addresses))
        company_list.append(sup)

------------------------------------------------------------------------------

class SupplierManager(models.Manager):
    def suppliers_for_panel(self, bought_in_control_panel_id):
        return self.filter(supplies__bought_in_control_panel__id = bought_in_control_panel_id).filter(company__hidden=0).order_by('company__name')

class Supplier(models.Model):
    uuid = UUIDField(primary_key=True)
    company  = models.ForeignKey(Company, db_column='company_uuid',null=True, blank=True)
    sector = models.ForeignKey(CustomerSector, db_column='sector_uuid',null=True, blank=True,verbose_name=_('Sector'))
    account_number = models.CharField(null=True, blank=True,max_length=255,verbose_name=_('Account No'))
    reference  = models.CharField(null=True, blank=True,max_length=255)
    notes = models.TextField(null=True, blank=True) 
    date_created = models.DateTimeField(null=True, blank=True, auto_now_add=True)
    date_modified = models.DateTimeField(null=True, blank=True, auto_now=True)
    user = UserField(null=True, blank=True)
    jms_code = models.CharField(null=True, blank=True,max_length=255)

    objects = SupplierManager()

    def __unicode__(self):
        return self.company.name

    class Meta:
        db_table = 'supplier'

------------------------------------------------------------------------------

class Contact(models.Model):

    uuid = UUIDField(primary_key=True)
    address = models.ForeignKey(Address, db_column='address_uuid',null=True,blank=True,verbose_name=_('Address'))
    title = models.ForeignKey(Title,db_column='title_uuid',null=True, blank=True)
    forename = models.CharField(null=True, blank=True,max_length=255)
    surname = models.CharField(null=True, blank=True,max_length=255)
    position = models.CharField(null=True, blank=True,max_length=255)
    mobile = models.CharField(null=True, blank=True,max_length=255)
    direct_line = models.CharField(null=True, blank=True,max_length=255)
    email = models.CharField(null=True, blank=True,max_length=255)
    origin = models.IntegerField(null=True, blank=True)
    lead_source = models.IntegerField(null=True, blank=True)
    notes = models.TextField(null=True, blank=True)
    contact_status = models.ForeignKey(ContactStatus, db_column='contact_status_uuid',verbose_name=_('Contact Status'))
    contact_method = models.ForeignKey(ContactMethod, db_column='contact_method_uuid',verbose_name=_('Contact Method'))
    date_created = models.DateTimeField(null=True, blank=True, auto_now_add=True)
    date_modified = models.DateTimeField(null=True, blank=True, auto_now=True)
    user = UserField(null=True, blank=True)
    jms_code = models.CharField(null=True, blank=True,max_length=255)
    allow_download = models.NullBooleanField(serialize=False)
    is_modified = ModifiedField(serialize=False)

    def __unicode__(self):
        return self.get_full_name()

    def get_full_name(self):
        return self.forename + " " + self.surname

    class Meta:
        db_table = 'contact'

------------------------------------------------------------------------------

class Address(models.Model):
    uuid = UUIDField(primary_key=True)
    company = models.ForeignKey(Company, db_column='company_uuid',null=True,blank=True,verbose_name=_('Address'))
    group_name = models.CharField(null=True, blank=False,max_length=255,verbose_name=_('Corporate Group'))
    line1 = models.CharField(null=True, blank=False,max_length=255,verbose_name=_('Address Line 1'))
    line2 = models.CharField(null=True, blank=True,max_length=255,verbose_name=_('Address Line 2'))
    line3 = models.CharField(null=True, blank=True,max_length=255,verbose_name=_('Address Line 3'))
    town = models.CharField(null=True, blank=True,max_length=255)
    county = models.CharField(null=True, blank=True,max_length=255)
    postcode = models.CharField(null=True, blank=True,max_length=255)
    country_iso = models.CharField(null=True, blank=True,max_length=255)
    telephone = models.CharField(null=True, blank=True,max_length=255)
    fax = models.CharField(null=True, blank=True,max_length=255)
    email = models.CharField(null=True, blank=True,max_length=255)
    website = models.CharField(null=True, blank=True,max_length=255)
    description = models.CharField(null=True, blank=True,max_length=255)
    date_created = models.DateTimeField(null=True, blank=True, auto_now_add=True)
    date_modified = models.DateTimeField(null=True, blank=True, auto_now=True)
    user = UserField(null=True, blank=True)
    jms_code = models.CharField(null=True, blank=True,max_length=255)
    allow_download = models.NullBooleanField(serialize=False)
    notes = models.CharField(null=True, blank=True,max_length=255)    
    is_modified = ModifiedField(serialize=False)

    def __unicode__(self):
        if self.description in [ '',  None ] :
            if self.line1 not in [ '', None ] :
                return self.line1
            return self.uuid
        return self.description

    def asList (self) :
        return [ b for b in self.line1, self.line2, self.line3, self.town, self.county if b not in ('', None) ]

    class Meta:
        db_table = 'address'
        verbose_name_plural = ('Addresses')

------------------------------------------------------------------------------

Answer 1

First thing, add .select_related('company') to your initial supplier query. Right now you're issuing an additional query every time you access supplier.company in the loop.

The rest of the query bloat is coming from selecting Address and Contact individually. That's two more queries that must be issued each time through the loop. If you're using Django 1.4+ you can try to use prefetch_related to cut those out. If you're running a lesser version, you can try django-batch-select which gives you similar functionality. It's probably going to require a little re-thinking of your approach, to figure out a way to select everything at all at once.

Answer 2

Can be re-written as:

sup['contacts'] = models.Contact.objects.filter(address__in=addresses)

Sups would end-up as a Queryset object, which would be evaluated when you used it. If you really do need the list, do the following, which will evaluate the QuerySet immediately and give you a Python list object:

sup['contacts'] = list(models.Contact.objects.filter(address__in=addresses))

https://docs.djangoproject.com/en/dev/ref/models/querysets/#in https://docs.djangoproject.com/en/dev/ref/models/querysets/#when-querysets-are-evaluated

Answer 3

sup['contacts'] = Contact.objects.filter(address__in=addresses)