Search code examples
djangodjango-querysetdjango-ormdjango-mysql

Django - Raw SQL Queries or Django QuerySet ORM

I know Django Object Relational Mapper (ORM) helps bridge the gap between the database and our code Performing raw queries.

But I want to find out which is better - Raw SQL Queries or Django QuerySet ORM.

So for that I was query on User Table -

from django.contrib.auth.models import User

Then i queried Django ORM QuerySet - 

orm_query = User.objects.all()
orm_query
<QuerySet [<User: superadmin>]>

After QuerySet I user raw - 

raw_query = User.objects.raw("select * from auth_user")
raw_query
<RawQuerySet: select * from auth_user>

And then I tried to print those queries using .query and it's output - 

print(orm_query.query)
SELECT `auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined` FROM `auth_user`

print(raw_query.query)
select * from auth_user

And i found orm_query much longer than raw_query. I want to know which one is best raw or orm query. Which should i use for best performance. What is the difference between them.

Solution

  • The query generated by Django's ORM selects every column explicitly, while your raw query selects all columns with the *. The result should be the same in both cases, you can stay without any problems with the ORM's query. If you want to omit certain columns from the result you can still use the queryset methods only() and defer() which will reduce the payload being returned from the database as well as the work that is necessary to convert the raw data into python objects.

    Most likely you only need to fall back to raw sql queries for solving use cases that Django's ORM is not capable of. In most cases the biggest performance impacts will be caused by not accessing related objects in the right way (see select_related() and prefetch_related() for more information on that) or not using indexes correctly. Also see Django's documentation on database access optimization.