Django多个注释会减慢查询速度 [英] Django multiple annotate slows down the query
问题描述
我一直在使用 django debug_toolbar
进行调试,如果我在查询中使用多个注释,那么Django需要花费很多时间来获取查询结果。
I have been debugging with django debug_toolbar
, if I use more than one annotate in query then it takes a lot of time for Django to fetch the query results.
class Project_First(models.Model):
project_first_results_M2M = models.ManyToManyField(Project_First_Results)
class Project_Second(models.Model):
project_second_results_M2M = models.ManyToManyField(Project_Second_Results)
class Project(models.Model):
project_first_M2M = models.ManyToManyField(Project_First)
project_second_M2M = models.ManyToManyField(Project_Second)
- 我正在尝试计算所有
project_first_M2M
对象中project_first_results_M2M
中存在的所有对象。 - I m trying to count all the objects present in
project_first_results_M2M
of all theproject_first_M2M
objects.
即,让我们假设 project_first_M2M
具有3个<$ c $的对象c> Project_First ,我想计算所有这三个对象中所有 project_first_results_M2M
个对象。
ie, let's suppose, project_first_M2M
has 3 objects of Project_First
and I want to count all the total project_first_results_M2M
objects present in all 3 of them.
Project.objects.all().annotate(first_res_count=Count('project_first_M2M__project_first_results_M2M',distinct=True))
- 上面的查询工作正常,需要80毫秒来获取结果。但是当我尝试添加其他
注释
进行查询时,就会出现问题。 - Above Query works fine and would take 80ms to fetch the results. But problem occurs when i try to add an additional
annotate
to query.
Project.objects.all().annotate(first_res_count=Count('project_first_M2M__project_first_results_M2M',distinct=True)).annotate(second_res_count=Count('project_second_M2M__project_second_results_M2M',distinct=True))
- 这将花费近4000毫秒来获取结果。
- 是否有任何快速替代的解决方案以高效的方式实现相同目标?
- 我想计算所有
project_first_M2M中
project_first_results_M2M
的所有对象 每个 - Is there any fast and alternate solution to achieve the same in much efficient way? maybe with raw sql queries.
- I want to count all the objects of
project_first_results_M2M
of allproject_first_M2M
objects within eachProject
object and similarly forproject_second_results_M2M
project_second_M2M
和 project_first_M2M
都包含相同的内容字段和相同数量的对象。反之,我甚至尝试了上述查询,并且只有当我添加其他注释
时,查询速度才会降低。
Both project_second_M2M
and project_first_M2M
contains the same fields and the same number of objects. I even tried in vice-versa condition to the above query and query slows down only when I add additional annotate
.
Project
对象中的code>个对象,对于 project_second_results_M2M
ul>
推荐答案
可能您可以使用 与预取有关
:
Project.objects.prefetch_related('project_first_M2M__project_first_results_M2M', 'project_second_M2M__project_second_results_M2M').annotate(first_res_count=Count('project_first_M2M__project_first_results_M2M',distinct=True)).annotate(second_res_count=Count('project_second_M2M__project_second_results_M2M',distinct=True))
这篇关于Django多个注释会减慢查询速度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!