With big amount of projects/tasks Projects page get slow to open and makes too much api and db requests

[summeroff]

My actions before raising this issue

• Read/searched the docs
• Searched past issues

Expected Behaviour

Projects page opens fast and not make unnecessary web and db requests.

Current Behaviour

It was faster in the beginning but with growing number of projects and tasks it get slower and slower.
Right now to open Projects it takes 30+ seconds.

In browsers dev tools it shows what /projects request takes 30 seconds to complete and then followed by huge amount of /data?type=preview requests.

Also with enabled query logging on db. It logs like 30000+ sql select queries just when Projects page requested.
To get this logs I add this to compose config

    container_name: cvat_db
    image: postgres:10-alpine
    command: ["postgres", "-c", "log_statement=all", "-c", "log_destination=stderr"]

refreshed page in browser and saved logs by
docker logs 5278709de3c0 &> sql.log.txt

Possible Solution

Steps to Reproduce (for bugs)

Context

Your Environment

• Git hash commit (git log -1): 1790800
• Docker version docker version (e.g. Docker 17.0.05): Docker version 20.10.5, build 55c4c88
• Are you using Docker Swarm or Kubernetes?
• Operating System and version (e.g. Linux, Windows, MacOS): Window locally and ubuntu on vps.
• Code example or link to GitHub repo or gist to reproduce problem:
• Other diagnostic information / logs:
  Logs from `cvat` container

[ActiveChooN]

@summeroff, it was happening due to attempting to serialize and return all nested tasks in the projects' request. So it resulted in numerous queries to the database. The related patch should fix it by retrieving only necessary previews and no nested tasks in the projects' request. Could you give an approximate amount of tasks and labels in your projects for reference, please?

[summeroff]

Checked on dk/fix-issue-3128 branch.
Projects page is fast now but only for a first page "projects?page=1". Any other page is noticeable slower.

Pages of individual projects still slow for projects what has big number of tasks.

[ActiveChooN]

@summeroff, I would reopen the issue and move it to a backlog, if slow responses remain. You are free to open PR if you have a solution.

[bsekachev]

REST API was significantly optimized. Not relevant anymore.