How does the select_related and prefetch_related functions of Django optimize QuerySet queries? (3) djangoqueryset

How does the select_related and prefetch_related functions of Django optimize QuerySet queries? (3) djangoqueryset

This is the last article in this series, mainly the best practices of select_related () and prefetch_related.

The first section describes the example and select_related ()

Article 2 prefetch_related ()

4. Some Instances
Select a function. If we want to obtain people from Hubei Province, the brainless way is to first obtain people from Hubei Province, then all cities in Hubei province, and finally get people from this city. Like this:

>>> Hb = Province. objects. get (name _ iexact = u "Hubei Province") >>>> people = [] >>> for city in hb. city_set.all ():... people. extend (city. birth. all ())...

Obviously, this is not a wise choice, because this will lead to 1 + (number of cities in Hubei Province) SQL queries. It is an inverse example, and the query and result obtained will not be listed.
Prefetch_related () may be a good solution. Let's take a look.

>>> Hb = Province. objects. prefetch_related ("city_set _ birth "). objects. get (name _ iexact = u "Hubei Province") >>>> people = [] >>> for city in hb. city_set.all ():... people. extend (city. birth. all ())...

Because it is a prefetch with a depth of 2, it will cause three SQL queries:

SELECT 'qsoptimize _ province '. 'id', 'qsoptimize _ province '. 'name' FROM 'qsoptimize _ province 'WHERE 'qsoptimize _ province '. 'name' LIKE 'hubei province '; SELECT 'qsoptimize _ City '. 'id', 'qsoptimize _ City '. 'name', 'qsoptimize _ City '. 'province _ id' FROM 'qsoptimize _ City' WHERE 'qsoptimize _ City '. 'vince _ id' IN (1); SELECT 'qsoptimize _ person '. 'id', 'qsoptimize _ person '. 'firstname', 'qsoptimize _ person '. 'lastname', 'qsoptimize _ person '. 'hometown _ id', 'qsoptimize _ person '. 'Living _ id' FROM 'qsoptimize _ person' WHERE 'qsoptimize _ person '. 'hometown _ id' IN (1, 3 );

Well... it looks good, but is it three queries? Which of the following queries may be simpler?

>>> People = list (Person. objects. select_related ("hometown _ province"). filter (hometown _ province _ name _ iexact = u "Hubei province "))

SELECT 'qsoptimize _ person '. 'id', 'qsoptimize _ person '. 'firstname', 'qsoptimize _ person '. 'lastname', 'qsoptimize _ person '. 'hometown _ id', 'qsoptimize _ person '. 'Living _ id', 'qsoptimize _ City '. 'id', 'qsoptimize _ City '. 'name', 'qsoptimize _ City '. 'province _ id', 'qsoptimize _ province '. 'id', 'qsoptimize _ province '. 'name' FROM 'qsoptimize _ person 'inner join 'qsoptimize _ City' ON ('qsoptimize _ body '. 'hometown _ id' = 'qsoptimize _ City '. 'id') inner join 'qsoptimize _ province 'ON ('qsoptimize _ City '. 'province _ id' = 'qsoptimize _ province '. 'id') WHERE 'qsoptimize _ province '. 'name' LIKE 'hubei province ';

+ ---- + ----------- + ---------- + ------------- + ----------- + ---- + -------- + ------------- + ---- + -------- + | Id | firstname | lastname | response | living_id | id | name | province_id | id | name | + ---- + ----------- + ---------- + ------------- + --------- + ---- + -------- + ------------- + ---- + -------- + | 1 | Zhang | 3 | 3 | 3 | Shiyan city | 1 | 1 | Hubei Province | 2 | Li | 4 | 1 | 3 | 1 | Wuhan City | 1 | 1 | Hubei Province | 3 | Wang | Machin | 3 | 2 | 3 | Shiyan city | 1 | 1 | Hubei Province | + ---- + ----------- + ---------- + ------------- + ----------- + ---- + -------- + ------------- + ---- + -------- + 3 rows in set (0.00 sec)

No problem at all. This not only reduces the number of SQL queries, but also simplifies the python program.
Select_related () is more efficient than prefetch_related (). Therefore, it is best to use select_related () Wherever possible, that is, for the ForeignKey field, avoid using prefetch_related ().


You can use both functions for the same QuerySet. Add a model: Order (Order) to the example we have been using)

class Order(models.Model):    customer   = models.ForeignKey(Person)    orderinfo  = models.CharField(max_length=50)    time       = models.DateTimeField(auto_now_add = True)    def __unicode__(self):        return self.orderinfo

If we get an order id, we need to know the province that the customer of this order has been. Because ManyToManyField must use prefetch_related (). What if I only use prefetch_related?

>>> plist = Order.objects.prefetch_related('customer__visitation__province').get(id=1)>>> for city in plist.customer.visitation.all():...   print city.province.name...

Obviously, four tables are related: Order, Person, City, and Province. According to the prefetch_related () feature, four SQL queries are required.

SELECT `QSOptimize_order`.`id`, `QSOptimize_order`.`customer_id`, `QSOptimize_order`.`orderinfo`, `QSOptimize_order`.`time` FROM `QSOptimize_order` WHERE `QSOptimize_order`.`id` = 1 ;SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id` FROM `QSOptimize_person` WHERE `QSOptimize_person`.`id` IN (1);SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id` FROM `QSOptimize_city` INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`) WHERE `QSOptimize_person_visitation`.`person_id` IN (1); SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` FROM `QSOptimize_province`WHERE `QSOptimize_province`.`id` IN (1, 2);

+ ---- + ------------- + Hour + | id | customer_id | orderinfo | time | + ---- + ------------- + --------------- + hour + | 1 | 1 | Info of Order | 17:05:48 | + ---- + ------------- + --------------- + ------------------- + 1 row in set (0.00 sec) + ---- + ----------- + ---------- + ------------- + ----------- + | id | firstname | lastname | region | living_id | + ---- + ----------- + ---------- + ------------- + ----------- + | 1 | 3 | 3 | 1 | + ---- + ----------- + ---------- + ------------- + ----------- + 1 row in set (0.00 sec) + keys + ---- + -------- + ------------- + | _ prefetch_related_val | id | name | province_id | + --------------------- + ---- + -------- + ------------- + | 1 | 1 | Wuhan | 1 | 2 | Guangzhou | 2 | 1 | 3 | Shiyan city | 1 | + ------------------------- + ---- + -------- + ------------- + 3 rows in set (0.00 sec) + ---- + -------- + | id | name | + ---- + -------- + | 1 | Hubei Province | 2 | Guangdong Province | + ---- + -------- + 2 rows in set (0.00 sec)

A better solution is to call select_related () First, call prefetch_related (), and then the table after select_related ().

>>> plist = Order.objects.select_related('customer').prefetch_related('customer__visitation__province').get(id=1)>>> for city in plist.customer.visitation.all():...   print city.province.name...

In this way, there will be only three SQL queries. Django will first make select_related, and then use the previously cached data when prefetch_related, thus avoiding one additional SQL query:

SELECT `QSOptimize_order`.`id`, `QSOptimize_order`.`customer_id`, `QSOptimize_order`.`orderinfo`, `QSOptimize_order`.`time`, `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id` FROM `QSOptimize_order` INNER JOIN `QSOptimize_person` ON (`QSOptimize_order`.`customer_id` = `QSOptimize_person`.`id`) WHERE `QSOptimize_order`.`id` = 1 ;SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id` FROM `QSOptimize_city` INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`) WHERE `QSOptimize_person_visitation`.`person_id` IN (1);SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` FROM `QSOptimize_province` WHERE `QSOptimize_province`.`id` IN (1, 2);

+ ---- + ------------- + Hour + ---- + ----------- + ---------- + ----------- + | id | customer_id | orderinfo | time | id | firstname | lastname | hometown_id | living_id | + ---- + ------------- + --------------- + certificate + ---- + ----------- + ---------- + ------------- + ----------- + | 1 | 1 | Info of Order | 17:05:48 | 1 | 3 | 3 | 1 | + ---- + ------------- + --------------- + ------------------- + ---- + ----------- + ------------ + ------------- + ----------- + 1 row in set (0.00 sec) + keys + ---- + -------- + ------------- + | _ prefetch_related_val | id | name | province_id | + --------------------- + ---- + -------- + ------------- + | 1 | 1 | Wuhan | 1 | 2 | Guangzhou | 2 | 1 | 3 | Shiyan city | 1 | + ------------------------- + ---- + -------- + ------------- + 3 rows in set (0.00 sec) + ---- + -------- + | id | name | + ---- + -------- + | 1 | Hubei Province | 2 | Guangdong Province | + ---- + -------- + 2 rows in set (0.00 sec)

It is worth noting that select_related can be called before prefetch_related is called, and Django will do what you want: Select t_related first, and then use the cached data prefetch_related. However, once prefetch_related has been called, select_related does not work.

Summary


There are only so many things I can think of about these two functions. However, for some personal reasons, it takes a short time to write the third article, which is a little hasty. If you remember something, I will add it in this blog.

Ask a question about queryset in django

From itertools import chainfrom operator import attrgetter #... post = Post. objects. get (pk = post_id) # get blog likes = post. like_set.all () # Get favorite information # likes = Like. objects. filter (post = post) reblogs = Post. objects. filter (reblog_from = post) # Get forwarding information # merge favorite and forwarding information, and sort by time in reverse order. notes = sorted (chain (likes, reblogs ), key = attrgetter ('created _ at'), reverse = True )#... use itertools. the chain function merges the iteratable objects, and the query set is the iteratable object:
>>> List (chain ([1, 2, 3], 'abc') >>> [1, 2, 3, 'A', 'B ', 'C'] sort by object attributes using the sorted function ).


DJANGO problem: the delete method of queryset

Try the following statement to see if all the statements will be deleted.
> From models import Entry
> Query = Entry. all ()
> Entries = query. fetch (1)
> Db. delete (entries)