Django Query a query?

Question

Trying to speed up the performance of my app, and post some analysis with debug toolbar I can see I am doing 68 queries.

I query the circuits for every showroom (68 of), I thought if I just query the circuits once, could I then requery the existing query instead of calling the DB again for each Circuit?

something like adding:

crData = Circuits.objects.only('circuit_preference','site_data__id')

then how to query crData again? to match each statement?

Current code below

# get shworoom data
srData =  SiteData.objects.only('location','subnet').filter(is_live=True).exclude(site_type='Major Site')

for sr in srData:
    site = SiteType()
    site.type = checkRoute(sr.subnet)
    site.location = sr.location

    if 'MPLS' in site.type:
        mpls = site.type.split('-')
        try:

            d = Circuits.objects.only('circuit_preference','site_data').filter(site_data__id=sr.id,provider=mpls[0],circuit_type__icontains=mpls[1]) 
            site.preference = d[0].circuit_preference
        except:
            pass
    elif site.type == '4G':
        try:
            d = Circuits.objects.only('circuit_preference','site_data').filter(site_data__id=sr.id,provider='EE',circuit_type__icontains=site.type)
            site.preference = d[0].circuit_preference
        except:
            pass    
    elif site.type == 'DSL' or site.type == 'FIBRE':
        try:
            d = Circuits.objects.only('circuit_preference','site_data').filter(site_data__id=sr.id,circuit_type__icontains=site.type) 
            site.preference = d[0].circuit_preference
        except:
            pass

**EDIT: models below

class SiteData(models.Model):
    location = models.CharField(max_length=50)
    site_type = models.CharField(max_length=20, verbose_name="Site Type", \
                choices=settings.SITE_TYPE)
    subnet = models.GenericIPAddressField(protocol='IPv4')
    bgp_as = models.CharField(max_length=6, verbose_name="BGP AS Number")
    opening_date = models.DateField(verbose_name="Showroom opening date")
    last_hw_refresh_date = models.DateField(verbose_name="Date of latest hardware refresh", \
                           blank=True, null=True)
    is_live = models.BooleanField(default=False, verbose_name="Is this a live site?")
    tel = models.CharField(max_length=20, blank=True, null=True)
    notes = models.TextField(blank=True)
    class Meta:
        verbose_name = "Site Data"
        verbose_name_plural = "Site Data"
        ordering = ('location',)

    def __unicode__(self):
        return self.location

class Circuits(models.Model):
    site_data = models.ForeignKey(SiteData, verbose_name="Site", on_delete=models.PROTECT)
    order_no = models.CharField(max_length=200, verbose_name="Order No")
    install_date = models.DateField(blank=True, null=True)
    circuit_type = models.CharField(max_length=100, choices=settings.CIRCUIT_CHOICES)
    circuit_preference = models.CharField(max_length=20, verbose_name="Circuit Preference", \
                         choices=settings.CIRCUIT_PREFERENCE, blank=True, null=True)
    circuit_speed_down = models.DecimalField(max_digits=10, decimal_places=1, blank=True, null=True)
    circuit_speed_up = models.DecimalField(max_digits=10, decimal_places=1, blank=True, null=True)
    circuit_bearer = models.IntegerField(blank=True, null=True)
    provider = models.CharField(max_length=200, choices=settings.PROVIDER_CHOICES)
    ref_no = models.CharField(max_length=200, verbose_name="Reference No")
    cost_per_month = models.DecimalField(decimal_places=2, max_digits=8)
    contract_length = models.IntegerField(verbose_name="Contact length in years")
    service_contacts = models.ForeignKey(ServiceContacts, on_delete=models.PROTECT)
    subnet = models.GenericIPAddressField(protocol='IPv4', verbose_name="Subnet", \
             blank=True, null=True)
    default_gateway = models.GenericIPAddressField(protocol='IPv4', \
                      verbose_name="Default Gateway", blank=True, null=True)
    subnet_mask = models.CharField(max_length=4, verbose_name="Subnet Mask", \
                  choices=settings.SUBNET_MASK_CHOICES, blank=True, null=True)
    internet_device = models.ForeignKey(ConfigTemplates, \
                      verbose_name="is this circuit the active internet line for a device?", \
                      default=6, on_delete=models.PROTECT)
    decommissioned = models.BooleanField(default=False, verbose_name="Decomission this circuit?")

Show your models.

Daniel Roseman
– Daniel Roseman

2017-07-17 15:16:37 +00:00
Commented Jul 17, 2017 at 15:16 — Daniel Roseman
– Daniel Roseman, Commented Jul 17, 2017 at 15:16
@DanielRoseman added above, Tanks

AlexW
– AlexW

2017-07-17 15:25:05 +00:00
Commented Jul 17, 2017 at 15:25 — AlexW
– AlexW, Commented Jul 17, 2017 at 15:25

Greg Sebastian · Accepted Answer · 2017-07-17 16:31:56Z

1

I was going to point you toward Pickling, but I suppose that doesn't make sense unless you need to cache the querysets to re-use in another location.

Actually I'm pretty sure querysets are pretty good for only hitting the database when they need to, which is when they're first evaluated. However, I think redeclaring the queryset would cause it to be re-evaluated, but if you create a list/dictionary of querysets I imagine you should be able to just re-use them without hitting the database again (unless you need to run new filters on them.) So I don't think you have much choice than to hit the database for each time you fetch a crData queryset, but you should at least be able to store the querysets and reuse them without it hitting the database for each one again.

Something like this should work I think. Would love to know if I'm wrong.

crData = []
for sr in srData:
    # ...
    crData.append(d)

for cr in crData:
    # Do stuff

EDIT: Here's another relevant question: Django ORM and hitting DB

edited Jul 17, 2017 at 16:31

answered Jul 17, 2017 at 15:59

Greg Sebastian

2011 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AlexW Over a year ago

the only problem is I do need to requery the data, I was hoping to get all the circuits once, then query that data in my if statements as above

Greg Sebastian Over a year ago

Ohh, do you just want to iterate over crData more than once and check it's fields?

AlexW Over a year ago

yes, reading that though it suggests that covering to a list would have a worse performance impact than running 68 queries?

Greg Sebastian Over a year ago

To be honest I'm not sure. I haven't benchmarked that kind of thing. I think filtering with the db is faster than filtering a list, but if you're only iterating and checking a value a list is likely faster, if there's even much difference. I had understood that if you assign a queryset to a variable and iterate over it twice, not running any new filters on it, the database should only be hit once. If something is hitting it several times I imagine it would be the Circuit querysets inside the loop?

AlexW Over a year ago

Thats right, every site in the loop gets another query, ill try the list out and see if it improves speed

Collectives™ on Stack Overflow

Django Query a query?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related