ForeignKey Load TImes

Hey All,

I’m looking for any and all opinions, optimizations and improvements I can make to my current setup.

I’m relatively new to Django and have been loving it so far. For my most recent work project I have connected to Microsoft SQL Server as my primary database and am connecting to tables previously created in SQL via django’s inspectdb generated Models (with adjustments where required).

The Utilization table currently contains 500+ rows and will increase over time, however I have noticed that load times for my tables.html template is very long. I believe this to be due to the large amount of foreign key’s in use for my normalized configuration. I’m looking for porential solutions and also while this post is here, just any general newb mistakes I may have made.

Below is the details to my configuration, I frequently miss details in posts so don’t hesitate to call out anything I’ve missed.

Database Connection:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'DE_IoT',
        'HOST': 'PERWSWF',
        'TRUSTED_CONNECTION' : 'Yes',
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server'
        }
    }
}

Models.py

class Division(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True)
    division = models.CharField(db_column='Division', max_length=20)

    class Meta:
        db_table = 'WE_tblDivisions'

    def __str__(self):
        return self.division

class Manager(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True) 
    manager = models.CharField(db_column='Manager', max_length=50) 

    class Meta:
        db_table = 'WE_tblManagers'
        
    def __str__(self):
        return self.manager


class Office(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True) 
    office = models.CharField(db_column='Office', max_length=20) 

    class Meta:
        db_table = 'WE_tblOffices'
        
    def __str__(self):
        return self.office


class Phase(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True) 
    phase = models.CharField(db_column='Phase', max_length=50) 

    class Meta:
        db_table = 'WE_tblPhases'
        
    def __str__(self):
        return self.phase


class Role(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True) 
    role = models.CharField(db_column='Role', max_length=100) 

    class Meta:
        db_table = 'WE_tblRoles'
        
    def __str__(self):
        return self.role


class Project(models.Model):
    id = models.CharField(db_column='ID', primary_key=True, max_length=7) 
    name = models.CharField(db_column='Name', max_length=50) 
    phase_fk = models.ForeignKey(Phase, models.DO_NOTHING, db_column='Phase_FK', blank=True, null=True) 
    manager_fk = models.ForeignKey(Manager, models.DO_NOTHING, db_column='Manager_FK', blank=True, null=True) 

    class Meta:
        db_table = 'WE_tblProjects'
        
    def __str__(self):
        return self.name


class Staff(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True) 
    firstname = models.CharField(db_column='FirstName', max_length=20) 
    lastname = models.CharField(db_column='LastName', max_length=20) 
    ftemax = models.FloatField(db_column='FTEMax', validators = [validators.MinValueValidator(0), validators.MaxValueValidator(1)]) 
    role_fk = models.ForeignKey(Role, models.DO_NOTHING, db_column='Role_FK', blank=True, null=True) 
    division_fk = models.ForeignKey(Division, models.DO_NOTHING, db_column='Division_FK', blank=True, null=True) 
    office_fk = models.ForeignKey(Office, models.DO_NOTHING, db_column='Office_FK', blank=True, null=True) 

    class Meta:
        db_table = 'WE_tblStaff'
        verbose_name_plural = "Staff"

    @property
    def full_name(self):
        return '%s %s' % (self.firstname, self.lastname)
        
    def __str__(self):
        return self.firstname+" "+self.lastname


class Utilization(models.Model):
    id = models.BigAutoField(db_column='ID', primary_key=True) 
    projectid_fk = models.ForeignKey(Project, models.DO_NOTHING, db_column='ProjectID_FK', max_length=7) 
    staffid_fk = models.ForeignKey(Staff, models.DO_NOTHING, db_column='StaffID_FK')
    date = models.DateField(db_column='Date') 
    utilization = models.FloatField(db_column='Utilization') 

    class Meta:
        db_table = 'WE_tblUtilizationTESTING'
        verbose_name_plural = "Utilization"
        
    def __str__(self):
        return self.date.strftime('%d-%m-%Y')

Views.py:

from django.shortcuts import render
from django.http import HttpRequest
from app.models import Utilization

def tables(request):
    """Renders the about page."""
    assert isinstance(request, HttpRequest)

    return render(
        request,
        'app/tables.html',
        {
            'utilization': Utilization.objects.all(),
        }
    )

urls.py

from django.urls import path
from app import views

urlpatterns = [
    path('', views.home, name='home'),
    path('tables/', views.tables, name='tables')
]

tables.html

{% extends "app/layout.html" %}

{% block content %}

<div class="container">
    <div class="row">
        <div class="col">
            <h2>Staff</h2>
            <table width=100%>
                <tr>
                    <th>Project ID</th>
                    <th>Staff</th>
                    <th>Date</th>
                    <th>Utilization</th>
                </tr>
                {% for object in utilization %}
                    <tr>
                        <td class="table-bordered">{{object.projectid_fk}}</td>
                        <td class="table-bordered">{{object.staffid_fk}}</td>
                        <td class="table-bordered">{{object.date}}</td>
                        <td class="table-bordered">{{object.utilization}}</td>
                    </tr>
                {% endfor %}
            </table>
        </div>
    </div>
</div>

{% endblock %}
<p>
    &nbsp;
</p>

See select_related for addressing the “N+1” query issue.

Large html <table> performance is always going to be problematic, in that the entire table must be received and evaluated before it can be displayed. There are some tricks that can be done to gain some improvements. (Search for “large html table performance”, you’ll get some relevant hits.)

You might also want to consider implementing pagination for your data.
(We use jQuery DataTables for all our table-driven displays.)

Thanks for the tips,
So the primary time loss is within the HTML {%for%} loop itteration, not the Django model itself, is this correct?
I have a coleague who uses jQuery so I’ll do some research and pester him for some info.
Thanks for your help

No, that’s not necessarily an accurate characterization. I don’t know how the division of time breaks down - you’d need to determine that using a mix of the browser’s developer tools and something like the Django Debug Toolbar.

But, with the information available to me at this point, my gut is that those two issues are both contributing to the delays.

To be clear - the issue with the for loop iteration would be the N+1 query issue, not the table rendering issue. (Always keep in mind that template rendering occurs in the server and is 100% complete before the page is sent to the browser. The html table rendering occurs in the browser and only starts after the page it starting to be sent to it from the server.)

Addressing the N+1 query issue will help. How much? Don’t know. Too many variables.

Handling the html rendering in the browser is a separate issue. Again, there are ways to improve that. The degree to which it will help is also an unknown because there are even more variables.