Terrible Django Admin Performance

Question

I am developing a Django project, which already got plenty of real world data so I can see its performance.

The performances of few DjangoAdmin lists are just terrible.

I have one admin list, lets call it devices. In that list I am fetching additional informations for each row, those fields are related from other tables and connected via FK/PK/M2N.

List contains about 500 records and loading of that screen takes, according to django-debug-toolbar, around 6.5 seconds, which is unbearable.

This admin class

@admin.register(Device)
class DeviceAdmin(admin.ModelAdmin):
    list_select_related = True
    list_display = ('id', 'name', 'project', 'location', 'machine', 'type', 'last_maintenance_log')
    inlines = [CommentInline, TestLogInline]

    def project(self, obj):
        try:
            return Device.objects.get(pk=obj.pk).machine.location.project.project_name
        except AttributeError:
            return '-'

    def location(self, obj):
        try:
            return Device.objects.get(pk=obj.pk).machine.location.name
        except AttributeError:
            return '-'

    def last_maintenance_log(self, obj):
        try:
            log = AdminLog.objects.filter(object_id=obj.pk).latest('time')
            return '{} | {}'.format(log.time.strftime("%d/%m/%Y, %-I:%M %p"), log.title)
        except AttributeError:
            return '-'

All Machine, Location and Project are tables in database.

After looking into queries in django-debug-toolbar I discovered something terrible. That screen required 287 sql queries ! Yes, over two hundreds!

Is there anything I can do to reduce this time to something reasonable?

EDIT: Thanks to Bruno I removed Device.objects.get(pk=obj.id) (as it was really redundant, I totally overlooked this.

So everywhere i put obj. for example obj.machine.location.project.project_name

Only this alone reduces the speed and query count to half, so far so good.

I not have trouble fusing obj approach and select_related approach. This is my current code, which is worse than only obj approach.

def project(self, obj):
        try:
            Device.objects.select_related('machine__location__project').get(id=obj.pk).machine.location.project.project_name
        except AttributeError:
            return '-'

Which creates a nice INNER JOINs in queries, but the performance is around 15% worse than without it and using only obj.machine.location.project.project_name

What am I doing wrong?

EDIT2:

Best performance I got is with this code:

@admin.register(Device)
class DeviceAdmin(admin.ModelAdmin):
    list_select_related = True
    save_as = True
    form = DeviceForm
    list_display = ('id', 'name', 'project', 'location', 'machine', 'type', 'last_maintenance_log')
    inlines = [CommentInline, TestLogInline]

    def project(self, obj):
        try:
            return obj.machine.location.project.project_name
        except AttributeError:
            return '-'

    def location(self, obj):
        try:
            return obj.machine.location.name
        except AttributeError:
            return '-'

    def last_maintenance_log(self, obj):
        try:
            log = AdminLog.objects.filter(object_id=obj.pk).latest('time')
            return '{} | {}'.format(log.time.strftime("%d/%m/%Y, %-I:%M %p"), log.title)
        except AttributeError:
            return '-'

    def get_queryset(self, request):
        return Device.objects.select_related('machine__location__project').all()

Which pushed this down to 104 queries (from almost 300) and time reduction over 50%. Can this be improved any further?

The number of queries is not surprising. From what I see you are doing 3 extra queries *per row* to get the data for the methods. — Klaus D., Aug 28 '18 at 11:15
Well, yes, it is not so suprising with this approach, but I am wondering if is this somehow avoidable, I'll like to keep those extra info, but the cost is just too high. — Josef Korbel, Aug 28 '18 at 11:16

score 2 · Accepted Answer · answered Aug 28 '18 at 11:47

2

First, avoid totally useless queries - this:

Device.objects.get(pk=obj.pk)

is as useless as can be since obj already the Device instance you are looking for.

Then override your DeviceAdmin.get_queryset method to make proper use of select_related and prefetch_related so you only have the minimal required number of queries.

answered Aug 28 '18 at 11:47

bruno desthuilliers

75,974
6
88
118

Hello Bruno, thanks for your input, I am going to try this right now and report back the results! – Josef Korbel Aug 28 '18 at 11:48
Just by omiting the useless queries I managed to drop the querycount to half (125), continuing with select_related and prefetch_related, even though it is not as clear as I would imagine it. – Josef Korbel Aug 28 '18 at 11:53
@JosefKorbel you may want to read this: https://stackoverflow.com/questions/31237042/whats-the-difference-between-select-related-and-prefetch-related-in-django-orm – bruno desthuilliers Aug 28 '18 at 11:57
Nice reading, I did understand it somehow, but it did not help, it is worse in fact. Dont you mind reading my edited question? – Josef Korbel Aug 28 '18 at 12:11
Looks like your last attempt is getting better. – bruno desthuilliers Aug 28 '18 at 15:28

Terrible Django Admin Performance

1 Answers1