666 Django projects checked for inefficient database queries. Over half had these 4 anti-patterns
Tags: django, webdev, python, performance
Published: 2021-01-03 14:50:32

All mature codebases contain anti-patterns: code problems that introduce security holes, tech debt that raises the cost of working with the codebase, and code that goes against best practice that slows it down.

[Django Doctor](https://django.doctor) performs static analysis to find and auto fix such problems. We checked 666 Django codebases for inefficient Django ORM calls and were surprised with the extent of the problem: **50% of all Django codebases we checked had the following anti patterns**:

- 16% used `queryset.count() > 0` instead of `queryset.exists()`
- 15% used `len(queryset)` instead of `queryset.count()`
- 10% checked `if queryset:` instead of `if queryset.exists():`
- 10% did not use foreign keys directly

These at best slow down the Django app, and at worse can bring production down.

_How would you solve these performance anti-patterns? Try [our Django performance refactor challenge](https://django.doctor/challenge/database-queries)._

Given these problems were present in over half of the  Django github repositories checked, there's a good chance that yours has at least one of these problems. An experienced Django dev may think they would not make these mistakes, but the thing is no dev is an island: over time devs change teams, inherit brownfield codebase that have accumulated tech debt, and will review code written by junior devs. It's therefore important to:

- avoid the mistake
- know how to effectively find and fix the mistake
- know how to communicate to junior devs _why_ it's a problem.

### **Using `.count() > 0` instead of `.exists()`**

Comparing `queryset.count()` is less efficient than checking `queryset.exists()`. The [Django docs](https://docs.djangoproject.com/en/3.1/topics/db/optimization/#use-queryset-exists) tells us to use `queryset.count()` if you only want the count, and use `queryset.exists()` if you only want to find out if at least one result exists.

The reason for this is because `queryset.count()` performs an SQL operation that scans _every_ row in the database table to calculate the sum. On the other hand `queryset.exists()` simply reads a single record in [the most optimized way](https://github.com/django/django/blob/b996424199cca58f64b77faeaa367b7e7efb9776/django/db/models/sql/query.py#L528):
- remove ordering
- remove grouping
- clear any dev-defined `select_related` and `distinct` from the queryset

So instead of 

```py
def check_hounds():
    queryset = HoundsModel.objects.all()
    if queryset.count() > 0:
         return "oh no. Run!"
```

Django Doctor would auto fix this to:

```py
def check_hounds():
    queryset = HoundsModel.objects.all()
    if queryset.exists():
         return "oh no. Run!"
```

[Read more](https://django.doctor/advice/C9002)

### **Using `len(queryset)` instead of `queryset.count()`**

`len(queryset)` performs the count at application level. That is much less efficient than doing `queryset.count()`, which does the calculation at database level and just returns the count.

Querysets are [lazily evaluated](https://docs.djangoproject.com/en/3.1/topics/db/queries/#querysets-are-lazy) - meaning the records are not read from the database until code interact with the data. Thats's why we can do `queryset.all()` without downloading every records from the database.

An example of something that interacts with the data is doing `len(queryset)`. This *will* read all the records in the queryset: effectively downloading the database over the internet. Not particularly efficient.

On the other hand doing `queryset.count()` handles the calculation at the database level by executing SQL like `SELECT COUNT(*) FROM table`. That means using `queryset.count()` makes the code quicker, and improves database performance. Plus imagine the waste in downloading 5000 records only to check the length and throw them away at the end!

So instead of

```py
def check_hounds():
    queryset = HoundsModel.objects.all()
    if len(queryset) > 2:
         return "oh no. Run!"
```

Django Doctor would auto fix this to:

```py
def check_hounds():
    if HoundsModel.objects.count() > 2:
         return "oh no. Run!"
```

Having said that though, if the records will need reading after the length is checked, then `len(queryset)` can be valid.

[Read more](https://django.doctor/advice/C9000)

### **Using `if queryset:` instead of `if queryset.exists():`**

Similar to above, this can load every row in the database table into memory because the queryset is evaluated. Checking if a queryset is truthy/falsey is much less efficient than checking `queryset.exists()`.

This is especially inefficient if the table is very large: it can cause a CPU spike on the database and use a lot of memory on the web server. So instead of pulling every single row from the table, check `queryset.exists()`, which simply tries to read a single record from the table in a very efficient way.

So instead of

```py
def check_hounds():
    queryset = HoundsModel.objects.all()
    if queryset:
         return "oh no. Run!"
```

Django Doctor would auto fix this to:

```py
def check_hounds():
    queryset = HoundsModel.objects.all()
    if queryset.exists():
         return "oh no. Run!"
```

[Read more](https://django.doctor/advice/C9003)

### **Not use foreign keys directly**

When working with foreign keys, accessing `model_instance.related_field.id` will result in a database read when `related_field.id` is evaluated. That can be eliminated by doing `model_instance.related_field_id`, which is the foreign key value that Django has already cached on the object to make this scenario more efficient.

So instead of

```py
def check_hounds(pk, farm_ids):
    hound = HoundsModel.objects.get(pk=pk)
    if hound.farm.id in farm_ids:
        ...
```

Django Doctor would auto fix this to:

```py
def check_hounds(pk, farm_ids):
    hound = HoundsModel.objects.get(pk=pk)
    if hound.farm_id in farm_ids:
      ...
```

[Read more](https://django.doctor/advice/C9001)

### Does your Django code have these anti-patterns?

Over time it's easy for tech debt to slip into your codebase. I can check that for you at [django.doctor](https://django.doctor), or can [review your GitHub PRs](https://github.com/marketplace/django-doctor/):

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/d10epulva9d373jgldhu.png)

Or try out [Django refactor challenges](https://django.doctor/challenge).