CODEWITHSUNDEEP
pythondjangoperformancesqlitedjango-queryset
texnic
texnic

Reputation: 4098

Slow saving to Django database

I have created a custom manage.py command like this:

from django.contrib.auth.models import User
from django.core.management.base import BaseCommand
from photos.models import Person


class Command(BaseCommand):
    help = 'Pre-populate database with initial data'

    def _create_people(self, user):
        for i in range(0, 100):
            person = Person(first_name='FN', surname='SN', added_by=user)
            person.save()

    def handle(self, *args, **options):
        user = User.objects.get(username="user1")
        self._create_people(user)

I've timed the handle() execution, it takes about 0.02 s if I don't do person.save() and about 0.1 s per Person if I do save. The database is sqlite, I believe it should be way faster. What could explain such poor performance and how can I improve it?

Upvotes: 2

Views: 6802

Answers (1)

Dhia
Dhia

Reputation: 10619

Analysis: 

def _create_people(self, user):
   for i in range(0, 100):
     person = Person(first_name='FN', surname='SN', added_by=user)
     # Hits the database for each save.
     person.save()

This function will hit the Database 100 times and auto-commits each time, and this is what is causing the low performance (without considering the low performance of sqlite in comparison with MySQL or PostgreSQL).

Improvement:

What you need here in your case is bulk_create which takes as input an array of objects created using the class constructor. So a possible solution is as follow:

def _create_people(self, user):
   person_data = {"first_name": "FN", "surname":"SN", "added_by": user}
   person_list = [Person(**person_data) for i in range(100)]
   Person.objects.bulk_create(person_list)

By default bulk_create hits the DB once no matter how many objects, except in SQLite (In SQLite it's about 999 per query.). How many objects can be created in a single query can be specified through the parameter batch_size.

NB:

  • save() will not be called, and the related signals will not be sent.
  • does not work with m2m relationships.

Upvotes: 12

Related Questions