Relationship between two models Django/python

Question

I got two models and I need to get data from one table(model) to another, my logic works but it takes a while (around 5 minutes) this is because Times Table has around 90,000 data lines. I will post my two models first and then the logic I use to relate and to se what I get from the Times table:

Model 1:

class otreport(models.Model):
    sales_order_shipset = models.CharField(max_length=30)
    wms = models.CharField(max_length=50)
    status = models.CharField(max_length=200)
    aging = models.DateField(null=True, blank=True)
    carrier = models.DateField(null=True, blank=True)
    add_date = models.DateTimeField(null=True, blank=True)
    asn_validation = models.DateTimeField(null=True, blank=True)
    docs_add_date = models.DateTimeField(null=True, blank=True)
    po_number = models.CharField(max_length=20, unique=True)

Model 2:

class Times(models.Model):
    external_order = models.CharField(max_length=20)
    planning_order = models.CharField(max_length=20)
    wms_order = models.CharField(max_length=40)
    customer_id = models.CharField(max_length=50)
    service_level = models.CharField(max_length=100)
    state = models.CharField(max_length=5)
    status_date = models.DateTimeField(null=True, blank=True)
    order_date = models.DateTimeField(null=True, blank=True)
    order_add_date = models.DateTimeField(null=True, blank=True) 
    asn_sent_time = models.DateTimeField(null=True, blank=True)
    docs_received_time = models.DateTimeField(null=True, blank=True) 
    docs_processing_time = models.CharField(max_length=100) 

This is my logic, what it happens (to do it faster I used a list to do not re read the sale po_numbers) and relate the external order with the sales order to get the info I want:

def import_times(request):
    print "Import data from Times to OT"
    po_cache = list()
    for item in otreport.objects.all():

        if item.po_number in po_cache:
            continue

        times = Times.objects.filter(external_order=item.sales_order_shipset)
        for wi in times:
            po_cache.append(wi.external_order)
            item.wms = wi.wms_order
            item.status = wi.shipment_status
            item.aging = wi.status_date
            item.carrier = wi.service_level
            item.add_date = wi.order_add_date
            item.asn_validation = wi.asn_sent_time
            item.docs_add_date = wi.docs_received_time
            item.save()

My question is if anyone has a better idea to run this more efficient, if you need more data o details feel free to ask :),

Thanks for your time

Answer 1

Two things you can try

1. May be a minor improvement but don't use a list for the cache, use a set instead. Lookup will be faster.

2. Batch your save.

Try this:

from django.db import transaction


def import_times(request):
    transaction.set_autocommit(False)
    print "Import data from Times to OT"
    po_cache = set()
    for item in otreport.objects.all():

        if item.po_number in po_cache:
            continue

        times = Times.objects.filter(external_order=item.sales_order_shipset)
        records_count = 0
        for wi in times:
            records_count += 1
            po_cache.add(wi.external_order)
            item.wms = wi.wms_order
            item.status = wi.shipment_status
            item.aging = wi.status_date
            item.carrier = wi.service_level
            item.add_date = wi.order_add_date
            item.asn_validation = wi.asn_sent_time
            item.docs_add_date = wi.docs_received_time
            item.save()
            if records_count >= 10000:
              transaction.commit()
              records_count = 0
    transaction.commit()