Possible methods to improve my database querys?

Question

I currently am using the attached script to get a self contained and (hopefully) unique set of data from my django database, but the time it takes to complete is getting ridiculous compared the the final filesize (20MB of formatted XML), and I think that there are ways for me to improve it massively but I don't have the experience to do so.

Is there anywhere you can spot in this set of querys which is slowing me down quite so heavily?

I would love references to django query optimisation techniques and tutorials!

The aim of the game here is to return all the fittings that are supplied by one company, including all the data that is needed to recreate that data in another instance of the application. Essentially I'm exporting fittings. this section is just about getting data into the correct format for my own serializer.

TL:DR; Help speed this code up while keeping the same output! It takes 10 4 mins!

@print_timing
def get_fittings(company_id):
    print "i'm getting Fittings"

    models = []
    result = []
    company_list = []
    company_list.append(Company.objects.filter(pk=company_id))
    print "Got Company"

    address_list = []
    address_list.append(Address.objects.filter(company=company_id))
    print "Got Address"

    customer_list = []
    customer_list.append(Customer.objects.filter(company=company_id))
    print "Got Customers"

    supplier_list = []
    supplier_list_internal = Supplier.objects.filter(company=company_id)
    supplier_list.append(supplier_list_internal)
    print "Got Supplier"

    fitting_supplier_list = []
    fitting_supplier_list_internal = FittingSupplier.objects.filter(supplier__in=supplier_list_internal)
    fitting_supplier_list.append(fitting_supplier_list_internal)
    print "Got Fitting Supplier"

    supplies_list = []
    supplies_list.append(Supplies.objects.filter(supplier__in=supplier_list_internal))
    print "Got Supplies"

    costbook_number = setup.Costbook.objects.filter(type="fittings").aggregate(max_number=model.Max('number'))
    this_costbook_list = FittingItemSupplierCost.objects.filter(supplier__in=fitting_supplier_list_internal, costbook_number=costbook_number['max_number'])

    fitting_list = []
    for this_costbook in this_costbook_list:
        fitting_list.append(Fitting.objects.filter(uuid=this_costbook.fitting_item.fitting.uuid))
    print "Got Fitting List"

    manufacturer_list = []

    for fitting_queryset in fitting_list:
        for fitting in fitting_queryset:
            if fitting.manufacturer != None:

                company_list.append(Company.objects.filter(uuid=fitting.manufacturer.company.uuid))

                address_list.append(Address.objects.filter(company=fitting.manufacturer.company.uuid))

                customer_list.append(Customer.objects.filter(company=fitting.manufacturer.company.uuid))

                manufacturer_list.append(Manufacturer.objects.filter(uuid=fitting.manufacturer.uuid))

    contacts_list = []
    for addresses in address_list:
        contacts_list.append(Contact.objects.filter(address__in=addresses))

    print "Got Companys and Addresses and Contacts and Customers and Manufacturers"

    fitting_supplier_list = []
    for fitting_supplier in fitting_supplier_list:
        fitting_list.append(Fitting.objects.filter(uuid=fitting_supplier.fitting.uuid))
    print "Got Fittings"

    #empty the list of FittingSuppliers
    fitting_item_list = []
    image_list = []
    for fitting_queryset in fitting_list:
        for fitting in fitting_queryset:
            fitting_item_list.append(fitting.item_set.all())
            image_list.append(FileStore.objects.filter(filename=fitting.image))
    print "Got FittingItem and FileStore"

    for fitting_queryset in fitting_list:
        fitting_supplier_list.append(FittingSupplier.objects.filter(fitting__in=fitting_queryset))
    print "Got FittingSupplier"

    fitting_item_supplier_cost_list = []
    for fitting_supplier_queryset in fitting_supplier_list:
        fitting_item_supplier_cost_list.append(FittingItemSupplierCost.objects.filter(supplier__in=fitting_supplier_queryset, costbook_number=costbook_number['max_number']))
    print "Got FittingItemSupplierCost"

    for company in company_list:
        result.append(company)
        models.append("Company")

    for address in address_list:
        result.append(address)
        models.append("Address")

    for contacts in contacts_list:
        result.append(contacts)
        models.append("Contact")

    for customer in customer_list:
        result.append(customer)
        models.append("Customer")

    for supplier in supplier_list:
        result.append(supplier)
        models.append("Supplier")   

    for supplies in supplies_list:
        result.append(supplies)
        models.append("Supplies")

    for manufacturer in manufacturer_list:
        result.append(manufacturer)
        models.append("Manufacturer")

    for fitting in fitting_list:
        result.append(fitting)   
        models.append("Fitting")

    for fitting_item in fitting_item_list:
        result.append(fitting_item)
        models.append("FittingItem")

    for image in image_list:
        result.append(image)
        models.append("Filestore")

    for fitting_supplier in fitting_supplier_list:
        result.append(fitting_supplier)
        models.append("FittingSupplier")

    for fitting_item_supplier_cost in fitting_item_supplier_cost_list:
        result.append(fitting_item_supplier_cost)
        models.append("FittingItemSupplierCost")

    #result, models = get_fitting_packs(company_id, result, models)

    return result, models

Models (There are a Lot!):

class Company(models.Model):
    uuid = UUIDField(primary_key=True)
    name =  models.CharField(null=True, blank=True,max_length=255,verbose_name=_('Company Name'))
    internal_name = models.CharField(null=True, blank=True,max_length=255,verbose_name=_('Internal Name'))
    reference = models.CharField(null=True, blank=True,max_length=255,verbose_name=_('Reference'))
    company_status = models.ForeignKey(CompanyStatus, null=True, db_column='company_status_uuid',verbose_name=(_('Company Status')))
    vat_number = models.CharField(null=True, blank=True,max_length=255,verbose_name=(_('Vat Number')))
    registration_number = models.CharField(null=True, blank=True,max_length=255,verbose_name=(_('Company Number')))
    discount = models.FloatField(null=True, blank=True)
    notes = models.TextField(null=True, blank=True,max_length=255)
    jms_code = models.TextField(null=True, blank=True,max_length=255)
    logo = models.TextField(null=True, blank=True,max_length=255)
    date_created = models.DateTimeField(null=True, blank=True, auto_now_add=True,verbose_name=_('Date Time'), serialize=False)
    date_modified = models.DateTimeField(null=True, blank=True, auto_now=True,verbose_name=_('Date Time Updated'), serialize=False)
    hidden = models.NullBooleanField(null=True, blank=True,default=0, serialize=False)
    user = UserField(null=True, blank=True, serialize=False)
    is_modified = ModifiedField(serialize=False)
    companyid = models.IntegerField(null=True, blank=True, serialize=False)
    seqno = models.IntegerField(null=True, blank=True, serialize=False)

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, serialize=False)
    date_modified = models.DateTimeField(null=True, blank=True, auto_now=True, serialize=False)
    user = UserField(null=True, blank=True, serialize=False)
    jms_code = models.CharField(null=True, blank=True,max_length=255)
    notes = models.CharField(null=True, blank=True,max_length=255, serialize=False)    
    is_modified = ModifiedField(serialize=False)

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(db_column='job_title',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, serialize=False)
    contact_status = models.ForeignKey(ContactStatus, db_column='contact_status_uuid',verbose_name=_('Contact Status'), serialize=False)
    contact_method = models.ForeignKey(ContactMethod, db_column='contact_method_uuid',verbose_name=_('Contact Method'), serialize=False)
    date_created = models.DateTimeField(null=True, blank=True, auto_now_add=True, serialize=False)
    date_modified = models.DateTimeField(null=True, blank=True, auto_now=True, serialize=False)
    user = UserField(null=True, blank=True, serialize=False)
    jms_code = models.CharField(null=True, blank=True,max_length=255)
    is_modified = ModifiedField(serialize=False)

class Customer(models.Model):
    uuid = UUIDField(primary_key=True)
    company  = models.ForeignKey(Company, db_column='company_uuid',null=True, blank=True)
    customer_sector = models.ForeignKey(CustomerSector, db_column='customer_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)
    customer_status = models.IntegerField(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)

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)

class Supplies(models.Model):
    uuid = UUIDField(primary_key=True)
    supplier  = models.ForeignKey(Supplier, db_column='supplier_uuid',null=True, blank=True)
    bought_in_control_panel  = models.ForeignKey('boughtin.BoughtInControlPanel', db_column='bought_in_control_panel_id',null=True, blank=True)

class Costbook(models.Model):
    COSTBOOK_TYPES = [
        ('glass', 'Glass'),
        ('timber', 'Timber'),
        ('fittings', 'Fittings'),
        ('misc', 'Miscellaneous'),
    ]
    uuid                = UUIDField(primary_key=True)
    number              = models.IntegerField(null=True, blank=True, editable=False)
    type                = models.CharField(null=True, blank=True, max_length=50, editable=False, choices=COSTBOOK_TYPES)
    name                = models.CharField(null=True, blank=True, max_length=255)
    date_created        = models.DateTimeField(null=True, blank=True, editable=False)
    date_modified       = models.DateTimeField(null=True, blank=True, auto_now=True, editable=False)
    user                = UserField(null=True, blank=True)

class FittingItemSupplierCost(CostbookCostEntry):
    cost_type = (
        (BI_COST_MANUAL, _('Default Cost')),
        (BI_COST_REQUEST, _('Request Cost'))         
    )

#    IMPORTANT: the following fields INHERITED from CostbookCostEntry (setp.models) 

#    uuid = UUIDField(primary_key=True)
#    costbook = models.ForeignKey(Costbook, db_column='costbook_uuid',null=True, blank=True)
#    costbook_number = models.IntegerField(null=True, blank=True, editable=False)
#    net_cost  = models.FloatField(_('Net Cost'),null=True, blank=True,default='0')
#    charge_out = models.FloatField(_('Charge out'),null=True, blank=True)

    rrp = models.FloatField(_('RRP'),null=True, blank=True)
    fitting_item = models.ForeignKey(FittingItem, db_column='fitting_item_uuid',null=True, blank=True, editable=False, related_name='supplier_cost_set')
    supplier = models.ForeignKey(FittingSupplier, db_column='fitting_supplier_uuid',null=True, blank=True)
    code = models.CharField(null=True, blank=False,max_length=255)
    cost_type = models.IntegerField(null=True, blank=True, choices=cost_type)
    user = UserField(null=True, blank=True, serialize=False)
    date_time_updated  = models.DateTimeField(null=True, blank=True, auto_now=True)  

class Fitting(models.Model):
    uuid                         = UUIDField(primary_key=True)
    bought_in_control_panel_file = models.ForeignKey(BoughtInControlPanelFile, db_column='bought_in_control_panel_file_id',null=True, blank=True)
    name                         = models.CharField(_('name'),null=True, blank=False,max_length=255)                           # Accessed by the get_name property
    code                         = models.CharField(_('Code'),null=True, blank=True,max_length=255)
    default_colour               = models.ForeignKey(Colour, db_column='default_colour_uuid',null=True, blank=True)
    material                     = models.ForeignKey(Material, db_column='material_uuid',null=True, blank=True)
    material_finish              = models.ForeignKey(MaterialFinish, db_column='material_finish_uuid',null=True, blank=True)
    pricing_type                 = models.IntegerField(_('pricing type'), choices=PRICING_TYPE)
    sales_description            = models.CharField(_('Sales Description'),null=True, blank=False,max_length=255)              # Accessed by the get_sales_description property
    purchase_description         = models.CharField(_('Purchase Description'),null=True, blank=False,max_length=255)           # Accessed by the get_purchase_description property
    workshop_description         = models.CharField(_('Workshop Description'),null=True, blank=False,max_length=255)           # Accessed by the get_workshop_description property
    notes                        = models.TextField(null=True, blank=True) 
    manufacturer                 = models.ForeignKey(Manufacturer, db_column='manufacturer_uuid',null=True, blank=True)
    manufacturer_code            = models.CharField(_('Manufacturer code'),null=True, blank=False,max_length=100)
    specification                = models.TextField(null=True, blank=False)
    limit_weight_min             = models.FloatField(_('Min Weight'), null=True, blank=True)
    limit_weight_max             = models.FloatField(_('Max Weight'), null=True, blank=True)
    limit_height_min             = models.FloatField(_('Min Height'), null=True, blank=True)
    limit_height_max             = models.FloatField(_('Max Height'), null=True, blank=True)
    limit_width_min              = models.FloatField(_('Min Width'), null=True, blank=True)
    limit_width_max              = models.FloatField(_('Max Width'), null=True, blank=True)
    fire_rating                  = models.ForeignKey(FireRating, db_column='fire_rating_id',null=True, blank=True)
    u_value                      = models.FloatField(_('U-value'),default=0)
    acoustic_rating              = models.FloatField(_('Acoustic Rating'),default=0)
    image                        = models.ImageField(_('Image'),upload_to=_fitting_image_upload_path, storage=DatabaseImageStorage(), null=True, blank=True)
    fitting_quantity_type        = models.ForeignKey(FittingQuantityType,db_column='fitting_quantity_type_id', null=True, blank=True)
    allow_profile                = models.BooleanField(default=0)
    profile_xml                  = models.TextField(null=False, blank=True)
    jms_default_cost             = models.FloatField(_('JMS Default Cost'),null=True, blank=True)
    disabled                     = models.BooleanField(default=0)
    date_time_updated            = models.DateTimeField(null=True, blank=True, auto_now=True)

class Manufacturer(models.Model):
    uuid = UUIDField(primary_key=True)
    company  = models.ForeignKey(Company, db_column='company_uuid',null=True, blank=True)
    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)

class FittingItem(models.Model, BoughtinItemMixin):
    uuid = UUIDField(primary_key=True)
    bought_in_control_panel_file = models.ForeignKey(BoughtInControlPanelFile, db_column='bought_in_control_panel_file_id',null=True, blank=True)
    fitting = models.ForeignKey(Fitting, db_column='fitting_uuid',null=True, related_name = 'item_set')
    unit_size = models.FloatField('Base Quantity', null=False, blank=False)
    unit_count = models.IntegerField('Multiplier', null=False, blank=False)

class FileStore(models.Model):
    uuid = UUIDField(primary_key=True)
    filename = models.CharField(max_length=255)
    data_base64 = models.TextField(null=True, blank=False)
    size = models.IntegerField(null=False, blank=False)

class FittingSupplier(models.Model):
    uuid = UUIDField(primary_key=True)
    fitting = models.ForeignKey(Fitting, db_column='fitting_uuid',null=True, blank=True, related_name='supplier_set')
    supplier = models.ForeignKey(Supplier, db_column='supplier_uuid',null=True, blank=True)
    rating = models.IntegerField(null=True, blank=True)
    markup = models.FloatField(_('markup'),null=True, blank=True)
    disabled = models.IntegerField(null=True, blank=True)
    date_time_updated  = models.DateTimeField(null=True, blank=True, auto_now=True)

Answer 1

A few things:

• You shouldn't iterate over querysets, unless you must.
• You should only get what you need.
• You have a lot of redundant code.
• Use django-debug-toolbar, and be happy.
• Almost certainly the information you want can be returned in one or two querysets. Without seeing your models, it's hard to tell though how best to answer the question.
• Related to the above, what is your question? after you've provided your models

Answer 2

It is not exactly clear what you're trying to retrieve, it looks like result contains everything more or less related to company_id. Don't you just need fittings?

Anyway there is one thing you do more often than once, it's this :

for x in a_previous_queryset:
    blop += list(Something.objects.filter(x=x.uuid))

Here you're doing one query for each element in a_previous_queryset, whereas you could do:

blop = Something.objects.filter(x__in=a_previous_queryset)

which would make just one queryset for everything. There probably are lots of other thing to do with your code.

Also, I don't know why you list every query.

More broadly, you really should try to understand, first, what each query actually does (reading the docs and connection.queries is good for that), and, second, what are all the options that django offer (the docs on querysets are good for that).

Answer 3

When you run Django with DEBUG=True, query execution times are being kept around for later analysis. Here's the relevant section of the Django documentation:

Make sure your Django DEBUG setting is set to True. Then, just do this:

from django.db import connection
connection.queries [{'sql': 'SELECT polls_polls.id,polls_polls.question,polls_polls.pub_date FROM
polls_polls', 'time': '0.002'}]