Django Database Performance Optimization Using Python Set Operations

Preface

Recently there was a new requirement:

• Personnel basic information (referred to as Personnel A), 100k
• A certain type of personnel information (referred to as Personnel B), 1000

The requirement is to display separately in the backend (Django Admin): the list of Personnel B already entered in A, and the list of Personnel B not yet entered

The team's DBA suggested using a view to solve this problem (as expected from a database person)

PS: At first I thought Django's Model directly manages database tables, so using Model to map a database model might be somewhat troublesome, but after looking it up I found that's not the case~

You just need to add managed = False in the Model's Meta subclass, and set the db_table attribute to the view name

But this project is written in Django, and I figured since we have such a handy ORM, why go to the extra trouble of implementing the functional logic in SQL?

So with a wave of my hand: no way, we'll do this in Python!

Rough Implementation

There are quite a few ways to implement this. You could add a method on the Model, such as is_in_a(), and inside it check whether that Personnel B is in Personnel A

You could also add a field in the admin configuration~

In the end I added a new method on the Model. Of course you don't have to limit yourself to this approach—Django is quite flexible.

The method looks roughly like this

def is_in_a(self) -> bool:
    from apps.people.models import PersonA
    queryset = PersonA.objects.filter(id_number=self.id_number)
    return queryset.exists()

is_in_a.short_description = '是否已录入'
is_in_a.boolean = True

OK, no issues. Next, configure admin

@admin.register(PersonB)
class PersonBAdmin(admin.ModelAdmin):
    list_display = ['name', 'id_number', 'is_in_a']

This way it can be displayed normally in the backend, and is_in_a can be used just like a regular model field

However, if you want to add a filter, that won't work—admin's default list_filter can only support database fields

To add our custom field to the filters, we can only write a Filter ourselves

First, let's look at a wrong example

class IsInAFilter(admin.SimpleListFilter):
    title = '是否已录入'
    parameter_name = 'is_in_a'

    def lookups(self, request, model_admin):
        return (
            ('true', '已录入'),
            ('false', '未录入')
        )

    def queryset(self, request, queryset):        
        raw_ids = []
        if self.value() == 'true':
            for item in queryset:
                if item.is_in_a():
                    raw_ids.append(item.pk)

        if self.value() == 'false':
            for item in queryset:
                if not item.is_in_a():
                    raw_ids.append(item.pk)

        return queryset if self.value() is None else queryset.filter(pk__in=raw_ids)

After writing it, just put it in the admin filter configuration

list_filter = [IsInAFilter]

It works, sure, but filtering is extremely slow, because when rendering the list, every item has to hit the database once (frankly, this kind of code is shit)

PS: Unfortunately, this code was copied from a project I wrote two years ago (runs away)

Optimization Approach

I definitely couldn't stand for this

What I hate most is people writing shit-mountain code

What's more, this was my own shit, even less tolerable

Time to start optimizing the code right away!

Calm down, think it through a bit

What's slow about this is that every item in the list has to check whether id_number is in Personnel A. So why not switch to batch checking?

When I think of batch, I think of values_list, using it to generate two lists of id_number. And once we have two lists, isn't that just set operations?

That's settled, let's do it!

Sets

First, a quick review of sets

This should be high school math

A set is the grouping of several objects into one or more wholes of various forms and sizes. Generally speaking, a set is a whole of things that share a certain characteristic, or a collection of certain identified objects. The things or objects that constitute a set are called “elements” or “members”. The elements of a set can be anything: they can be people, objects, or letters or numbers, and so on.

The three main properties of sets

Unorderedness: In a set, every element has equal status; there is no order among the elements.

• An order relation can be defined on a set, and once an order relation is defined, the elements can be ordered according to it. But as far as the properties of the set itself are concerned, there is no inherent order among the elements. (See order theory)

Distinctness: In a set, any two elements are considered different, i.e., each element can appear only once.

• Sometimes you need to describe the situation where the same element appears multiple times; you can use a multiset, whose elements are allowed to appear more than once.

Determinacy: Given a set and any given element, that element either belongs to the set or does not belong to the set—one of the two must hold; ambiguous cases are not allowed.

No need to delve deep into the math concepts. Sets in programming languages are also somewhat different from mathematical sets, but they both have distinctness, meaning there are no duplicate elements in a set.

Set Operations

To achieve the performance optimization mentioned earlier, here we only need to master a few set operations

Let a and b be two different sets

a = set([1, 2, 3, 4])
b = set([3, 4, 5, 6])

Just look at the table for the four operations

For easier understanding, let's draw a diagram

That's clear now, right~

So the problem above, simplified to set operations, is taking the intersection and the difference respectively

Final Implementation

The final implementation not only performs much better, but also has less code than before—simply perfect

def queryset(self, request, queryset):
    from apps.people.models import PersonA

    # 使用集合操作提高性能
    set1 = set(PersonA.objects.values_list('id_number', flat=True))
    set2 = set(queryset.values_list('id_number', flat=True))

    id_numbers = set()

    # 选择已录入的，取交集
    if self.value() == 'true':
        id_numbers = set1 & set2
    # 选择未录入的，取差集
    elif self.value() == 'false':
        id_numbers = set2 - set1

    return queryset if self.value() is None else queryset.filter(id_number__in=id_numbers)

Done~!

Hold On

Finally, let me recommend some good stuff I found while looking things up

Django ORM Cookbook

Chinese edition: https://django-orm-cookbook-zh-cn.readthedocs.io/zh_CN/latest/index.html

This is a book that, as the name suggests, teaches you how to use Django ORM. It has 50 examples, seems pretty good, can help fill in the gaps~

Intermediate Python

Chinese edition: https://eastlakeside.gitbook.io/interpy-zh/

Also a book, Chinese title “Python进阶”, so you should know what it's about~

References

• Set (mathematics) - Wikipedia: https://zh.m.wikipedia.org/zh/集合_(数学)
• python set operations (intersection, union, difference, symmetric difference): https://blog.csdn.net/sxingming/article/details/51922776
• Common Python set (Set) operations: https://www.jianshu.com/p/f60fabfefc09
• https://www.runoob.com/python3/python3-set.html