CODEWITHSUNDEEP
mysqldjangodjango-models
Krish V
Krish V

Reputation: 546

How to reduce the number of database hits while querying in Django

I have three tables

  1. User
  2. Device
  3. Log

I want to filter the logs based on devices and logs. I'm using the following querying which iterates over the users and devices in order to get the logs. I feel this will become a performance hit. How to reduce the number of database hits?

for user_obj in User.objects.all():
    device_qs = Device.objects.filter(user=user_obj)
    if device_qs.exists():
        for device_obj in device_qs:
            log_count = Log.objects.filter(user=user_obj, device=device_obj, created_at__range(from_date, to_date)).count()

Upvotes: 1

Views: 778

Answers (2)

Endre Both
Endre Both

Reputation: 5740

If you only need the log count per user and device (which is what you get from the code you posted), you can get that in just one query:

from django.db.models import Count

logs = (Log.objects
    .filter(created_at__range = (from_date, to_date))
    .values('user', 'device')
    .annotate(log_count=Count('device'))
)

You can modify the query to include any attributes of the user and device models that you need:

.values('user__last_name', 'device__name')  # etc.

You can also order the dataset by appending order_by() at the end to be able to iterate over it in the desired order:

.order_by('user__last_name', '-log_count')

Upvotes: 1

Lord Elrond
Lord Elrond

Reputation: 16042

What I would do is create a "proxy model" that references a view in your MySQL instance

The view would look like this:

SELECT 
t1.*,
t2.*,
t3.*
FROM users t1
RIGHT JOIN device t2 (ON t1.id=t2.user_id)
RIGHT JOIN log t3 (ON t3.device_id=t2.id);

Now to create a proxy model, do this:

class SomeModel(models.Model):
    # all fields from the 3 tables here

    class Meta:
        db_table = 'yourViewNameHere'
        managed = False # this keeps django from creating the table

then python manage.py makemigrations + python manage.py migrate as usual

Now, to access the the data you need, you would do something like this:

from django.db import connection
sql = "SELECT * FROM your_view WHERE some_date_column > 'foo' AND some_date_column < 'bar' "

with connection.cursor() as cur:

    cur.execute(sql)
    data = cur.fetchall()

print(data)

Note that if you are passing parameters to the raw sql query, you should always pass them like this to avoid sql injection:

sql = "SELECT * FROM your_view WHERE some_date_column > %s AND some_date_column < %s"

params = ('foo', 'bar')
with connection.cursor() as cur:

    cur.execute(sql, params)
    data = cur.fetchall()

Upvotes: 1

Related Questions