Re: Prefetch() with through models

On 16/09/2015 9:53 AM, Erik Cederstrand wrote:
> Hi folks,
>
> I'm working on a school timetable app. I want to fetch hundreds of
> thousands of Lesson instances with prefetched m2m relations (e.g.
> subjects). My m2m relations use through models (I'm not sure this
> actually makes a difference here), and I'm running into performance
> issues because the prefetch query does something along the lines of
> "SELECT ... FROM lesson_subjects WHERE lesson_id IN
> [insane_list_of_lesson_ids]".

I'm no expert so I'm wondering if len([insane_list_of_lesson_ids]) ==
"hundreds of thousands"?

And if there are that many involved, why wouldn't the infrastructure
groan and creak a little?

I'm not much help but I do remember many years ago a school timetable
programming guru who told me they are definitely not trivial. I think he
was referring to timetable creation given enrolments, student
preferences, curriculum/course requirements, availability of teachers
and location of campuses. One of the main issues (ISTR) was memory - or
lack of it.

Someone other than me is going to have to help on this but I am
interested in the solution.

Good luck

Mike
>
> The initial query on Lesson uses a properly indexed filter on e.g.
> dates, so I thought I'd try to use the same filter to get the related
> Subjects via the relation to Lessons:
>
> qs = Subject.objects.filter(lessons__school_id=8,
> lessons__start__lt=datetime(2015, 8, 1))
> Lesson.objects.filter(school_id=8, start__lt=datetime(2015, 8, 1))\
> .prefetch_related(Prefetch('subjects', queryset=qs))
>
> I can see that the extra filters are added to the prefetch query, but
> the huge IN clause is still there.
>
> Am I using Prefetch() wrong? Are there any other techniques to avoid
> the huge IN clause in prefetch queries?
>
> Thanks, Erik
>

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/55F9808E.5060204%40dewhirst.com.au.
For more options, visit https://groups.google.com/d/optout.