Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is the speed of the query I wrote for statistics normal?

I want to know if this query I wrote for statistics is fast. It currently works in 3 seconds with the data and query I wrote below.

Now I have a user table. Those whose role is Hr can create a vacancy. So the user has a vacancy list. Users whose role is candidate can apply to these vacancies and this is stored in the applications table. So there is a list of applications in vacancy. Now I need statistics like this:

  • Names of HR users
  • Total shared vacancy numbers of HR users
  • Total number of completed vacancies of HR users
  • Number of applications that have reached "CV approved" status in HR users’ vacancies
  • Number of applications that have reached "Interview" status in the vacancies of HR users
  • Number of applications that have reached "Exam" status in the vacancies of HR users
  • And +10 more than application status in this type

Row numbers in my database:

  • All users: +125 000 (55 Hr users)
  • Vacancies: 1900
    -Applications: +340 000

My query is as follows:

var statisticsQuery = _context.Users
    .OrderBy(hrUser => hrUser.Id)
    .Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))
    .Select(hrUser => new VacancyByRecruiterModelDto
    {
        RecruiterId = hrUser.Id,
        RecruiterFullName = hrUser.Name + " " + hrUser.Surname,
        PublishedTotalVacancyCount = hrUser.Vacancies.Count(vac => vac.StatusId == 1),
        CompletedTotalVacancyCount = hrUser.Vacancies.Count(vac => vac.StatusId == 2),
        CvCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 1),
        ExamCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 2),
        InterviewCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 3),
        DocumentCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 4),
        HiringSuccessCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 5),
        CvFailedCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 6),
        CandidateFailedCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 7),
        DontSeeCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 8),
        ReserveCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 9),
        InternCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 10),
        SecurityCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 11),
        DontExamCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 12),
        DontInterviewCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 13),
        HiringStoppedCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 14),
        FailInternshipCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 15),
    }).AsNoTracking();

if (loadMore?.Skip != null && loadMore?.Take != null)
{
    statisticsQuery = statisticsQuery.Skip(loadMore.Skip.Value).Take(loadMore.Take.Value);
}

Now, when I set Take 10 and query only for 10 Hr users, the above code runs in 3 seconds. Is this normal or should it be further optimized? If it should be done, how?

Tags:

2

Answers


  1. 0

    You can consider updating the DTO to store the application counts as a grouping to be filled in by EF/Linq then expose helper properties for the specific counts:

    public class VacancyByRecruiterModelDto
{
    public int RecruiterId { get; set; }
    public string RecruiterFullName { get; set;}
    public int PublishedTotalVacancyCount { get; set; }
    public int CompletedTotalVacancyCount { get; set; }

    public Dictionary<int, int> ApplicationCounts { get; set; } = new();

    public int CvCount 
    {
        int count = 0;
        ApplicationCounts.TryGetValue(1, out count);
        return count;
        // You could use ApplicationCounts[1], however if no item in the user
        // has a status of 1 there would be no element /w count.
    }
    public int ExamCount 
    {
        int count = 0;
        ApplicationCounts.TryGetValue(2, out count);
        return count;
    }

    // ... repeat for each count...
}

    Then when populating:

    var statisticsQuery = _context.Users
    .OrderBy(hrUser => hrUser.Id)
    .Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))
    .Select(hrUser => new VacancyByRecruiterModelDto
    {
        RecruiterId = hrUser.Id,
        RecruiterFullName = hrUser.Name + " " + hrUser.Surname,
        PublishedTotalVacancyCount = hrUser.Vacancies.Count(vac => vac.StatusId == 1),
        CompletedTotalVacancyCount = hrUser.Vacancies.Count(vac => vac.StatusId == 2),
        ApplicationCounts = hrUser.Vacancies
            .SelectMany(v => v.Applications)
            .GroupBy(a => a.StatusId)
            .ToDictionary(g => g.Key, g.Count())
    });

if (loadMore?.Skip != null && loadMore?.Take != null)
{
    statisticsQuery = statisticsQuery
        .Skip(loadMore.Skip.Value)
        .Take(loadMore.Take.Value);
}

    Similarly, this can be done for the vacancy counts if there is a chance that there could be more than two statuses. Note that AsNoTracking is only needed when you are loading entities. It is not necessary/used when projecting.

    Login or Signup to reply.
  2. 0

    Try the following optimization. I have used grouping to count statuses separately and then joined the result with the users. It should reduce count of subqueries and improve performance.

    var usersQuery = _context.Users
    .Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))
    .OrderBy(hrUser => hrUser.Id);

if (loadMore?.Skip != null && loadMore?.Take != null)
{
    usersQuery = usersQuery.Skip(loadMore.Skip.Value).Take(loadMore.Take.Value);
}

var statusesQuery =
    from u in usersQuery
    from v in hrUser.Vacancies
    from a in v.Applications
    where a.StatusId >= 1 && a.StatusId <= 15
    group a by new { UserId = u.Id } into g
    select new
    {
        g.Key.UserId,
        CvCount              = g.Count(app => app.StatusId == 1),
        ExamCount            = g.Count(app => app.StatusId == 2),
        InterviewCount       = g.Count(app => app.StatusId == 3),
        DocumentCount        = g.Count(app => app.StatusId == 4),
        HiringSuccessCount   = g.Count(app => app.StatusId == 5),
        CvFailedCount        = g.Count(app => app.StatusId == 6),
        CandidateFailedCount = g.Count(app => app.StatusId == 7),
        DontSeeCount         = g.Count(app => app.StatusId == 8),
        ReserveCount         = g.Count(app => app.StatusId == 9),
        InternCount          = g.Count(app => app.StatusId == 10),
        SecurityCount        = g.Count(app => app.StatusId == 11),
        DontExamCount        = g.Count(app => app.StatusId == 12),
        DontInterviewCount   = g.Count(app => app.StatusId == 13),
        HiringStoppedCount   = g.Count(app => app.StatusId == 14),
        FailInternshipCount  = g.Count(app => app.StatusId == 15),        
    };

var statisticsQuery =
    from u in usersQuery
    from s in statusesQuery
        .Where(s => s.UserId == u.Id)
        .DefaultIfEmpty()
    select new VacancyByRecruiterModelDto
    {
        RecruiterId = u.Id,
        RecruiterFullName = u.Name + " " + hrUser.Surname,
        PublishedTotalVacancyCount = u.Vacancies.Count(vac => vac.StatusId == 1),
        CompletedTotalVacancyCount = u.Vacancies.Count(vac => vac.StatusId == 2),

        CvCount              = (int?)s.CvCount ?? 0,
        ExamCount            = (int?)s.ExamCount ?? 0,
        InterviewCount       = (int?)s.InterviewCount ?? 0,
        DocumentCount        = (int?)s.DocumentCount ?? 0,
        HiringSuccessCount   = (int?)s.HiringSuccessCount ?? 0,
        CvFailedCount        = (int?)s.CvFailedCount ?? 0,
        CandidateFailedCount = (int?)s.CandidateFailedCount ?? 0,
        DontSeeCount         = (int?)s.DontSeeCount ?? 0,
        ReserveCount         = (int?)s.ReserveCount ?? 0,
        InternCount          = (int?)s.InternCount ?? 0,
        SecurityCount        = (int?)s.SecurityCount ?? 0,
        DontExamCount        = (int?)s.DontExamCount ?? 0,
        DontInterviewCount   = (int?)s.DontInterviewCount ?? 0,
        HiringStoppedCount   = (int?)s.HiringStoppedCount ?? 0,
        FailInternshipCount  = (int?)s.FailInternshipCount ?? 0
    };
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search