Django ORM: slow SQL request using an update with a list of ids

user_19777490 0
Viewed 61

I have some database speed issues when doing an update with Django.

My request takes about 15s to be executed, updating ~1000 rows, which is quite slow

Here is a simplified version of my code

constant1 = sth
constant2 = sth

myList = Model.objects.filter(
    ...
)[:nb]

myListIds = []
for object in myList:

    ...
    myListIds.append(object.pk)

Model.objects.filter(
    pk__in=myListIds
).update(
    ...
    field1 = constant1,
    field2 = constant2,
    ...
)

I tried to look at the SQL request generated by Django, but it didn't teach me anything

What am I doing wrong here ?

My guess is that pk__in is the issue, but I can't find a workaround. I need to use a list of ids in order to update because my queryset is sliced, and Django does not allow to update sliced queryset (Cannot update a query once a slice has been taken.)

django-rest-framework django database performance
1 Answers

In your code you filter a queryset, insert ids in a list, and filter a new queryset with same ids. I think you can simply do this:

Model.objects.filter(
    ...
).update(
    ...
)

But if you need the for cycle and the slice, you can use bulk_update() method

myList = Model.objects.filter(
    ...
)[:nb]

objs = []
for object in myList:
    ...
    objs.append(object)

Model.objects.bulk_update(objs)

or you can simply do:

myList = Model.objects.filter(
    ...
)[:nb]

for object in myList:
    ...
    object.field = constant1
    object.save()

Note: If you are searching to cut off execution time, bulk_update() method accept as second param a list of fields, that can be useful for your pourpose.

user_141649230
Related