CODEWITHSUNDEEP
pythondjangodjango-models
Robert
Robert

Reputation: 325

Django query effective in a loop

I made a functionality on a legacy database, this is working proper. But there are troubles with the performance. By analyzing I have notice that query in a loop has an impact on the performance. See the code below how the functionality is developed this is small part of the code. 

class Article(ReadOnlyModel, models.Model):
  article_id = models.IntegerField(primary_key=True,db_column=u'lArticle_id')
  description = models.TextField(db_column=u'cDescription')
  article_number = models.TextField(db_column=u'cArticlenr', blank=True)

class ArticleImage(ReadOnlyModel, models.Model):
    article_image_id = models.IntegerField(primary_key=True, db_column='intArticleImageId')
    article = models.ForeignKey('Article', db_column=u'intArticleId', related_name='images')
    image_link = models.TextField(db_column='chvImageLink', blank=True)
    image_kind = models.SmallIntegerField(null=True, db_column='inyImageKind', blank=True)
    insert_date = models.DateTimeField(null=True, db_column='dtmInsertDate', blank=True)

articles = Article.objects.all()

result = []
for article in articles:
    # Get image from article
    try:
        image = article.images.filter(image_kind=1).latest('insert_date').image_link
    except ArticleImage.DoesNotExist:
        image = ""

    soap_fabric = soap.SFabric(
        id=article.article_id,
        name=article.description,
        articleNumber=article.article_number,
        image=image
    result.append(soap_fabric)

    return result

How can I improve this part of code?

Upvotes: 1

Views: 95

Answers (2)

catavaran
catavaran

Reputation: 45575

You can reduce number of queries to two:

  1. Load latest image links into the dictionary;
  2. Use this dict in the article loop.

Here is the example:

images_qs = ArticleImage.objects.filter(image_kind=1) \
                        .values('article', 'image_link', 'insert_date') \
                        .order_by('-insert_date')
images = {}
for img in images_qs:
    images.setdefault(img['article'], img['image_link'])

result = [soap.SFabric(id=article.article_id,
                       name=article.description,
                       articleNumber=article.article_number,
                       image=images.get(article.article_id, ''))
          for article in Article.objects.all()]

Upvotes: 0

Bernhard Vallant
Bernhard Vallant

Reputation: 50786

If the IDs of your images increment over time you could try something like this to get all latest images in one query and a subquery(instead of doing an extra query for every article):

   from django.db.models import Max

   image_ids = Article.objects.annotate(
        latest_image_id=Max('images__article_image_id')
   ).values_list('latest_image_id', flat=True)   

   latest_images = ArticleImage.objects.filter(
       pk__in=image_ids
   ).select_related('article')

Upvotes: 1

Related Questions