Aggregate save () s in Django?

Question

I am using Django with sqlite backend and the write problem is the problem. At some point, I can finish the “correct” db, but at the moment I'm stuck in sqlite. I think that my problems with write performance are probably due to the fact that I create a large number of rows, and, presumably, every time I save() , it locks, unlocks and synchronizes the database on disk.

How can I combine a large number of save() calls into a single database operation?

Answer 1

EDIT: commit_on_success deprecated and was removed in Django 1.8. Use transaction.atomic instead. See Fraser Harris's answer .

This is actually easier to do than you think. You can use transactions in Django. These batch operations with the database (in particular, save, insert and delete) in one operation. I found the easiest to use - commit_on_success . Essentially, you wrap the database save operations in a function, and then use the commit_on_success decorator.

 from django.db.transaction import commit_on_success @commit_on_success def lot_of_saves(queryset): for item in queryset: modify_item(item) item.save() 

This will have a huge increase in speed. You will also get the advantage of rollbacks if any of the elements fails. If you have millions of save operations, you might have to commit_manually them in blocks using commit_manually and transaction.commit() but I rarely needed this.

Hope this helps,

Will be

Answer 2

Django 1.6's new atomic, simple API for managing database transactions . Copied verbatim from documents:

An atom can be used as a decorator :

 from django.db import transaction @transaction.atomic def viewfunc(request): # This code executes inside a transaction. do_stuff() 

and as a context manager :

 from django.db import transaction def viewfunc(request): # This code executes in autocommit mode (Django default). do_stuff() with transaction.atomic(): # This code executes inside a transaction. do_more_stuff() 

The legacy django.db.transaction autocommit() , commit_on_success() and commit_manually() are deprecated and will be removed in Django 1.8.

Answer 3

I think this is the method you are looking for: https://docs.djangoproject.com/en/dev/ref/models/querysets/#bulk-create

The code is copied from the documentation:

 Entry.objects.bulk_create([ Entry(headline='This is a test'), Entry(headline='This is only a test'), ]) 

Which in practice will look like this:

 my_entries = list() for i in range(100): my_entries.append(Entry(headline='Headline #'+str(i)) Entry.objects.bulk_create(my_entries) 

According to the docs, this performs one query, regardless of the size of the list (maximum 999 elements in SQLite3), which cannot be said of the atomic decorator.

There is an important distinction to be made. On the OP question, it sounds like he's trying to create mass creation, not mass conservation. atomic decorator is the fastest solution to save, but not to create.

Answer 4

"How can I combine a large number of save () calls into a single database operation?"

You do not need. Django already manages the cache for you. You cannot improve its DB caching by trying to fuss with persistence.

"Recording performance problems are probably due to the fact that I am creating a large number of lines."

Right.

SQLite is pretty slow. The way it is. Queries are faster than most other databases. The entries are pretty slow.

Let's consider more serious changes of architecture. Are you loading rows during a web transaction (i.e. bulk uploading files and loading the database from these files)?

If you are doing bulk uploads inside a web transaction, stop. You need to do something smarter. Use celery or use some other “batch” way to do your loads in the background.

We are trying to limit ourselves to checking files in a web transaction and performing loads when the user does not wait for their HTML page.