Reducing number of ORM queries in Django web application

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I'm trying to improve the performance of one of my Django applications to make them run just a bit smoother, as part of a first iteration in improving what I currently have running. When doing some profiling I've noticed that I have a very high number of SQL queries being executed on a couple of pages.

The dashboard page for instance easily has 250+ SQL queries being executed. Further investigation pointed me to the following piece of code in my views.py:

for project in projects:

            for historicaldata in project.historical_data_for_n_months_ago(i):

                for key in ('hours', 'expenses'):

                    history_data[key] = history_data[key] + getattr(historicaldata, key)

Relevant function in models.py file:

def historical_data_for_n_months_ago(self, n=1):

    n_year, n_month = n_months_ago(n)



    try:

        return self.historicaldata_set.filter(year=n_year, month=n_month)

    except HistoricalData.DoesNotExist:

        return

As you can see, this will cause a lot of queries being executed for each project in the list. Originally this was set-up this way to keep functionality centrally at the model level and introduce convenience functions across the application.

What would be possible ways on how to reduce the number of queries being executed when loading this page? I was thinking on either removing the convince function and just working with select_related() in the view, but, it would still need a lot of queries in order to filter out records for a given year and month.

Thanks a lot in advance!

Edit As requested, some more info on the related models.

Project

class Project(models.Model):

    name = models.CharField(max_length=200)

    status = models.IntegerField(choices=PROJECT_STATUS_CHOICES, default=1)

    last_updated = models.DateTimeField(default=datetime.datetime.now)



    total_hours = models.DecimalField(default=0, max_digits=10, decimal_places=2)

    total_expenses = models.DecimalField(default=0, max_digits=10, decimal_places=2)



    def __str__(self):

        return "{i.name}".format(i=self)



    def historical_data_for_n_months_ago(self, n=1):

        n_year, n_month = n_months_ago(n)



        try:

            return self.historicaldata_set.filter(year=n_year, month=n_month)

        except HistoricalData.DoesNotExist:

            return

HistoricalData

class HistoricalData(models.Model):

    project = models.ForeignKey(Project, on_delete=models.CASCADE)

    person = models.ForeignKey(Person, on_delete=models.CASCADE)

    year = models.IntegerField()

    month = models.IntegerField()

    hours = models.DecimalField(max_digits=10, decimal_places=2, default=0)

    expenses = models.DecimalField(max_digits=10, decimal_places=2, default=0)



    def __str__(self):

        return "Historical data {i.month}/{i.year} for {i.person} ({i.project})".format(i=self)

edited Jan 3 at 17:12

asked Jan 3 at 15:57

Watcher

Could you add related models here?

– Sergey Pugach
Jan 3 at 16:50

Information has been added!

– Watcher
Jan 3 at 17:12

add a comment |

The dashboard page for instance easily has 250+ SQL queries being executed. Further investigation pointed me to the following piece of code in my views.py:

for project in projects:

            for historicaldata in project.historical_data_for_n_months_ago(i):

                for key in ('hours', 'expenses'):

                    history_data[key] = history_data[key] + getattr(historicaldata, key)

Relevant function in models.py file:

def historical_data_for_n_months_ago(self, n=1):

    n_year, n_month = n_months_ago(n)



    try:

        return self.historicaldata_set.filter(year=n_year, month=n_month)

    except HistoricalData.DoesNotExist:

        return

Thanks a lot in advance!

Edit As requested, some more info on the related models.

Project

class Project(models.Model):

    name = models.CharField(max_length=200)

    status = models.IntegerField(choices=PROJECT_STATUS_CHOICES, default=1)

    last_updated = models.DateTimeField(default=datetime.datetime.now)



    total_hours = models.DecimalField(default=0, max_digits=10, decimal_places=2)

    total_expenses = models.DecimalField(default=0, max_digits=10, decimal_places=2)



    def __str__(self):

        return "{i.name}".format(i=self)



    def historical_data_for_n_months_ago(self, n=1):

        n_year, n_month = n_months_ago(n)



        try:

            return self.historicaldata_set.filter(year=n_year, month=n_month)

        except HistoricalData.DoesNotExist:

            return

HistoricalData

class HistoricalData(models.Model):

    project = models.ForeignKey(Project, on_delete=models.CASCADE)

    person = models.ForeignKey(Person, on_delete=models.CASCADE)

    year = models.IntegerField()

    month = models.IntegerField()

    hours = models.DecimalField(max_digits=10, decimal_places=2, default=0)

    expenses = models.DecimalField(max_digits=10, decimal_places=2, default=0)



    def __str__(self):

        return "Historical data {i.month}/{i.year} for {i.person} ({i.project})".format(i=self)

edited Jan 3 at 17:12

asked Jan 3 at 15:57

Watcher

Could you add related models here?

– Sergey Pugach
Jan 3 at 16:50

Information has been added!

– Watcher
Jan 3 at 17:12

add a comment |

The dashboard page for instance easily has 250+ SQL queries being executed. Further investigation pointed me to the following piece of code in my views.py:

for project in projects:

            for historicaldata in project.historical_data_for_n_months_ago(i):

                for key in ('hours', 'expenses'):

                    history_data[key] = history_data[key] + getattr(historicaldata, key)

Relevant function in models.py file:

def historical_data_for_n_months_ago(self, n=1):

    n_year, n_month = n_months_ago(n)



    try:

        return self.historicaldata_set.filter(year=n_year, month=n_month)

    except HistoricalData.DoesNotExist:

        return

Thanks a lot in advance!

Edit As requested, some more info on the related models.

Project

class Project(models.Model):

    name = models.CharField(max_length=200)

    status = models.IntegerField(choices=PROJECT_STATUS_CHOICES, default=1)

    last_updated = models.DateTimeField(default=datetime.datetime.now)



    total_hours = models.DecimalField(default=0, max_digits=10, decimal_places=2)

    total_expenses = models.DecimalField(default=0, max_digits=10, decimal_places=2)



    def __str__(self):

        return "{i.name}".format(i=self)



    def historical_data_for_n_months_ago(self, n=1):

        n_year, n_month = n_months_ago(n)



        try:

            return self.historicaldata_set.filter(year=n_year, month=n_month)

        except HistoricalData.DoesNotExist:

            return

HistoricalData

class HistoricalData(models.Model):

    project = models.ForeignKey(Project, on_delete=models.CASCADE)

    person = models.ForeignKey(Person, on_delete=models.CASCADE)

    year = models.IntegerField()

    month = models.IntegerField()

    hours = models.DecimalField(max_digits=10, decimal_places=2, default=0)

    expenses = models.DecimalField(max_digits=10, decimal_places=2, default=0)



    def __str__(self):

        return "Historical data {i.month}/{i.year} for {i.person} ({i.project})".format(i=self)

edited Jan 3 at 17:12

asked Jan 3 at 15:57

Watcher

The dashboard page for instance easily has 250+ SQL queries being executed. Further investigation pointed me to the following piece of code in my views.py:

for project in projects:

            for historicaldata in project.historical_data_for_n_months_ago(i):

                for key in ('hours', 'expenses'):

                    history_data[key] = history_data[key] + getattr(historicaldata, key)

Relevant function in models.py file:

def historical_data_for_n_months_ago(self, n=1):

    n_year, n_month = n_months_ago(n)



    try:

        return self.historicaldata_set.filter(year=n_year, month=n_month)

    except HistoricalData.DoesNotExist:

        return

Thanks a lot in advance!

Edit As requested, some more info on the related models.

Project

class Project(models.Model):

    name = models.CharField(max_length=200)

    status = models.IntegerField(choices=PROJECT_STATUS_CHOICES, default=1)

    last_updated = models.DateTimeField(default=datetime.datetime.now)



    total_hours = models.DecimalField(default=0, max_digits=10, decimal_places=2)

    total_expenses = models.DecimalField(default=0, max_digits=10, decimal_places=2)



    def __str__(self):

        return "{i.name}".format(i=self)



    def historical_data_for_n_months_ago(self, n=1):

        n_year, n_month = n_months_ago(n)



        try:

            return self.historicaldata_set.filter(year=n_year, month=n_month)

        except HistoricalData.DoesNotExist:

            return

HistoricalData

class HistoricalData(models.Model):

    project = models.ForeignKey(Project, on_delete=models.CASCADE)

    person = models.ForeignKey(Person, on_delete=models.CASCADE)

    year = models.IntegerField()

    month = models.IntegerField()

    hours = models.DecimalField(max_digits=10, decimal_places=2, default=0)

    expenses = models.DecimalField(max_digits=10, decimal_places=2, default=0)



    def __str__(self):

        return "Historical data {i.month}/{i.year} for {i.person} ({i.project})".format(i=self)

python django sqlite orm

edited Jan 3 at 17:12

asked Jan 3 at 15:57

Watcher

edited Jan 3 at 17:12

asked Jan 3 at 15:57

Watcher

edited Jan 3 at 17:12

asked Jan 3 at 15:57

Watcher

asked Jan 3 at 15:57

Watcher

asked Jan 3 at 15:57

Watcher

Could you add related models here?

– Sergey Pugach
Jan 3 at 16:50

Information has been added!

– Watcher
Jan 3 at 17:12

add a comment |

Could you add related models here?

– Sergey Pugach
Jan 3 at 16:50

Information has been added!

– Watcher
Jan 3 at 17:12

Could you add related models here?

– Sergey Pugach
Jan 3 at 16:50

Information has been added!

– Watcher
Jan 3 at 17:12

add a comment |

1 Answer
1

active

oldest

votes

I don't think looping through querysets is ever a good idea. So it would be better if you could find some other way. If you could elaborate your view function and exactly what its supposed be to done maybe I could help further.

If you want all the historical_data entries for a project (reverse related) you need to use prefetch_related. Since you want a specific portion of the historical data associated with said project you need to use it with Prefetch.

from django.db.models import Prefetch

Project.objects.prefetch_related(

    Prefetch(

        'historicaldata_set', 

        queryset=HistoricalData.objects.filter(year=n_year, month=n_month)

    )

)

After that, you should be looping through this dataset in your django template (if you are using that). You can also pass it to a drf-serializer and that would also get your work done :)

answered Jan 3 at 18:23

Mehran

243111

That looks indeed a good way forward. I'll try that. Basically what is happening in the view is just gathering that data for each year/month combination in scope (usually last 12 months) and then creates a graph out of that data. So not a lot more then pulling it out of the database, maybe doing some rework to get it in the right shape and then pushing it out to the template.

– Watcher
Jan 3 at 18:44

gotcha, if this was indeed the solution you were looking for please select the answer

– Mehran
Jan 5 at 17:16

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025745%2freducing-number-of-orm-queries-in-django-web-application%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

from django.db.models import Prefetch

Project.objects.prefetch_related(

    Prefetch(

        'historicaldata_set', 

        queryset=HistoricalData.objects.filter(year=n_year, month=n_month)

    )

)

After that, you should be looping through this dataset in your django template (if you are using that). You can also pass it to a drf-serializer and that would also get your work done :)

answered Jan 3 at 18:23

Mehran

243111

That looks indeed a good way forward. I'll try that. Basically what is happening in the view is just gathering that data for each year/month combination in scope (usually last 12 months) and then creates a graph out of that data. So not a lot more then pulling it out of the database, maybe doing some rework to get it in the right shape and then pushing it out to the template.

– Watcher
Jan 3 at 18:44

gotcha, if this was indeed the solution you were looking for please select the answer

– Mehran
Jan 5 at 17:16

add a comment |

from django.db.models import Prefetch

Project.objects.prefetch_related(

    Prefetch(

        'historicaldata_set', 

        queryset=HistoricalData.objects.filter(year=n_year, month=n_month)

    )

)

After that, you should be looping through this dataset in your django template (if you are using that). You can also pass it to a drf-serializer and that would also get your work done :)

answered Jan 3 at 18:23

Mehran

243111

That looks indeed a good way forward. I'll try that. Basically what is happening in the view is just gathering that data for each year/month combination in scope (usually last 12 months) and then creates a graph out of that data. So not a lot more then pulling it out of the database, maybe doing some rework to get it in the right shape and then pushing it out to the template.

– Watcher
Jan 3 at 18:44

gotcha, if this was indeed the solution you were looking for please select the answer

– Mehran
Jan 5 at 17:16

add a comment |

from django.db.models import Prefetch

Project.objects.prefetch_related(

    Prefetch(

        'historicaldata_set', 

        queryset=HistoricalData.objects.filter(year=n_year, month=n_month)

    )

)

After that, you should be looping through this dataset in your django template (if you are using that). You can also pass it to a drf-serializer and that would also get your work done :)

answered Jan 3 at 18:23

Mehran

243111

from django.db.models import Prefetch

Project.objects.prefetch_related(

    Prefetch(

        'historicaldata_set', 

        queryset=HistoricalData.objects.filter(year=n_year, month=n_month)

    )

)

After that, you should be looping through this dataset in your django template (if you are using that). You can also pass it to a drf-serializer and that would also get your work done :)

answered Jan 3 at 18:23

Mehran

243111

answered Jan 3 at 18:23

Mehran

243111

answered Jan 3 at 18:23

Mehran

243111

answered Jan 3 at 18:23

Mehran

243111

That looks indeed a good way forward. I'll try that. Basically what is happening in the view is just gathering that data for each year/month combination in scope (usually last 12 months) and then creates a graph out of that data. So not a lot more then pulling it out of the database, maybe doing some rework to get it in the right shape and then pushing it out to the template.

– Watcher
Jan 3 at 18:44

gotcha, if this was indeed the solution you were looking for please select the answer

– Mehran
Jan 5 at 17:16

add a comment |

That looks indeed a good way forward. I'll try that. Basically what is happening in the view is just gathering that data for each year/month combination in scope (usually last 12 months) and then creates a graph out of that data. So not a lot more then pulling it out of the database, maybe doing some rework to get it in the right shape and then pushing it out to the template.

– Watcher
Jan 3 at 18:44

gotcha, if this was indeed the solution you were looking for please select the answer

– Mehran
Jan 5 at 17:16

That looks indeed a good way forward. I'll try that. Basically what is happening in the view is just gathering that data for each year/month combination in scope (usually last 12 months) and then creates a graph out of that data. So not a lot more then pulling it out of the database, maybe doing some rework to get it in the right shape and then pushing it out to the template.

– Watcher
Jan 3 at 18:44

gotcha, if this was indeed the solution you were looking for please select the answer

– Mehran
Jan 5 at 17:16

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu