StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODjango Query Optimization - Using Itertools
text
Body
copied!<p>I have an educational site that serves up curriculum based upon Grade level.</p> <ol> <li><p>The GradeLevel table stores all of the possible Grade Levels. Then I have LessonCategories and LessonCurriculum tables.</p></li> <li><p>The GradeLevel table has reverse relationships established with the curriculum and categories tables.</p></li> <li><p>I loop through each grade in the GradeLevel table, (8 grades), and grab the respective, curriculum and categories along the way.</p></li> <li><p>Upon completion, I stuff all of the collected curriculum and categories into a list and and pass that to my template.</p></li> </ol> <p>Now, the problem is that Django is evaluating each query AT LEAST twice. Once for the initial request and the second when I put it into the list. (I am using itertools to chain the results. Itertools is causing the queries to be run again.) This is having the adverse effect of slowing my server down to a crawl.</p> <p>My question is if someone could take a look at my models and queries and make a suggestion as to a better way to query as to avoid and/or mitigate this MAJOR performance bottle-neck.</p> <p><strong>GradeLevel Model:</strong></p> <pre><code>class GradeLevel(models.Model): title = models.CharField('Grade',max_length=10, null=True, blank=True, db_index=True) fullname = models.CharField('Description',max_length=100, null=True, blank=True, db_index=True) </code></pre> <p><strong>LessonCategory Model:</strong></p> <pre><code>class LessonCategory(models.Model): title = models.CharField(max_length=255, null=True, blank=True, db_index=True) ... gradelevel = models.ManyToManyField(GradeLevel, related_name='grade_cats', null=True, blank=True) </code></pre> <p><strong>LessonCurriculum:</strong></p> <pre><code>class LessonCurriculum(models.Model): title = models.CharField(max_length=255, null=True, blank=True, db_index=True) ... gradelevel = models.ManyToManyField(GradeLevel, related_name='grade_curriculum', null=True, blank=True) </code></pre> <p>My View:</p> <pre><code>from itertools import chain from operator import attrgetter def my_view(request): grade_pk = GradeLevel.objects.prefetch_related().get(title='pre-k') grade_pk_categories = grade_pk.grade_cats.filter(active=True,featured=True) grade_pk_galleries = grade_pk.grade_curriculum.filter(active=True,featured=True) grade_k = GradeLevel.objects.prefetch_related().get(title='k') grade_k_categories = grade_k.grade_cats.filter(active=True,featured=True) grade_k_galleries = grade_k.grade_curriculum.filter(active=True,featured=True) grade_1 = GradeLevel.objects.prefetch_related().get(title='1') grade_1_categories = grade_1.grade_cats.filter(active=True,featured=True) grade_1_galleries = grade_1.grade_curriculum.filter(active=True,featured=True) grade_2 = GradeLevel.objects.prefetch_related().get(title='2') grade_2_categories = grade_2.grade_cats.filter(active=True,featured=True) grade_2_galleries = grade_2.grade_curriculum.filter(active=True,featured=True) grade_3 = GradeLevel.objects.prefetch_related().get(title='3') grade_3_categories = grade_3.grade_cats.filter(active=True,featured=True) grade_3_galleries = grade_3.grade_curriculum.filter(active=True,featured=True) grade_4 = GradeLevel.objects.prefetch_related().get(title='4') grade_4_categories = grade_4.grade_cats.filter(active=True,featured=True) grade_4_galleries = grade_4.grade_curriculum.filter(active=True,featured=True) grade_5 = GradeLevel.objects.prefetch_related().get(title='5') grade_5_categories = grade_5.grade_cats.filter(active=True,featured=True) grade_5_galleries = grade_5.grade_curriculum.filter(active=True,featured=True) grade_6 = GradeLevel.objects.prefetch_related().get(title='6') grade_6_categories = grade_6.grade_cats.filter(active=True,featured=True) grade_6_galleries = grade_6.grade_curriculum.filter(active=True,featured=True) grade_7 = GradeLevel.objects.prefetch_related().get(title='7') grade_7_categories = grade_7.grade_cats.filter(active=True,featured=True) grade_7_galleries = grade_7.grade_curriculum.filter(active=True,featured=True) grade_8 = GradeLevel.objects.prefetch_related().get(title='8') grade_8_categories = grade_8.grade_curriculum.filter(active=True,featured=True) grade_8_galleries = grade_8.grade_curriculum.filter(active=True,featured=True) gallery_list = list(set(sorted(chain(grade_pk_categories,grade_pk_galleries,grade_k_categories,grade_k_galleries,grade_1_categories,grade_1_galleries,grade_2_categories,grade_2_galleries,grade_3_categories,grade_3_galleries,grade_4_categories,grade_4_galleries,grade_5_categories,grade_5_galleries,grade_6_categories,grade_6_galleries,grade_7_categories,grade_7_galleries,grade_8_categories,grade_8_galleries), key=attrgetter('display_order')))) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload