How to reduce the time for this code in django rest framework-CodePudding

class Category:
  title = models.CharField(max_length=50)

class Tag:
  name = models.CharField(max_length=50)
class Video:
  video = models.FileField(upload_to='xxx/')
class Exercise:
  name = models.CharField(max_length=50)
  video = models.ForeignKey(Video)    
  description = models.CharField(max_length=250)
  category = models.ForeignKey(Category, on_delete=models.CASCADE)
  tag = models.ManyToManyField(tag, on_delete=models.CASCADE)
class Data:
  relation = models.ForeignKey(Relation, on_delete=models.CASCADE)
  exercise = models.ForeignKey(Exercise, on_delete=models.CASCADE)

The code for getting data accordingly this is taking too much time how do I reduce this or what are the ways to handle these kind of situations

for each in Data:
        sam.append(
            {
                "name": each.exercise.name,
                "url": each.exercise.video.video.url,
                "description": each.exercise.description,
                "category": each.exercise.category.title,
                "tag": each.exercise.tag.name
            }
        )

CodePudding user response：

Many times it’s not the database that’s slow, but the queries you send to it. This is especially true when working with large databases like Postgres. If you have a query that returns 5 million rows, and takes 0.1 seconds to run on your machine, then when you run it in your database server, it might take 100x longer. When possible avoid doing database reads in Python and do everything in SQL (and just pass Python the results). To create a view with all fields:

CREATE OR REPLACE VIEW all_exercises AS SELECT exercise.*, data.* FROM exercise JOIN data ON data.exercise_id = exercise.id;
SELECT * FROM all_exercises WHERE id = 1234; // this will run instantly even though the initial query was slow;

CodePudding user response：

You'll want to use select_related..[Django-doc] and prefetch_related..[Django-doc] here to reduce the queries your for loop is doing, otherwise it will do about 4 extra queries per Data object.

So you could so something like:

for each in Data.objects.select_related(
    "exercise__video",
    "exercise__category",
).prefetch_related(
    "exercise__tag",
).all():
    sam.append(
        {
            "name": each.exercise.name,
            "url": each.exercise.video.video.url,
            "description": each.exercise.description,
            "category": each.exercise.category.title,
            "tags": [t.name for t in each.exercise.tag.all()]
        }
    )