I am having a performance issue retrieving three game rankings. The code below runs approximately 300 ms and it's to long for my manager.

        public async Task<PointsDto> UserPoints(int userId, int countryId)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var userData = await context.UsersProfile
                .AsNoTracking()
                .Select(x => new { x.SeasonPoints, x.RankingPoints, x.Id })
                .FirstOrDefaultAsync(x => x.Id == userId);

            var seasonPlace = await context.UsersProfile
                .Select(x => x.SeasonPoints)
                .Where(x => x > userData.SeasonPoints)
                .CountAsync();

            var regionPlace = await context.Users
              .Include(x => x.UserProfile)
              .Select(x => new { x.UserProfile.RankingPoints, x.CountryId })
              .Where(x => x.CountryId == countryId && x.RankingPoints > userData.RankingPoints)
              .CountAsync();

            var worldPlace = await context.UsersProfile
                .Select(x => x.RankingPoints)
                .Where(x => x > userData.RankingPoints)
                .CountAsync();

            var result = new PointsDto()
            {
                RankingPoints = userData.RankingPoints,
                SeasonPoints = userData.SeasonPoints,
                RegionPlace = regionPlace   1,
                SeasonPlace = seasonPlace   1,
                WorldPlace = worldPlace   1
            };

            stopwatch.Stop();
            Console.WriteLine(stopwatch.ElapsedMilliseconds);

            return result;

        }

I tried to download all users and count them. Then it worked better, but I'm afraid when there will be 1,000,000 users and at least 1/10 will download so much data, it will be a heavy burden on the memory. It would be good to cut the execution time by at least half, but I don't know how to do it anymore.

CodePudding user response：

For a performance reason I would delegate it to a Sql Server TVF. Kind of

create function Ranking(@userId int, @countryId int)
returns table
as
return
    with userdata as (
       select SeasonPoints, RankingPoints
       from UsersProfile
       where Id = @userId
    )
    select count(case when x.SeasonPoints > userData.SeasonPoints then x.Id end) seasonPlace,
           count(case when x.CountryId = @countryId and x.RankingPoints > userData.RankingPoints then x.Id end) regionPlace,
           count(case when x.RankingPoints > userData.RankingPoints then x.Id end) worldPlace,
    from UsersProfile x
    join userData on (x.RankingPoints > userData.RankingPoints 
                     or x.SeasonPoints > userData.SeasonPoints);

The query optimizer would advice proper indexes if missing.