CODEWITHSUNDEEP
djangodjango-querysetdjango-ormdjango-mysql
Cipher
Cipher

Reputation: 2122

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.

Upvotes: 2

Views: 5124

Answers (3)

Daniel Holmes
Daniel Holmes

Reputation: 2002

In general, the Django ORM is pretty good at doing what you typically need for database queries. It becomes easier and particularly useful for complex queries. Writing raw queries for these cases may become very tedious and time consuming. This is why development time may become as issue. In most cases, the ORM will be just as fast as raw SQL queries. So the ORM should be your default choice.

In those few cases where performance may become an issue, following the recommendations in Bernhard Vallant's answer should be the first things you try; select_related, prefecth_related, database indexes, and so on.

Upvotes: 1

user8060120
user8060120

Reputation:

It doesn't matter how much length of the sql text, if you want to compare performance you should use the something like EXPLAIN ANALYZE (example for the postgres), read the answers for the mysql

dev=> EXPLAIN ANALYZE 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;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on auth_user  (cost=0.00..10.50 rows=50 width=1527) (actual time=0.004..0.004 rows=0 loops=1)
 Planning time: 0.124 ms
 Execution time: 0.032 ms
(3 rows)

dev=> EXPLAIN ANALYZE select * from auth_user;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on auth_user  (cost=0.00..10.50 rows=50 width=1527) (actual time=0.004..0.004 rows=0 loops=1)
 Planning time: 0.114 ms
 Execution time: 0.032 ms
(3 rows)

as you can see the Execution time is equal.

Upvotes: 2

Bernhard Vallant
Bernhard Vallant

Reputation: 50776

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.

Upvotes: 3

Related Questions